プロ野球が始まったこともありここ最近は贔屓チームの試合ばっかり見ています。

チーム状況が苦しいのですが、なんとか頑張って欲しいものです。

さて、本題へ…

7月13日にMySQL 8.0.21がGAとなりました。

dev.mysql.com

早速インストールしてみて、遊んでみたりしています。

今回はInnoDB redo logの無効化について調べていこうと思います。

このバージョンで新しくInnoDBにおけるREDOログを無効にすることが可能となりました。これによりmysqldumpしたデータのインポートなど、元データのあるデータロードの高速化が見込めます。

3行で
InnoDB Redo Log
InnoDB Redo Logの無効化/有効化
sysbenchを使って検証してみる
検証内容を変えてみる
- Redo有効
- Redoログ無効化
まとめ

3行で

MySQL 8.0.21からコマンドで簡単にInnoDB Redo Logの有効化/無効化の切り替えができるようになった
Redoログを無効化にすることでデータロードの高速化が狙える
手元にデータがある場合など、失敗してもやり直せる場合では積極的に使ってみてもいいかも（ただし対象はグローバルなので本番かつオンラインでは非推奨）

InnoDB Redo Log

InnoDB Redo LogはいわゆるWAL(Write Ahead Log)とも呼ばれ、ACID特性のうちDurabilityを担保するためのものです。 InnnoDBへの変更はデータページに即時に反映されるわけではなく、まずREDOログに書き込まれます。クライアントにはREDOログへの書き込みが成功した時点でCommit成功の通知を行います。

なぜ直接データページを変更しないかというとパフォーマンスの問題です。データページの書き込み時にはほとんどの場合ディスクへのランダムアクセスが発生するため比較的コストの高い操作となります。

一方REDOログもディスク上に存在する（メモリには揮発性があるため）ので、ディスクIOは発生するのですがシーケンシャルに書き込まれるため、直接データページに書き込むよりパフォーマンスがよくなります。その後REDOログに書き込まれた内容は、チェックポイントという操作を通じて非同期にデータページに反映されていきます。

MySQLはクラッシュリカバリ時にまだチェックポイントが済んでいないRedoログを適用しデータの整合性を保ちます。

参考

MySQL :: MySQL 8.0 Reference Manual :: 15.18.2 InnoDB Recovery

ということでRedoログは大切な要素であり、本番環境では間違っても無効化にしてはいけない機能です。しかし、mysqldumpで取得したデータをインポートしたいなどの元となるデータが手元にある場合でのデータロードを行うときはこの機能を無効化してもいいかもしれません。途中でクラッシュしても、手元にデータがあるためもう一度インポートし直せばいいからです。

そのような場合ではRedoログを一時的に無効化することによってデータロードにかかる時間を短縮することが可能です。（これはRedoログに限らずDoublewriteバッファなどにも言える）

MySQL 8.0.17からCloneプラグインが導入されたためmysqldump --all-databasesでデータ移行する機会も減ってきているかもしれませんが、バージョンが異なるサーバ間でのデータ移行などでまだまだmysqldumpが必要となるケースはあるでしょう。

InnoDB Redo Logの無効化/有効化

redoログの無効化は下記のコマンドで行えます。

ALTER INSTANCE DISABLE INNODB REDO_LOG;

redoログの有効化は下記のコマンドで行えます。

ALTER INSTANCE ENABLE INNODB REDO_LOG:

なお、これらの操作にはINNODB_REDO_LOG_ENABLE権限が必要です。

mysql>ALTER INSTANCE ENABLE INNODB REDO_LOG;
ERROR 1227 (42000): Access denied; you need (at least one of) the INNODB_REDO_LOG_ENABLE privilege(s) for this operation

REDOログが有効/無効化は下記のコマンドから調べることが可能です。

SHOW STATUS LIKE 'Innodb_redo_log_enabled';

SHOW VARIABLESではありませんので、注意しましょう。

実行例

mysql>ALTER INSTANCE DISABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.01 sec)

mysql>SHOW STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql>ALTER INSTANCE ENABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.10 sec)

mysql>SHOW STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+
1 row in set (0.01 sec)

変更は即時に反映され、対象はインスタンス全体です。もちろん普段はONにしておきましょう。

なおSET GLOBALと違って再起動しても反映された変更が元に戻らないため注意が必要です。

sysbenchを使って検証してみる

それでは、redoログを無効化することでどの程度データロードが高速化するか、sysbenchのprepareコマンドを使って検証してみます。

検証方法は下記の通りです。

テーブルの行数は1500万行
テーブルの数は1
スレッド数も1
各検証は都度mysqldは再起動してバッファが空の状態から始める

1500万行のテーブルサイズはインデックス込みで約3.5GBほどとなります。

主なサーバパラメータは下記の通りです。

