(cache) MySQL5.7でInnoDBのTransparent Page Compressionを試してみる Part2

これはMySQL Casual Advent Calendar 2015の21日目のエントリです。
Part1に続いての内容となります。

今回の計測パターン

Part1ではデータ・INDEXが全てinnodb_buffer_pool_sizeに収まる量でしたが、今回は収まらない量、実サイズで141GBのデータで計測。
LinkBenchの実行オプションは前回と同じです

# ./bin/linkbench -c config/MyConfig.properties -D maxtime=3600 -D requests=10000000 -D requesters=64 -r

パラメータについて

最後にまとめますが、結構変更しました。無圧縮であれば変える事なく安定した性能を出せたのですが、やはりreadの展開負荷が増えるとパラメータ変更しないとどうにもならない感じでした。まだ改善出来そうな気がするけど、そこまでやると環境特化が過ぎるのでほどほどという感じです。

データ量について

zlib, lz4, none(無圧縮)で以下のようになっています。FBWorkload.propertiesでmaxid1 = 140000001として作成しています。

compression	innodb_page_size	du -sch	du -sch --apparent-size
lz4	16k	77GB	141GB
zlib	16k	70GB	141GB
none	16k	141GB	141GB

ベンチマーク前後の圧縮状況

zlibの場合

ベンチマーク実行前。

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        58.8689 |            1994.0352 |       4848.0000 |
| linkdb/linktable#P#p1  |        58.9474 |            2036.2070 |       4960.0000 |
| linkdb/linktable#P#p2  |        58.7449 |            1884.5313 |       4568.0000 |
| linkdb/linktable#P#p3  |        58.2566 |            5511.7969 |      13204.0000 |
| linkdb/linktable#P#p4  |        59.0217 |            1786.6523 |       4360.0000 |
| linkdb/linktable#P#p5  |        58.8554 |            2473.6133 |       6012.0000 |
| linkdb/linktable#P#p6  |        58.8582 |            1936.9570 |       4708.0000 |
| linkdb/linktable#P#p7  |        59.0226 |            2715.9844 |       6628.0000 |
| linkdb/linktable#P#p8  |        59.1500 |            1581.7109 |       3872.0000 |
| linkdb/linktable#P#p9  |        58.4090 |            3137.6250 |       7544.0000 |
| linkdb/linktable#P#p10 |        58.8885 |            1825.3516 |       4440.0000 |
| linkdb/linktable#P#p11 |        58.4348 |            3747.5156 |       9016.0000 |
| linkdb/linktable#P#p12 |        59.1133 |            1574.9570 |       3852.0000 |
| linkdb/linktable#P#p13 |        59.0273 |            2268.2500 |       5536.0000 |
| linkdb/linktable#P#p14 |        58.7831 |            1937.1953 |       4700.0000 |
| linkdb/linktable#P#p15 |        58.6023 |            3702.6133 |       8944.0000 |
| linkdb/counttable      |        61.7001 |            3021.0938 |       7888.0000 |
| linkdb/nodetable       |        29.1925 |           27674.3906 |      39084.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

