はじめに
MySQL 8.4 より、innodb_change_bufferingシステム変数のデフォルト値がall→none（無効）に変更されました。リリースノートや、What Is New in MySQL 8.4に変更の旨記載があります。
innodb_change_bufferingシステム変数で変更バッファの動作モードが決定されます。
今回はnone,allで処理速度にどのくらい違いがあるのかを、ベンチマークツール Sysbenchを用いて検証します。
- none: いかなる操作もバッファリングしません。
- all: 挿入、削除マーク操作、およびパージをバッファリングします。
none,allのほか、inserts,deletes,changes,purgesがありますが、ここでは説明を省略させていただきます。詳細は以下ドキュメントをご参照ください。
MySQL :: MySQL 8.4 Reference Manual :: 17.14 InnoDB Startup Options and System Variables
変更バッファの概要
引用：https://dev.mysql.com/doc/refman/8.4/en/innodb-change-buffer.html
MySQL InnoDB の変更バッファ（挿入バッファ・Change Buffer・Ibuf）は、セカンダリインデックスページがバッファプールにない場合に、そのページへの変更をキャッシュする特別なデータ構造です。
バッファリングされた変更はINSERT、DELETE、またはUPDATE操作(DML)によって発生する可能性があり、後で他の読み取り操作によってページがバッファプールにロードされるときにマージされます。
コストのかかるランダム アクセス I/O 操作を回避できます。
ただし、変更バッファが有用なのはHDDなどの場合であり、ランダム読み取りがシーケンシャル読み取りとほぼ同じ速度であるSSDを使用している場合は、無効にすることを検討してください。
変更バッファを無効にすることのパフォーマンス上の利点について、以下のMySQLバグレポートで紹介されています。
- Less redo log being generated
- Less I/O on the InnoDB system tablespace
- Change buffering could make the system tablespace (ibdata*) grow unexpectedly; disabling it cures this.
- Faster slow shutdown and less background load (no buffer merges)
- Less frequent redo log flushes when using a small redo log, because the buffer pool is not polluted by ibuf pages
- Faster InnoDB Hot Backup runs (less stuff being written to the redo logs, and you can use smaller redo logs)
- 生成される redo ログが少なくなる
- InnoDB システム テーブルスペースの I/O が少なくなる
- 変更バッファリングにより、システム テーブルスペース (ibdata*) が予想外に大きくなる可能性があります。これを無効にすると、この問題は解決します。
- 低速シャットダウンが高速化し、バックグラウンド ロードが軽減されます (バッファ マージなし)
- バッファ プールが ibuf ページによって汚染されないため、小さな redo ログを使用する場合、redo ログのフラッシュ頻度が低くなります
- InnoDB ホット バックアップの実行が高速化されます (redo ログに書き込まれる内容が少なくなり、小さな redo ログを使用できます)
引用：https://bugs.mysql.com/bug.php?id=56283
Google翻訳
検証
検証にはSysbenchを用いて
- テーブルを作成・1000万行を挿入した後、
- 10分間の書き込み処理を実行します。
結論から言うと、SSDではほぼ違いがありませんでした。（1％ほどallのほうが速い）
検証環境
|
1 2 3 4 5 6 |
$ cat /etc/oracle-release Oracle Linux Server release 9.3 $ mysql --version mysql Ver 8.4.1 for Linux on x86_64 (MySQL Community Server - GPL) $ sysbench --version sysbench 1.0.20 |
- CPU: 4コア
- メモリ: 16GB
- ストレージ: SSD使用（参考程度に、ストレージの読み取り速度はhdparmコマンドで240.02 MB/secとなりました。）
123$ hdparm -t /dev/sda/dev/sda:Timing buffered disk reads: 726 MB in 3.02 seconds = 240.02 MB/sec
検証コマンド
Sysbenchインストール
|
1 2 |
$ sudo dnf install -y epel-release $ sudo dnf install -y sysbench |
Sysbench用スキーマ作成・ユーザ作成
|
1 2 3 |
mysql> CREATE DATABASE benchmark; mysql> CREATE USER bench@localhost IDENTIFIED BY 'password'; mysql> GRANT ALL PRIVILEGES ON 'benchmark'.* TO 'bench'@'localhost'; |
innodb_buffer_pool_sizeを10GBに設定
|
1 |
mysql> SET GLOBAL innodb_buffer_pool_size=10*1024*1024*1024; |
innodb_change_buffering（デフォルトでnone）
|
1 2 3 4 5 6 7 |
mysql> SHOW VARIABLES LIKE 'innodb_change_buffering'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_change_buffering | none | +-------------------------+-------+ 1 row in set (0.01 sec) |
1000万行のデータを投入します。
|
1 2 3 4 5 6 7 8 9 |
$ sysbench --db-driver=mysql \ --mysql-host=localhost \ --mysql-user=bench \ --mysql-password='password' \ --mysql-db=benchmark \ --table_size=10000000 \ --secondary=on \ oltp_write_only \ prepare; |
自動作成されたテーブル定義は以下の通りとなっています。
—secondary=onを指定したので、主キーは無く、セカンダリインデックスが2つあります。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show create table benchmark.sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE 'sbtest1' ( 'id' int NOT NULL AUTO_INCREMENT, 'k' int NOT NULL DEFAULT '0', 'c' char(120) NOT NULL DEFAULT '', 'pad' char(60) NOT NULL DEFAULT '', KEY 'xid' ('id'), KEY 'k_1' ('k') ) ENGINE=InnoDB AUTO_INCREMENT=7492 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
1000万行が作成されたテーブルサイズは2.6Gほどでした。
|
1 2 3 |
$ ls -lh /var/lib/mysql/benchmark/ total 2.6G -rw-r-----. 1 mysql mysql 2.6G Jul 18 15:17 sbtest1.ibd |
次に10分間の書き込み処理（oltp_write_only）を流します。
|
1 2 3 4 5 6 7 8 9 10 |
$ sysbench --db-driver=mysql \ --mysql-host=localhost \ --mysql-user=bench \ --mysql-password='password' \ --mysql-db=benchmark \ --events=0 \ --time=600 \ --threads=4 \ oltp_write_only \ run; |
結果は以下のように出力されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL statistics: queries performed: read: 0 write: 2059942 other: 1030033 total: 3089975 transactions: 514659 (857.76 per sec.) queries: 3089975 (5149.91 per sec.) ignored errors: 715 (1.19 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 600.0029s total number of events: 514659 Latency (ms): min: 1.39 avg: 4.66 max: 82.23 95th percentile: 9.06 sum: 2398246.98 Threads fairness: events (avg/stddev): 128664.7500/229.23 execution time (avg/stddev): 599.5617/0.08 |
次にinnodb_change_bufferingをallに設定してSysbenchを実行します。
テストテーブルは1回ごとに削除してprepareで再作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SET GLOBAL innodb_change_buffering=all; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_change_buffering'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_change_buffering | all | +-------------------------+-------+ 1 row in set (0.01 sec) mysql> DROP TABLE benchmark.sbtest1; |
上記と同じprepareコマンドと、runコマンドを実行します。
変更バッファがどのくらいマージされたのかをSHOW ENGINE INNODB STATUSコマンドで調べられます。Sysbench実行前は以下です。
|
1 2 3 4 5 6 |
$ mysql -uroot -ppassword -e "SHOW ENGINE INNODB STATUS\G" | grep Ibuf -A 4 Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 |
runコマンドでSysbench実行後に、再度SHOW ENGINE INNODB STATUSコマンドを実行した結果が以下です。
|
1 2 3 4 5 6 |
$ mysql -uroot -ppassword -e "SHOW ENGINE INNODB STATUS\G" | grep Ibuf -A 4 Ibuf: size 1, free list len 0, seg size 2, 1281 merges merged operations: insert 0, delete mark 1281, delete 0 discarded operations: insert 0, delete mark 0, delete 0 |
合計が、1281 mergesとなっています。
- merges: InnoDB セカンダリ インデックス挿入バッファが InnoDB データファイルにマージされた合計回数。
内訳をみると、delete mark 1281となっています。
- delete mark: 変更バッファリングによってマージされた削除済みレコードの合計数。
insertとdeleteについては0のままとなっています。
- insert: InnoDB セカンダリ インデックス挿入バッファへの挿入の合計数。
- delete: InnoDB セカンダリ インデックス削除バッファが InnoDB データファイルにマージされた合計回数。
また、discarded operations:も0のままとなっています。
その他のメトリックについては、以下ドキュメントをご参照ください。
MySQL :: Oracle Enterprise Manager for MySQL Database User’s Guide :: 6.12 InnoDB Insert Buffer Activity Metrics
検証結果
none,allでそれぞれ3回ずつテストを行い、平均値を表にまとめました。
時間は10分で統一されているので、どのくらい処理が実行されたかを比較対象として、queries,transactionsを確認します。
また、参考程度にLatencyも速くなっているか確認しておきます。
query、transactions数
多いほど良い
| queries | queries per sec. | transactions | transactions per sec. | |
|---|---|---|---|---|
| none | 3090755 | 5151.20 | 514810 | 858.01 |
| all | 3119973 | 5199.86 | 519685 | 866.13 |
Latency
短いほど良い
| min | avg | max | 95th percentile | |
|---|---|---|---|---|
| none | 1.36 | 4.66 | 112.46 | 9.17 |
| all | 1.37 | 4.61 | 118.28 | 9.06 |
ほぼ変わらない結果となりました。（1％ほどallのほうが速い）
まとめ
結論としては、SSD使用時に変更バッファを無効にしても処理速度にほとんど違いは無いことが改めて確認できました。
余談になりますが、MariaDBでは2022年リリースのMariaDB 11.0.0 で変更バッファが削除されました。
InnoDB Change Buffering – MariaDB Knowledge Base
こちらはパフォーマンスの低下だけでなくバグ起因のところがあるようですので、同列で扱うことはできませんが、MySQLもデフォルト無効→非推奨→削除の流れに進んでいくのではないかと想像してしまいます。
