MySQL8.0.21の「Redoログ無効化」で大量書込処理を加速する
7/13 に MySQL 8.0.21 がリリースされました。
リリースノートを読んでいて「Functionality Added or Changed」の3つ目に
InnoDB: Redo logging can now be enabled and disabled using ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. This functionality is intended for loading data into a new MySQL instance. Disabling redo logging helps speed up data loading by avoiding redo log writes.
The new INNODB_REDO_LOG_ENABLE privilege permits enabling and disabling redo logging.
The new Innodb_redo_log_enabled status variable permits monitoring redo logging status.
がありました。
これは、「Redoログへの書き込みを省略することで、クラッシュリカバリができなくなる代わりに、更新処理を高速化する」という機能を実現するものです。
主に、構築段階でのイニシャルデータロードでのユースケースが想定されています。
Oracleからやってきた人には馴染みのある「NOLOGGING」ですが、MySQLでもできるようになりました。
ただし、「システム全体に対する設定のみ!」です。OracleだとSQL単位でNOLOGGING指定できたり、表単位でNOLOGGING属性をもたせたりできましたが、MySQLはまだそこまでできません。
機能の要点
こんな感じです。
- 「 ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG 」コマンドでREDOログ出力の有効無効を切り替え。SET GLOBALやmy.cnfへの設定ではない。
- mysqldを再起動しても、再起動前の設定は残っている。
- REDOログ出力有効無効の切り替えには「 INNODB_REDO_LOG_ENABLE 権限(8.0.21から追加された)」が必要。
- 現在の状態はSHOW GLOBAL STATUSコマンドから「Innodb_redo_log_enabled」で確認可能。サーバーシステム変数(VARIABLES)ではない。
- 意図せずクラッシュすると、起動時にエラーになる。(まだ試してないけど、ドキュメントにそう書いてある。)
なぜGLOBAL VARIABLES(SET GLOBAL=)ではなく、「ALTER INSTANCE」でGLOBAL STATUSに持つようにしたのだろう。。。
早速試してみた
やりたくなりますよね。DBベンチ厨?としては。
HammerDBとかTPC-Hとか出てきますが、そのへんの説明は省略します。要はでかいデータロードの速度を測るのに都合のいいテストデータとして使った、ってだけです。
なお、いつもなら数回試した平均や中央値を勘案するのですが、1発あたり結構時間がかかったので、計測はどれも一発勝負です。
そのため、かならずmysqldを再起動してバッファクリアした状態からの試験にしています。
シナリオ
- MySQL8.0.20(Redoログ無効化機能自体がない)、MySQL8.0.21(Redoログ有効)、MySQL8.0.21(「Redoログ無効)の3環境を用意。
- HammerDB3.3からTPC-H ScaleFactor=1を1つのDBにbuildschemaし、その内容を他の2つのDBに複製。よってHammerDBによってランダム生成されるデータもすべて同じ状態。
- TPC-Hの最大テーブル「LINEITEM」をCSVで出力。CSVで約820MB、行数6,001,420件。
- 各試験の前に、LINEITEMテーブルをTRUNCATEかつ「systemctl {stop|start} mysqld」で停止、起動する。
- CSV化したLINEITEMテーブルのデータを、リモートの負荷発生マシンから各環境に対してLOAD DATA LOCAL INFILEで投入し、時間を計測する。
環境
結果
HammerDBが生成するTPC-Hのテーブルには、FKやIndexがついているので、それらの有り無しも含めて計測しました。
- FKあり、Secondary Indexあり
バージョン | REDO_LOG | 時間 | 8.0.20比 |
---|---|---|---|
8.0.20 | ENABLE | 30 min 8.31 sec | 1.00 |
8.0.21 | ENABLE | 28 min 31.50 sec | 0.93 |
8.0.21 | DISABLE | 19 min 15.48 sec | 0.64 |
- FKなし、Secondary Indexあり
バージョン | REDO_LOG | 時間 | 8.0.20比 |
---|---|---|---|
8.0.20 | ENABLE | 17 min 50.87 sec | 1.00 |
8.0.21 | ENABLE | 17 min 13.64 sec | 0.97 |
8.0.21 | DISABLE | 13 min 21.69 sec | 0.75 |
- FKなし、Secondary Indexなし、PKのみ。
バージョン | REDO_LOG | 時間 | 8.0.20比 |
---|---|---|---|
8.0.20 | ENABLE | 1 min 11.97 sec | 1.00 |
8.0.21 | ENABLE | 1 min 11.65 sec | 1.00 |
8.0.21 | DISABLE | 50.81 sec | 0.71 |
テストした順に記載しましたが、最初っから、PKのみにして試験すればよかった。。。。
一発10~30分かかっている原因が、FKチェックとIndexの書き込みだったなんて。。。
とはいえ、どれもREDOログ出力を止めると、3割くらいスピードアップしています。
追記:パラメータ設定について(2020/07/16 18時過ぎ)
せっかくEBS多めにしてIOPSベースラインを9000まで引き上げているのに、innodb_io_capacity/innodb_io_capacity_maxをデフォルトのまま(200と2000)でした。
せっかくEC2に(CPU2つのものが欲しかったから)メモリが8GiBあるのに、innodb_buffer_pool_sizeがデフォルトのまま(128MB)でした。
ほかにも、チューニングのしどころ(ていうか最初っから変えるよね、っていう項目)は結構あります。
で、innodb_io_capacity/innodb_io_capacity_maxを5000、innodb_buffer_pool_sizeを5GBまで引き上げて軽く試してみました。
絶対的な時間はガッツリ短縮される(FKなし、Secondary Indexあり、REDO_LOG=ENABLEが、上記追加設定前:17分台->設定後:2分台)のですが、
REDO_LOG=ENABLEとDISABLEとの間の比率という意味では、15%~30%程度の改善ということで、大きくは変わりませんでした。
環境やワークロードによって差は出ると思いますので、参考程度にしつつ、まあまあ効果はありそうなのでうまく使いましょう、って感じですね。