はじめに
どうも、共通新人研修がビジネス職よりで割とコードとか書いている暇がなかったけんつです。
今週からはエンジニアの研修で RFC と格闘することが強制されて息を吹き返してます。
MySQL 8.0.20 がリリースされて、めちゃくちゃ気になったのが binlog 圧縮。
他にも検証をしていたのだけど、なんか急に気になったので全てを放り投げて調べてみたくなった。
zstd というアルゴリズムを用いて binlog を可逆圧縮する機能が追加になったので、どれだけ圧縮されるのか調べてみた。
MySQL の運用に関わったことがなく、完全に趣味で追っているので binlog 関連の検証として正しい方法なのかはわかっていないので間違っているところがあったり、改善点があれば教えて欲しい。
リリースノートの文献
以下のリリースノートにある、引用部分を参照する。
dev.mysql.com
From MySQL 8.0.20, you can enable binary log transaction compression on a MySQL server instance. When binary log transaction compression is enabled, transaction payloads are compressed using the zstd algorithm, and then written to the server's binary log file as a single event (a Transaction_payload_event). Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replication slaves, other Group Replication group members, or clients such as mysqlbinlog. They are not decompressed by receiver threads, and are written to the relay log still in their compressed state. Binary log transaction compression therefore saves storage space both on the originator of the transaction and on the recipient (and for their backups), and saves network bandwidth when the transactions are sent between server instances.
You can enable binary log transaction compression on a MySQL server instance using the binlog_transaction_compression system variable, which defaults to OFF. You can also use the binlog_transaction_compression_level_zstd system variable to set the level for the zstd algorithm that is used for compression. This value determines the compression effort, from 1 (the lowest effort) to 22 (the highest effort).
ハイパー雑に和訳すると。
MySQL 8.0.20 からサーバインスタンスでバイナリトランザクションを圧縮することが可能になった。
圧縮する場合、トランザクションペイロードは zstd アルゴリズムを使用してバイナリログを圧縮する。
このときサーバはバイナリログファイルに１つのイベントとして書き込む。
スタンドアローン時やレプリケーション時を問わず圧縮された状態で送信され、受信時に解凍されない。
この特性によりトランザクションの送信、受信においてストレージ領域とネットワーク領域を節約する。
やってみた
MySQL 8.0.19, 8.0.20 の docker コンテナを起動して mysql client に入ってから以下の sql ファイルを実行する。
mysql> source sql/class.up.sql
mysql> source sql/hashjoin.up.sql
この状態で ./docker/mysql8019|mysql8020/data/ 以下の binlog ファイルのサイズを確認する。
// MySQL 8.0.19
-rw-r----- 1 hoge hoge 3102158 4 29 03:01 binlog.000001
-rw-r----- 1 hoge hoge 890060 4 29 03:03 binlog.000002
// MySQL 8.0.20
-rw-r----- 1 hoge hoge 298346 4 29 03:01 binlog.000001
-rw-r----- 1 hoge hoge 130275 4 29 03:02 binlog.000002
// MySQL 8.0.20 without zstd compression
-rw-r----- 1 hoge hoge 3102159 4 29 03:55 binlog.000001
-rw-r----- 1 hoge hoge 890061 4 29 03:56 binlog.000002
大体 binlog.000001 は 1/10 ~ 1/9 あたりのサイズになっているっぽい。
binlog ファイルの分割が発生した上で binlog.000001 のサイズが縮小しているのでおそらくそのぐらい圧縮されるとみても良いのか？というところ。
binlog.000002 に関しては 1/9 ほどになっている。
これだと怪しいのでみんな大好き sakila sample database で試してみた。
// MySQL 8.0.20
❯ ll ./docker/mysql8020/data | grep "binlog"
-rw-r----- 1 hoge hoge 298170 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 156 4 29 10:57 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
// MySQL 8.0.19
~/mysqlProject/mysql-poc master*
❯ ll ./docker/mysql8019/data | grep "binlog"
-rw-r----- 1 hoge hoge 3102158 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 155 4 29 10:57 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
この状態で以下のファイルを実行すると
mysql> source sakila-db/sakila-schema.sql;
mysql> source sakila-db/sakila-data.sql;
// MySQL 8.0.20
~/mysqlProject/mysql-poc master* 1m 59s
❯ ll ./docker/mysql8020/data | grep "binlog"
-rw-r----- 1 hoge hoge 298170 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 590183 4 29 11:01 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
// MySQL 8.0.19
~/mysqlProject/mysql-poc master*
❯ ll ./docker/mysql8019/data | grep "binlog"
-rw-r----- 1 hoge hoge 3102158 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 1359983 4 29 11:01 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
大体 1/2 程度、ファイルサイズが減少している。
もうちょっと binlog にいろいろ突っ込みたいので、sakila db を drop してもう一回やり直した場合、次のようになった。
~/mysqlProject/mysql-poc master* 24s
❯ ll ./docker/mysql8020/data | grep "binlog"
-rw-r----- 1 hoge hoge 298170 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 1180403 4 29 11:13 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
~/mysqlProject/mysql-poc master*
❯ ll ./docker/mysql8019/data | grep "binlog"
-rw-r----- 1 hoge hoge 3102158 4 29 10:57 binlog.000001
-rw-r----- 1 hoge hoge 2719998 4 29 11:13 binlog.000002
-rw-r----- 1 hoge hoge 32 4 29 10:57 binlog.index
1/2 ~ 1/3 あたりをフワフワしている。
perfomance_shema をみてみる
binary_log_transaction_compression_stats をみるとどの程度圧縮されたのか、圧縮適用外のトランザクションがどれぐらいあったかをみることができる。
MySQL :: MySQL 8.0 Reference Manual :: 26.12.11.11 The binary_log_transaction_compression_stats Table
みてみると、大体 58% ほど圧縮されているようなので大体ただしいのかなといったところ。
mysql> select * from binary_log_transaction_compression_stats\G;
*************************** 1. row ***************************
LOG_TYPE: BINARY
COMPRESSION_TYPE: ZSTD
TRANSACTION_COUNTER: 60
COMPRESSED_BYTES_COUNTER: 2249088
UNCOMPRESSED_BYTES_COUNTER: 5293704
COMPRESSION_PERCENTAGE: 58
FIRST_TRANSACTION_ID: ANONYMOUS
FIRST_TRANSACTION_COMPRESSED_BYTES: 2185
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 4310
FIRST_TRANSACTION_TIMESTAMP: 2020-04-29 02:32:49.437426
LAST_TRANSACTION_ID: ANONYMOUS
LAST_TRANSACTION_COMPRESSED_BYTES: 193
LAST_TRANSACTION_UNCOMPRESSED_BYTES: 212
LAST_TRANSACTION_TIMESTAMP: 2020-04-29 02:36:34.471615
*************************** 2. row ***************************
LOG_TYPE: BINARY
COMPRESSION_TYPE: NONE
TRANSACTION_COUNTER: 151
COMPRESSED_BYTES_COUNTER: 94042
UNCOMPRESSED_BYTES_COUNTER: 94042
COMPRESSION_PERCENTAGE: 0
FIRST_TRANSACTION_ID: ANONYMOUS
FIRST_TRANSACTION_COMPRESSED_BYTES: 105
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 105
FIRST_TRANSACTION_TIMESTAMP: 2020-04-29 02:32:45.334943
LAST_TRANSACTION_ID: ANONYMOUS
LAST_TRANSACTION_COMPRESSED_BYTES: 209
LAST_TRANSACTION_UNCOMPRESSED_BYTES: 209
LAST_TRANSACTION_TIMESTAMP: 2020-04-29 02:36:34.452032
2 rows in set (0.00 sec)
余談
圧縮レベルを最大にして同じことを行い、ファイルサイズを比較しても大体 1/2 ~ 1/3 で推移していて「？」となった。
その辺の設定をみてみると " from 1 (the lowest effort) to 22 (the highest effort). " とあるので、もしかすると圧縮レベル以外に
何か別の要因がないと圧縮率は向上しない？
一回だけ雑に SQL を叩きまくったら 1/7 程度に減少したケースがあったけど再現しなかったので余談としている。要検証。
さいごに
binlog ファイルが良い感じに圧縮されると見ていいのか自信がないが物理的な容量が減少していることはわかった。大体半分ぐらいになるっぽい。
計算リソースについては言及していないので、圧縮レベルとファイルサイズがどのようにそれらと関わってくるかがわかっていない。
あと圧縮レベルを上げても満足に圧縮されないケースがあったので、このへんも努力的な意味合いでのレベルなのかそれとも強制力があるかどうかみたいなところは
まだまだ追っていく必要がありそう。
2020/04/29 4:42
MySQL が死ぬぐらいの SQL (source sql/hashjoin.up.sql をもう一回実行すると死ぬ)をぶん投げると 1/5 程度になることが確認できたけど
その場合、バイナリログの圧縮にどのような影響がでるのか要検証
2020/04/29 11:43
sakila sample database を使った場合に binlog.000002 のファイルサイズがどれくらい減少しているのか検証したので追加している。
2020/04/29 12:08
パフォーマンススキーマで確認できることがわかったので追記。