[root@e5 ~]# du -sch /var/lib/mysql/zlib/linkdb/
70G     /var/lib/mysql/zlib/linkdb/
70G     total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/zlib/linkdb/
141G    /var/lib/mysql/zlib/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク実行後

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        26.9303 |            4059.7500 |       5556.0000 |
| linkdb/linktable#P#p1  |        25.9182 |            4237.4766 |       5720.0000 |
| linkdb/linktable#P#p2  |        27.0742 |            3821.3125 |       5240.0000 |
| linkdb/linktable#P#p3  |        25.4127 |           11453.6250 |      15356.0000 |
| linkdb/linktable#P#p4  |        25.6982 |            3744.8125 |       5040.0000 |
| linkdb/linktable#P#p5  |        24.8719 |            5255.9609 |       6996.0000 |
| linkdb/linktable#P#p6  |        26.1643 |            4010.7578 |       5432.0000 |
| linkdb/linktable#P#p7  |        25.5466 |            5709.0898 |       7668.0000 |
| linkdb/linktable#P#p8  |        25.1685 |            3364.4258 |       4496.0000 |
| linkdb/linktable#P#p9  |        26.3750 |            6402.4297 |       8696.0000 |
| linkdb/linktable#P#p10 |        26.0155 |            3788.0039 |       5120.0000 |
| linkdb/linktable#P#p11 |        25.4671 |            7808.0664 |      10476.0000 |
| linkdb/linktable#P#p12 |        25.0689 |            3350.9180 |       4472.0000 |
| linkdb/linktable#P#p13 |        26.1782 |            4709.8281 |       6380.0000 |
| linkdb/linktable#P#p14 |        27.2247 |            3912.3984 |       5376.0000 |
| linkdb/linktable#P#p15 |        25.8104 |            7689.0078 |      10364.0000 |
| linkdb/counttable      |        55.0439 |            3842.8516 |       8548.0000 |
| linkdb/nodetable       |        25.5240 |           32727.7305 |      43944.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

[root@e5 ~]# du -sch /var/lib/mysql/zlib/linkdb/
118G    /var/lib/mysql/zlib/linkdb/
118G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/zlib/linkdb/
162G    /var/lib/mysql/zlib/linkdb/
162G    total
[root@e5 ~]#

lz4の場合

ベンチマーク実行前

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        51.9957 |            2327.2500 |       4848.0000 |
| linkdb/linktable#P#p1  |        51.8411 |            2388.6836 |       4960.0000 |
| linkdb/linktable#P#p2  |        51.9117 |            2196.6719 |       4568.0000 |
| linkdb/linktable#P#p3  |        51.0858 |            6456.6758 |      13200.0000 |
| linkdb/linktable#P#p4  |        51.9142 |            2096.5391 |       4360.0000 |
| linkdb/linktable#P#p5  |        51.7082 |            2903.3008 |       6012.0000 |
| linkdb/linktable#P#p6  |        51.9340 |            2262.9453 |       4708.0000 |
| linkdb/linktable#P#p7  |        51.8711 |            3189.9805 |       6628.0000 |
| linkdb/linktable#P#p8  |        52.0150 |            1859.8984 |       3876.0000 |
| linkdb/linktable#P#p9  |        51.5774 |            3653.0039 |       7544.0000 |
| linkdb/linktable#P#p10 |        51.8585 |            2137.4844 |       4440.0000 |
| linkdb/linktable#P#p11 |        51.0880 |            4407.9492 |       9012.0000 |
| linkdb/linktable#P#p12 |        51.9364 |            1851.4102 |       3852.0000 |
| linkdb/linktable#P#p13 |        51.9473 |            2658.2734 |       5532.0000 |
| linkdb/linktable#P#p14 |        51.8707 |            2264.0000 |       4704.0000 |
| linkdb/linktable#P#p15 |        51.6060 |            4332.2305 |       8952.0000 |
| linkdb/counttable      |        59.2718 |            3212.6406 |       7888.0000 |
| linkdb/nodetable       |        28.4740 |           27983.8320 |      39124.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

