バイナリログ(binlog)とは？
MySQLのバイナリログ(binlog)は、MySQLインスタンス上で実行されたデータ変更履歴を記録したログファイルです。
バイナリログはレプリケーションや、バックアップと組み合わせて任意の時刻にデータベースをリカバリするPITR(Point In Time Recovery)などの目的で利用されます。
ただし、更新量が多いワークロードやbinlog_expire_logs_seconds(バイナリログの保存期間。デフォルト30日)が大きすぎる場合、バイナリログが肥大化していきストレージ容量を逼迫する可能性があります。
バイナリログの量を減らすには、binlog_expire_logs_secondsを下げるのが、シンプルで簡単に取れる対策ですが、例えば直近1週間の任意の時刻に常に復元できる状態でなければならないなどの要件があり、変更できない環境もあるかと思います。
MySQL 8.0.20から導入されたバイナリログトランザクション圧縮機能を使えば、バイナリログの保存期間を変更せずにサイズを減らすことが可能です。
binlog_transaction_compressionについて
binlog_transaction_compressionをONに設定することで、トランザクションペイロードを圧縮してバイナリログへ出力されます。
デフォルト値は0で、SET GLOBALやSET SESSIONでONにすることができます。
mysql> SHOW VARIABLES LIKE 'binlog_transaction_compression%';
+-------------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------------+-------+
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
+-------------------------------------------+-------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL binlog_transaction_compression=ON;
Query OK, 0 rows affected (0.00 sec)
// Replicaの場合はSQL_THREADの再起動が必要
STOP REPLICA SQL_THREAD;
START REPLICA SQL_THREAD;
レプリケーション環境で、ソースインスタンスでbinlog_transaction_compression=ONにした場合、レプリカへの送信され、リレーログに書き込まれるトランザクションペイロードも圧縮されているため、ネットワーク帯域とストレージ領域両方の節約にもなります。
すべてのトランザクションが圧縮されるわけではなく、一部は常に非圧縮です。
- トランザクションの GTID に関連するイベント (匿名 GTID イベントを含む)
- 変更イベントやハートビートイベントの表示など、その他のタイプの制御イベント
- インシデントイベントおよびそれを含むトランザクション全体
- 非トランザクションイベントおよびそれらを含むトランザクション全体。 非トランザクションストレージエンジンとトランザクションストレージエンジンが混在するトランザクションでは、ペイロードは圧縮されません
- ステートメントベースのバイナリロギングを使用してログに記録されるイベント。 バイナリログトランザクションの圧縮は、行ベースのバイナリロギング形式にのみ適用されます
SET GLOBAL binlog_transaction_compression=ON;
STOP REPLICA SQL_THREAD;
START REPLICA SQL_THREAD;
binlog_transaction_compression_level_zstdについて
圧縮にはzstdアルゴリズムが使用され、binlog_transaction_compression_level_zstdで圧縮レベルを設定可能です。 値の範囲は1-22で、大きいほど圧縮率は高くなりますが、データ圧縮コストも増加し、CPUやメモリーが使用されるので注意が必要です。
参考までに、実際に導入した本番稼働している環境では、デフォルトの3で圧縮率90% と1/10まで削減され、十分だったため特に値は変えませんでした。(ここでの圧縮率は元のサイズに対する減少率)
※ 圧縮はトランザクションの内容やワークロードによって異なるかと思いますのでご注意ください
バイナリログのトランザクション圧縮モニタリング
performance_schema.binary_log_transaction_compression_statsテーブルより、起動後のバイナリログトランザクション圧縮に関する統計情報が確認できます。
mysql> SELECT * FROM performance_schema.binary_log_transaction_compression_stats\G
*************************** 1. row ***************************
LOG_TYPE: BINARY
COMPRESSION_TYPE: ZSTD
TRANSACTION_COUNTER: 2108491
COMPRESSED_BYTES_COUNTER: 60567685985
UNCOMPRESSED_BYTES_COUNTER: 587303716054
COMPRESSION_PERCENTAGE: 90
FIRST_TRANSACTION_ID: bf9daf52-669d-11ed-8889-fa163e96c4fa:8721719
FIRST_TRANSACTION_COMPRESSED_BYTES: 319
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 627
FIRST_TRANSACTION_TIMESTAMP: 2024-03-12 19:20:10.251788
LAST_TRANSACTION_ID: bf9daf52-669d-11ed-8889-fa163e96c4fa:10830259
LAST_TRANSACTION_COMPRESSED_BYTES: 335
LAST_TRANSACTION_UNCOMPRESSED_BYTES: 425
LAST_TRANSACTION_TIMESTAMP: 2024-03-17 17:27:41.315158
LOG_TYPE: BINARY, COMPRESSION_TYPE: ZSTDのレコードは、バイナリログに書き込まれた圧縮トランザクションの情報で、例えばCOMPRESSION_PERCENTAGE: 90より圧縮率90%であることがわかります。
検証
トランザクション圧縮によってbinlogサイズは減る一方で、圧縮作業によるレイテンシ悪化やサーバリソースの消費がトレードオフとしてかかってきます。
ベンチマークsysbenchや巨大トランザクションをコミットして、圧縮による影響を検証してみます。
検証環境の情報は以下になります。
| MySQL Version | binlog_row_image | binlog_transaction_compression_level_zstd |
|---|---|---|
| 8.0.35 | FULL | 3 |
sysbenchの実行結果
圧縮が行われるのは、更新がコミットされるときなので、oltp_write_onlyシナリオでsysbenchベンチマークを実行してみます。
// 4.7GBテーブル10個をimportして実行
$ sysbench /usr/share/sysbench/oltp_write_only.lua --threads=30 --db-driver=mysql --mysql-host=${TARGET_HOST} --mysql-password=Password123 --tables=10 --table-size=20000000 --mysql-ssl=REQUIRED prepare
$ sysbench /usr/share/sysbench/oltp_write_only.lua --time=600 --threads=30 --db-driver=mysql --mysql-host=${TARGET_HOST} --mysql-password=Password123 --tables=10 --table-size=20000000 --mysql-ignore-errors=1062,1213 --mysql-ssl=REQUIRED run
// binlog_transaction_compression=OFF
SQL statistics:
queries performed:
read: 0
write: 674752
other: 337376
total: 1012128
transactions: 168688 (281.09 per sec.)
queries: 1012128 (1686.55 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 281.0925
time elapsed: 600.1157s
total number of events: 168688
Latency (ms):
min: 94.20
avg: 106.71
max: 131.75
95th percentile: 121.08
sum: 18001384.60
Threads fairness:
events (avg/stddev): 5622.9333/396.94
execution time (avg/stddev): 600.0462/0.04
// binlog_transaction_compression=ON
SQL statistics:
queries performed:
read: 0
write: 675972
other: 337986
total: 1013958
transactions: 168993 (281.60 per sec.)
queries: 1013958 (1689.60 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 281.5996
time elapsed: 600.1180s
total number of events: 168993
Latency (ms):
min: 94.27
avg: 106.52
max: 129.61
95th percentile: 121.08
sum: 18000888.35
Threads fairness:
events (avg/stddev): 5633.1000/462.64
execution time (avg/stddev): 600.0296/0.03
スループット・レイテンシともに、圧縮ONによる悪化は特に見られませんでした。
またCPU/memory使用率に関しても差はありませんでした。
oltp_write_onlyで実行されるDMLは、1件更新(4件/トランザクション)のためトランザクションサイズが小さい場合影響は軽微と言えそうです。
巨大トランザクションをCOMMIT
次は100万、500万、1,000万件のUPDATEで、巨大トランザクションをCOMMITしてみます。
mysql> SHOW CREATE TABLE sbcp\G
*************************** 1. row ***************************
Table: sbcp
Create Table: CREATE TABLE `sbcp` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
BEGIN;
UPDATE sbcp SET k=XXX ORDER BY id LIMIT {1000000, 5000000, 10000000};
COMMIT;
圧縮ON/OFFそれぞれで実行して、COMMIT実行時間・COMMIT CPU時間・メモリ使用量・トランザクションサイズを比較します。
なお、UPDATE対象のテーブルは事前にbuffer poolへ乗せています。
また、COMMIT CPU時間はperformance_schema.events_statements_summary_by_digestのSUM_CPU_TIME、メモリ使用量はOSのused, buff/cacheの増分を見ています。
mysql> UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_statements_cpu';
mysql> SELECT DIGEST_TEXT, format_pico_time(SUM_CPU_TIME) as cpu_latency FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT='COMMIT';
+-------------+-------------+
| DIGEST_TEXT | cpu_latency |
+-------------+-------------+
| COMMIT | 35.40 s |
+-------------+-------------+
結果は以下のようになりました。
- UPDATE 100万件
| 圧縮OFF | 圧縮ON | |
|---|---|---|
| COMMIT実行時間 | 0.93 s | 3.38 s |
| COMMIT CPU時間 | 0.83 s | 3.36 s |
| メモリ使用量 | +552MB | +581MB |
| トランザクションサイズ | 364MB | 100MB |
- UPDATE 500万件
| 圧縮OFF | 圧縮ON | |
|---|---|---|
| COMMIT実行時間 | 6.15 s | 17.41 s |
| COMMIT CPU時間 | 4.34 s | 17.29 s |
| メモリ使用量 | +2.45GB | +3.03GB |
| トランザクションサイズ | 1.77GB | 498MB |
- UPDATE 1,000万件
| 圧縮OFF | 圧縮ON | |
|---|---|---|
| COMMIT実行時間 | 11.90 s | 35.77 s |
| COMMIT CPU時間 | 8.59 s | 35.40 s |
| メモリ使用量 | +4.13GB | +5.49GB |
| トランザクションサイズ | 3.55GB | 0.97GB |
トランザクションサイズに伴い、COMMITの実行時間やCPU/メモリ使用量が増加していることがわかります。
大きいトランザクションが頻繁に流れるワークロードではパフォーマンスに影響を与える可能性があるので、ここは注意が必要そうです。
注意点
巨大トランザクションによるレプリケーションエラー
MySQL 8.0.34未満では、圧縮しても1GBを超えるトランザクションがCOMMITされた場合、サーバがバイナリログからそのトランザクションが読み取れず、レプリケーションエラーが発生してしまいます。
// レプリカ側でこのようなエラーログが出る
2023-08-27T09:24:47.799386+09:00 144833 [ERROR] [MY-010557] [Repl] Error reading packet from server for channel '': log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event '' at 4, the last event read from './mysqld-bin.054885' at 304669560, the last byte read from './mysqld-bin.054885' at 304669579. (server_errno=1236)
2023-08-27T09:24:47.799727+09:00 144833 [ERROR] [MY-013114] [Repl] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event '' at 4, the last event read from './mysqld-bin.054885' at 304669560, the last byte read from './mysqld-bin.054885' at 304669579.', Error_code: MY-013114
通常、トランザクションはbinlog_row_event_max_size(=8K)あるいは1レコード毎にバイナリログイベントとして分割されますが、トランザクションペイロード圧縮が有効な場合はトランザクションとバイナリログイベントが1:1となります。
そのため、上限1GBを超えたバイナリログイベントに書き込まれ、バイナリログが実質的に破損してしまうのがこの問題の原因です。
8.0.34からは、圧縮後1GBを超える場合は圧縮しないように修正されています。
巨大トランザクションが実行される環境では、8.0.34以上にするのが安全でしょう。
まとめ
-
Pros
-
binlog_transaction_compression=ONを本番導入した環境では圧縮率90%を達成 - ソース/レプリカ間のバイナリログ転送時間やNW帯域使用量も削減
-
-
Cons
- COMMITのレイテンシが悪化
- sync_binlog=1でCOMMIT時にbinlogへの書き込みも行われるため圧縮コストが追加でかかる
- CPU時間も増加
- 細かいトランザクションでは影響はほぼなし
- 巨大トランザクションではレイテンシが悪化しやすくなる
- tx size 236MB: +2.4sec
- tx size 1.77GB: +13sec
- tx size 3.55GB: +24sec
- メモリ使用量が増加
- 巨大トランザクションでは非圧縮と比べて使用量が増加する
- tx size 236MB: +29MB
- tx size 1.77GB: +593MB
- tx size 3.55GB: +1.36GB
- 巨大トランザクションでは非圧縮と比べて使用量が増加する
- COMMITのレイテンシが悪化
書き込みが多いワークロードではバイナリログサイズ削減が大きく見込める一方で、パフォーマンスやCPU/メモリ使用率は悪化する可能性があります。
そのため、いきなり導入するより事前に負荷試験が行えると安全です。
おまけ: SET PERSISTで設定する場合はご注意を
SET PERSISTは8.0から導入されたシステム変数の変更と永続化が同時に行える便利な機能ですが、SET PERSISTによって設定したパラメーターは再起動時に、IO・SQLスレッドなどに反映されないバグがあります。(最新8.0.36の現在)
https://bugs.mysql.com/bug.php?id=114125
レプリカのSQLスレッドでbinlog_transaction_compressionがOFFだと圧縮は行われないので、永続化したい場合はmy.cnfへ記載するようにしましょう。