こんにちは。プラットフォーム開発部の西です。
lancers.jp は2008年にリリースしたサービスです。
クライアント及びランサーの皆様のおかげで12月16日にサービス開始15周年を迎えます。
長期間稼働しているシステムのデータベースには、それなりにデータが蓄積されています。
データベースの文脈でチューニングと聞くと参照系の方を想像してしまいがちなのですが、更新系のチューニングについて調べる機会がありましたので書いてみたいと思います。
概要
イメージとして、テーブル名を伏せた上で抜粋するとこのような規模感です。
> select table_rows from information_schema.tables order by table_rows desc;
+------------+
| TABLE_ROWS |
+------------+
| 77,661,334 |
| 69,845,127 |
| 52,873,285 |
| 45,284,547 |
| 43,818,138 |
+------------+これらのテーブルを参照 or 更新するプログラムの開発が必要となる状況を考えた場合、まずは既存データの調査の後に設計に入ることが多いかと思います。
ここで作成したプログラムが正しく動くことを検証するにあたり、本番環境に近いデータで検証ができるとより高い品質を担保することができます。
一定期間稼働しているサービスにおいては、下記のような形で想定していないデータが存在する可能性があるため、本番環境のデータを正とした動作検証が重要と考えます。
- 今の仕様では発生しないデータ（過去のコードで生成されたデータ）
- バグや運用カバーなどの理由により、手動で書き換えられたデータ
開発者自身でテストデータを作り、動作確認するのも一つの手段ではあるのですが、上記のような開発者が想定し得ないパターンの検証漏れが発生する可能性があります。
そのため、一定開発が進んだ段階で実際のデータをもとに動かすことが重要と考えています。
しかし、データ量が多いとその分の取り回しは大変です。当該テーブルへの書き換えを伴うものであれば都度データの初期化などが必要となるため尚更大変です。
lancers.jp では、開発部署全体で共用している検証環境があり、本番環境と同等のデータ量を同期して利用できるようにしています。
しかし、このデータベースは1つしか存在しないため、頻繁に重いクエリを投げたり迂闊にデータの内容を書き換えたりなどが発生すると他の検証に影響を与える可能性があり、暗黙的に一定の制限が伴っているのが現状です。
本番環境に近いデータを用いながらローカル開発環境上で開発できると捗りそうですが、データ量が大きいことがネックになりがちです。
そこで、本番と同等のデータ量をローカル環境上に構築するにあたり、高速化できる余地がないか調査しました。
調査方法
インターネット上で事例を検索すると、データベースへの更新処理の高速化に有効なパラメータとして、Innodb_redo_log_enabled パラメータをオフにするという方法があります。
MySQL 8.0.21で追加されたオプションのようです。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-redo-log.html
MySQLに限った話ではないのですが、更新系のクエリを実行すると一度REDOログ(※1)と呼ばれる領域に書き込まれ、非同期でテーブルへの反映を行っています。
(※1) 呼び方は製品によって異なる
この領域はデータベースに何らかの障害が発生した際、ロールフォワードする用途で使っています。
本番環境ではオンにする必要があるのですが、開発環境にインポートする場合はオフにすることができます。
早速、どの程度の効果があるかを見てみましょう。
27万件ほどのレコードがテーブルで試してみたいと思います。
> time mysql -uroot -h127.0.0.1 test_database < table_20231110.sql
________________________________________________________
Executed in 57.69 secs fish external
usr time 1.14 secs 0.19 millis 1.14 secs
sys time 0.08 secs 1.25 millis 0.08 secs
> ALTER INSTANCE DISABLE INNODB REDO_LOG;
> time mysql -uroot -h127.0.0.1 test_database < table_20231110.sql
________________________________________________________
Executed in 50.43 secs fish external
usr time 962.84 millis 0.22 millis 962.62 millis
sys time 70.64 millis 2.07 millis 68.57 millis
7秒ほどの短縮ができました。どうやら効果はあるようです。
次にローカル開発環境内で直接起動しているMySQL（Dockerを介さない形）で試してみたいと思います。
> time mysql -uroot -h127.0.0.1 test_database < table_20231110.sql
________________________________________________________
Executed in 6.05 secs fish external
usr time 516.43 millis 0.17 millis 516.26 millis
sys time 41.93 millis 1.52 millis 40.41 millis
> ALTER INSTANCE DISABLE INNODB REDO_LOG;
> time mysql -uroot -h127.0.0.1 test_database < table_20231110.sql
________________________________________________________
Executed in 5.64 secs fish external
usr time 502.76 millis 0.16 millis 502.60 millis
sys time 38.27 millis 1.13 millis 37.14 millis
約0.4秒ほどの短縮ですが、こちらは想定していたほど短縮されませんでした。
それ以上に、Dockerコンテナを介することで発生していたオーバーヘッド（約50秒）の短縮が著しいです。
最後に、本番環境と同僚のデータをローカル環境上のMySQLデータベース（Dockerなし）にインポートして試してみました。
件数は7600万件ほどです。Innodb_redo_log_enabledはオフにしたままとします。
> time mysql -uroot -h127.0.0.1 test_database < table_20231110.sql
________________________________________________________
Executed in 58.52 mins fish external
usr time 125.84 secs 0.13 millis 125.84 secs
sys time 7.38 secs 1.07 millis 7.38 secs
1時間未満で同期されました。
インターネット上の事例を見るともう少し短縮の余地はありそうなのですが、マシンスペックに起因するところもあると思うのでこの辺にしておきます。
ちなみに、Docker環境上のMySQLコンテナにもインポートを試みたのですが数時間以上経過後も終わる気配がなく、途中からデータ自体のインポートも進捗している様子がなく検証ができませんでした。（何らかのリソースを使い果たしてしまったものと考えられます）
最後に
今回の実験を通して、MySQLにおける更新処理の高速化とDocker環境がもたらすオーバーヘッドについて観察することができました。
lancers.jpのインフラはAWS上で構築されており、検証環境も同様にAWS上に構築されています。
ここまで書いておいてひっくり返すようですが、RDSのスナップショットを採取した上で、そのイメージを元に検証用のRDSインスタンスを別に建てて、気軽に壊して作れるようにする方が楽で早いし簡単だと思います。（状況が許せば）