[root@e5 ~]# du  -sch /var/lib/mysql/lz4/linkdb/
77G     /var/lib/mysql/lz4/linkdb/
77G     total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/lz4/linkdb/
141G    /var/lib/mysql/lz4/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク実行後

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        24.2162 |            4228.7383 |       5580.0000 |
| linkdb/linktable#P#p1  |        23.3699 |            4401.6328 |       5744.0000 |
| linkdb/linktable#P#p2  |        24.3990 |            3976.6133 |       5260.0000 |
| linkdb/linktable#P#p3  |        22.8851 |           11903.4609 |      15436.0000 |
| linkdb/linktable#P#p4  |        23.0805 |            3898.2813 |       5068.0000 |
| linkdb/linktable#P#p5  |        22.3430 |            5460.8438 |       7032.0000 |
| linkdb/linktable#P#p6  |        23.6796 |            4164.0391 |       5456.0000 |
| linkdb/linktable#P#p7  |        22.9749 |            5937.0977 |       7708.0000 |
| linkdb/linktable#P#p8  |        22.6060 |            3498.2070 |       4520.0000 |
| linkdb/linktable#P#p9  |        23.7852 |            6658.1250 |       8736.0000 |
| linkdb/linktable#P#p10 |        23.4115 |            3936.6484 |       5140.0000 |
| linkdb/linktable#P#p11 |        22.8490 |            8119.3672 |      10524.0000 |
| linkdb/linktable#P#p12 |        22.5232 |            3480.2578 |       4492.0000 |
| linkdb/linktable#P#p13 |        23.5010 |            4902.0586 |       6408.0000 |
| linkdb/linktable#P#p14 |        24.5686 |            4076.3125 |       5404.0000 |
| linkdb/linktable#P#p15 |        23.2421 |            7995.0977 |      10416.0000 |
| linkdb/counttable      |        51.3861 |            4196.3516 |       8632.0000 |
| linkdb/nodetable       |        24.9594 |           33062.8867 |      44060.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

[root@e5 ~]# du  -sch /var/lib/mysql/lz4/linkdb/
121G    /var/lib/mysql/lz4/linkdb/
121G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/lz4/linkdb/
162G    /var/lib/mysql/lz4/linkdb/
162G    total
[root@e5 ~]#

none(無圧縮)の場合

ベンチマーク実行前。MySQLのinformation_schemaに対するクエリは無圧縮のためエラーになりますが一応載せておきます。

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`information_schema`.`INNODB_SYS_TABLESPACES`.`FILE_SIZE` - `information_schema`.`INNODB_SYS_TABLESPACES`.`ALLOCATED_SIZE`)'
mysql>

[root@e5 ~]# du -sch /var/lib/mysql/none/linkdb/
141G    /var/lib/mysql/none/linkdb/
141G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/none/linkdb/
141G    /var/lib/mysql/none/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク後

[root@e5 ~]# du -sch /var/lib/mysql/none/linkdb/
163G    /var/lib/mysql/none/linkdb/
163G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/none/linkdb/
163G    /var/lib/mysql/none/linkdb/
163G    total
[root@e5 ~]#

ベンチマーク結果

compress	スコア
lz4	9625
zlib	7966
none	11041

予想通りといえばそれまでの結果で none > lz4 > zlib の順に速い結果となりました。

考察

zlib, lz4, noneのベンチマーク中のグラフを見ていきます。グラフの順番もzlib, lz4, noneになります。

noneに比べるとzlib、lz4はグラフがかなりジグザグであまり安定しておらず、ベンチマーク実行から5分程度で性能が落ちていることが分かります。そのタイミングはInnoDB バッファプールのFree Buffersが枯渇したタイミングとほぼ一致します。

noneのケースでFree buffersが多少残っているのはinnodb_lru_scan_depthにより空きがきちんと確保されているためです。zlib, lz4のどちらも上手く空き領域が確保出来ていません。そのため、パラメータを変更したと先に記載したとおり、innodb_io_capacity=80000, innodb_io_capacity_max=120000としてzlib, lz4のベンチマークを実行しています。
他にinnodb_lru_scan_depthの設定値どおり空きが確保出来ていないため、innodb_page_cleanersを論理CPUコア数と同じ24にするためにinnodb_buffer_pool_instancesを24に変更、その結果innodb_buffer_pool_size = 42Gとしています。チャンクサイズのせいか40Gとしても42Gに調整されたためです。

History list lengthもzlib, lz4は跳ねる事があり、zlibは特に顕著でした。

最初傾向が異なる事を前提に、innodb_purge_threadsをデフォルトの4のままベンチマークを流しましたがHistory list lengthが増加の一途だったためzlib, lz4についてはinnodb_purge_threadsを8にして測定しています。
それでも以下のようにpurge_invokedが全然増えない時間帯があるため、今回の測定ケースでzlibを使う場合はinnodb_purge_threads, innodb_io_capacityをもっと増やしても良いかもしれません。

