なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

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.

See Disabling Redo Logging.

がありました。


これは、「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で投入し、時間を計測する。
環境
  • MySQLサーバー
    • AWS EC2 m5.large(2vCPU、8GiBメモリ)
    • AmazonLinux2
    • MySQL8.0.20用/MySQL8.0.21用の2台。いずれもyumでインストールしたもの。
    • パラメータは原則デフォルトで、以下を調整。たぶんコレで全部のはず。
      • innodb_log_file_size=1GB : 試験取り込むデータはCSVで820MB程度なので、それを上回る量とした。
      • innodb_buffer_pool_load_at_startup=0:試験の都度mysqldを再起動し、バッファをクリアするため、停止時にデフォルトでダンプされるバッファデータを起動時に読み込まない設定にした。
      • local_infile=ON:リモートにあるクライアントからLOAD DATA LOCAL INFILEを実行するため。
  • 負荷発生源
    • AWS EC2 m5.large(2vCPU、8GiBメモリ)
    • AmazonLinux2
    • MySQL8.0.20のClientをyumでインストール。ここから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時過ぎ)
    • パラメータは原則デフォルトで、以下を調整。たぶんコレで全部のはず。
      • innodb_log_file_size=1GB : 試験取り込むデータはCSVで820MB程度なので、それを上回る量とした。
      • innodb_buffer_pool_load_at_startup=0:試験の都度mysqldを再起動し、バッファをクリアするため、停止時にデフォルトでダンプされるバッファデータを起動時に読み込まない設定にした。
      • local_infile=ON:リモートにあるクライアントからLOAD DATA LOCAL INFILEを実行するため。

せっかく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%程度の改善ということで、大きくは変わりませんでした。



環境やワークロードによって差は出ると思いますので、参考程度にしつつ、まあまあ効果はありそうなのでうまく使いましょう、って感じですね。

まとめ

  • MySQL8.0.21の「Redoログ無効化」が使えるようになった。
  • Redoログを無効にするとクラッシュリカバリが効かなくなるので、「失敗したらやり直し」な初期ロードなど作業に使える。
  • Redoログ無効化すると、ファイルロード処理で30%くらい速くなる。
  • Redoログ無効化は、システム全体でのON/OFFしかない。
  • (お約束ですが)「マイナーバージョンアップ」です。

先日発売されたコチラの本はMySQL8.0.20までなので当機能は記載されていませんが、それでも「入門」という皮をかぶりつつ中上級の話も盛りだくさんですので、幅広い層の人に役立つ本になっています。