innodb_buffer_pool_size = 2G
innodb_write_io_threads = 4
innodb_flush_method = O_DIRECT

binary logやdoublewrite bufferは有効のままです。

実行するコマンド

time sysbench --db-driver=mysql \
              --mysql-host=127.0.0.1 \
              --mysql-port=3306 \
              --mysql-user=sbtest \
              --mysql-password=sbtest \
              --mysql-db=sbtest \
              --tables=1 \
              --table_size=15000000 \
              --threads=1 \
              oltp_read_write \
              prepare;

Redoログ有効化での測定

まずはデフォルトの状態で計測してみます。

[root@centos8-mysql:~]# mysql -e "show status like 'Innodb_redo_log_enabled';"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

[root@centos8-mysql:~]# time sysbench --db-driver=mysql \
>               --mysql-host=127.0.0.1 \
>               --mysql-port=3306 \
>               --mysql-user=sbtest \
>               --mysql-password=sbtest \
>               --mysql-db=sbtest \
>               --tables=1 \
>               --table_size=15000000 \
>               --threads=1 \
>               oltp_read_write \
>               prepare;
sysbench 1.1.0-797e4c4 (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 15000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

real    20m20.143s
user 0m57.082s
sys 0m3.532s

20分ほどかかりました。

Redoログ無効化での測定

お次はRedoログを無効化にして測定してみます。

[root@centos8-mysql:~]# mysql -e "show status like 'Innodb_redo_log_enabled';"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

[root@centos8-mysql:~]# time sysbench --db-driver=mysql \
>               --mysql-host=127.0.0.1 \
>               --mysql-port=3306 \
>               --mysql-user=sbtest \
>               --mysql-password=sbtest \
>               --mysql-db=sbtest \
>               --tables=1 \
>               --table_size=15000000 \
>               --threads=1 \
>               oltp_read_write \
>               prepare;
sysbench 1.1.0-797e4c4 (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 15000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

real    10m40.655s
user 0m42.342s
sys 0m4.060s

10分ほど。

およそ半分の時間でロードが完了しました。それなりにいい結果なのではないでしょうか。

検証内容を変えてみる

もう少し検証を続けます。

先ほどは大きなテーブルをシングルスレッドでインポートしてきましたが、今度は比較的小さいテーブルを複数のスレッドでインポートする場合はどうなのか調べてみます。

検証内容は下記の通りです。

各テーブルの行数は150万行
テーブルの数は10
スレッド数も10
各検証は都度mysqldは再起動してバッファが空の状態から始める

全体のデータサイズはそこまで変わりません。

Redo有効

まずはRedoログを有効化した状態で測定します。

[root@centos8-mysql:~]# mysql -e "show status like 'Innodb_redo_log_enabled';"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

[root@centos8-mysql:~]# time sysbench --db-driver=mysql \
>               --mysql-host=127.0.0.1 \
>               --mysql-port=3306 \
>               --mysql-user=sbtest \
>               --mysql-password=sbtest \
>               --mysql-db=sbtest \
>               --tables=10 \
>               --table_size=1500000 \
>               --threads=10 \
>               oltp_read_write \
>               prepare;

real    12m31.859s
user 1m11.136s
sys 0m3.859s

12分ほどかかりました。

Redoログ無効化

次はRedoログを無効化にして測定してみます。

[root@centos8-mysql:~]# mysql -e "show status like 'Innodb_redo_log_enabled';"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

[root@centos8-mysql:~]# time sysbench --db-driver=mysql \
>               --mysql-host=127.0.0.1 \
>               --mysql-port=3306 \
>               --mysql-user=sbtest \
>               --mysql-password=sbtest \
>               --mysql-db=sbtest \
>               --tables=10 \
>               --table_size=1500000 \
>               --threads=10 \
>               oltp_read_write \
>               prepare;

real    6m14.839s
user 1m26.621s
sys 0m4.233s

こちらもREDOログを無効化することによって半分ほどの時間でデータのロードが完了しました。

まとめ

ということで、今回はInnoDB Redo Logの無効化について調べどの程度パフォーマンスが向上するか簡単に調べてみました。

今回の検証ではRedoログの無効化により書き込みにかかる時間が約半分となることを確認しました。（今回の検証は極めて簡単な場合なので必ずご自身の環境で試してみてください）

もしbinary logみたいにセッションごとにRedoログの有効/無効の切り替えができたらかなり便利ですが、つくり的になかなか難しいのだろうと思います。

ちなみに、今回はSSDを使っていましたが、HDDだともう少し検証結果に違いが出てくるかもしれません。（HDDはシーケンシャルIOとランダムIOの差がSSDより大きいため）

それでは、今回はこの辺で。

三流エンジニアの落書き帳

さあ、今日も未知という扉を開けてみよう

MySQL8.0.21から使えるInnoDB REDO LOGの無効化について

3行で