無圧縮のpurge_invokedの安定感を見ると安心します。

interrupt, context switchについては zlib > lz4 > none の順で高くなっており、そのあたりも性能に影響を及ぼしていると考えられます。

原因が分かっていませんがzlibの場合、fsyncの数が他と比較して結構高くなっていました。こちらもzlibが遅い原因の一つになっていそうです。zlib, lz4、共にwriteが多いのは確かですが、それにしてもlz4以上にzlibはfsyncが多いのが謎です。

お試しで測定した際、zlib, lz4双方の特徴としてwait/sync/mutex/innodb/buf_dblwr_mutexの待ち時間が長い傾向にありました。innodb_autoextend_incrementを8に下げて測定した所、待ち時間が多少減少し、性能も改善する傾向にありましたので今回の測定ではinnodb_autoextend_increment=8で測定しています。noneではinnodb_autoextend_increment=32としています。減らすことでwait/io/file/innodb/innodb_data_fileは増加する傾向でしたが、今回の測定環境ではそれを補って改善する結果となりました。
このあたりは使用するioデバイスの性能で変わる可能性が大いに考えられるため適宜調整すると良いでしょう。

やや見辛いと思いますのでwait/sync/mutex/innodb/buf_dblwr_mutexのWaits, Timeのみ取り出したのグラフを載せておきます。

その他のグラフを適当に載せておきます。

buffer_flush_n_to_flush_requestedがzlib, lz4で大きいのはinnodb_io_capacityの設定がnoneよりも大きい設定にしているためです。

まとめ

個人的にはTransparent Page Compressionを使うならlz4を採用したい所です。圧縮率はzlibの方が良いようですので少しでもディスク使用量を減らしたい場合はワークロードと相談しつつ決める事になるかと思います。今回はLinkBenchで使用される3テーブル全てを圧縮して計測しましたが、全てのテーブルを圧縮する事はそんなに無いと思いますのでテーブルのデータ構造による圧縮率を考慮しつつ使用すると良いかと思います。
ただ、圧縮を使った場合はパラメータ調整がかなり面倒な感じなのでサービスで使うテーブルで使用する場合は十分に検証を行う必要がありそうです。溜め込む形のテーブルでバッチ処理のみでの使用であれば割と大雑把な設定でも動くんじゃないかと思いますし、圧縮のせいで遅いとなったらさっさとALTER発行してnoneに変えてしまえば良いでしょう。

もうちょっとパラメータいじったPart3やるかは不明です。

最後にパラメータ載せておきます。

none(無圧縮)のパラメータ

skip-name-resolve
skip-external-locking
max_allowed_packet = 16M
query_cache_type = 0
query_cache_size = 0
thread_cache_size = 1024
table_open_cache = 4096
table_open_cache_instances = 24

performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 24
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0
innodb_io_capacity = 12000
innodb_io_capacity_max = 15000
innodb_lru_scan_depth = 4000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0
innodb_read_ahead_threshold = 0
innodb_sync_array_size = 24
innodb_autoextend_increment = 32

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

log_timestamps = SYSTEM
innodb_page_cleaners = 24
innodb_numa_interleave = ON

zlib, lz4の時のパラメータ

skip-name-resolve
skip-external-locking
max_allowed_packet = 16M
query_cache_type = 0
query_cache_size = 0
thread_cache_size = 1024
table_open_cache = 4096
table_open_cache_instances = 24

performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 24
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 8
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0
innodb_io_capacity = 80000
innodb_io_capacity_max = 120000
innodb_lru_scan_depth = 3000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0
innodb_read_ahead_threshold = 0
innodb_sync_array_size = 24
innodb_autoextend_increment = 8

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

log_timestamps = SYSTEM
innodb_page_cleaners = 24
innodb_numa_interleave = ON