Hatena::ブログ(Diary)

hiroi10の日記

2015-12-06

MySQL5.7でInnoDBのTransparent Page Compressionを試してみる Part1

このエントリはMySQL Casual Advent Calendar 2015の6日目のエントリです。
Part1としているのは12/21にPart2を公開予定のためです。

InnoDBのTransparent Page Compression

MySQL 5.7からInnoDBにTransparent Page Compressionという圧縮機能が追加されました。InnoDBの圧縮機能はMySQL 5.1のInnoDB Pluginから使用可能でしたが、これまでのInnoDB圧縮と違い、Transparent Page Compressionは使用環境の制限があります。具体的にはFileSystemがhole punchingをサポートしている必要があります。
MySQL Server Blogからの抜粋ですが以下のようにKernelのバージョン制限があります。
InnoDB Transparent Page Compression

よく使われているであろうCentOSでは7系であれば標準のKernelで利用可能です。
2016/01/06追記: 5.7.8-rc以降、CentOS6の2.6.32-504.12.2.el6.x86_64では使えることを確認しました。なおLinux genericだとダメなもよう。RHEL/Oracle Linux用のrpmだと使用可のようです。
利用可能な場合はMySQL起動時に以下のようなログが出力されます。

[Note] InnoDB: PUNCH HOLE support available

サポートされている圧縮形式はzlibとlz4の2つです。

注意点

特徴として*.ibdファイルはsparseファイルになるのでコールドバックアップを行っている場合は注意が必要です。
またxfsでのsparseファイルの挙動がやや怪しいので現時点ではext4か可能ならnvmfsでの使用が推奨になるかと思います。
InnoDB deadlock, thread stuck on kernel calls from transparent page compression

利用方法

サポートされているKernel環境であれば使用する際はこれまでのInnoDBの圧縮機能とほとんど変わらず、CREATE TABLE文に記載する形になります。

CREATE TABLE `t1` (
 id int,
 primary key (id)
) ENGINE=InnoDB COMPRESSION="zlib";

これまでのInnoDB圧縮より構文はシンプルかもしれません。
COMPRESSIONで指定可能な項目は zlib, lz4, noneの3つです。これまでのInnoDB圧縮との大きな違いは途中で圧縮を無効にしたい、有効にしたい、といった場合は以下のようにALTER文を実行しますがメタデータの変更しか発生しないためデータ量に関わらず即座に応答が返ってきます。

mysql> ALTER TABLE counttable COMPRESSION="none";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE counttable COMPRESSION="lz4";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

対象ページへの次の書き込みから圧縮・解凍されたデータが書き込まれる形となるので多少好みが分かれるかもしれません。
強制的に全ページを変更後の内容(zlib, lz4, none)に書き換えたい場合はOPTIMIZE TABLEを使う必要があります。

圧縮率

今回はLinkBenchのデータ(maxid1 = 20000001)のデータで確認しました。innodb_page_sizeは4k, 8k, 16k, 32k, 64kと全て試してみました(16kがデフォルト)。なお先にも記載した通りsparseファイルとなるためサイズの確認にはduコマンドに--apparent-sizeオプションの有無で確認しています。innodb_page_size 16kの場合の詳細な値は後述します。


compressioninnodb_page_sizedu -schdu -sch --apparent-size
lz44k21GB26GB
lz48k17GB25GB
lz416k13GB24GB
lz432k11GB25GB
lz464k9.8GB26GB
zlib4k21GB26GB
zlib8k13GB25GB
zlib16k11GB24GB
zlib32k8.7GB25GB
zlib64k8.0GB26GB

各テーブルの圧縮率をMySQLから確認する場合は以下のようになります。

innodb_page_size=16kで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  |        62.5583 |             353.4492 |        944.0000 |
| linkdb/linktable#P#p1  |        63.5606 |             230.2969 |        632.0000 |
| linkdb/linktable#P#p2  |        61.3289 |             580.0664 |       1500.0000 |
| linkdb/linktable#P#p3  |        62.3612 |             414.0273 |       1100.0000 |
| linkdb/linktable#P#p4  |        61.7483 |             368.7461 |        964.0000 |
| linkdb/linktable#P#p5  |        62.1545 |             534.3789 |       1412.0000 |
| linkdb/linktable#P#p6  |        62.0091 |             784.1328 |       2064.0000 |
| linkdb/linktable#P#p7  |        63.0929 |             329.2109 |        892.0000 |
| linkdb/linktable#P#p8  |        62.6877 |             343.2734 |        920.0000 |
| linkdb/linktable#P#p9  |        63.6906 |             219.3086 |        604.0000 |
| linkdb/linktable#P#p10 |        61.4523 |             488.7852 |       1268.0000 |
| linkdb/linktable#P#p11 |        63.9216 |             202.0391 |        560.0000 |
| linkdb/linktable#P#p12 |        62.2676 |             250.5430 |        664.0000 |
| linkdb/linktable#P#p13 |        62.1705 |             550.7969 |       1456.0000 |
| linkdb/linktable#P#p14 |        61.9548 |             590.4609 |       1552.0000 |
| linkdb/linktable#P#p15 |        63.2757 |             308.4844 |        840.0000 |
| linkdb/counttable      |        61.5928 |             434.7695 |       1132.0000 |
| linkdb/nodetable       |        29.3900 |            3951.3359 |       5596.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql> 

innodb_page_size=16kで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  |        55.4050 |             420.9766 |        944.0000 |
| linkdb/linktable#P#p1  |        56.7123 |             273.5781 |        632.0000 |
| linkdb/linktable#P#p2  |        53.2847 |             702.5977 |       1504.0000 |
| linkdb/linktable#P#p3  |        54.7643 |             499.4023 |       1104.0000 |
| linkdb/linktable#P#p4  |        53.4013 |             447.3477 |        960.0000 |
| linkdb/linktable#P#p5  |        53.9754 |             649.8672 |       1412.0000 |
| linkdb/linktable#P#p6  |        54.0529 |             948.3477 |       2064.0000 |
| linkdb/linktable#P#p7  |        55.7346 |             394.8477 |        892.0000 |
| linkdb/linktable#P#p8  |        55.5176 |             409.2383 |        920.0000 |
| linkdb/linktable#P#p9  |        56.7004 |             263.2617 |        608.0000 |
| linkdb/linktable#P#p10 |        53.5631 |             588.8203 |       1268.0000 |
| linkdb/linktable#P#p11 |        56.8499 |             241.6406 |        560.0000 |
| linkdb/linktable#P#p12 |        54.0886 |             304.8516 |        664.0000 |
| linkdb/linktable#P#p13 |        54.4187 |             661.8398 |       1452.0000 |
| linkdb/linktable#P#p14 |        54.2518 |             710.0117 |       1552.0000 |
| linkdb/linktable#P#p15 |        55.7080 |             370.2813 |        836.0000 |
| linkdb/counttable      |        59.3272 |             458.7891 |       1128.0000 |
| linkdb/nodetable       |        28.2260 |            3999.2461 |       5572.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

圧縮率だけ見るとzlibの方が良さそうです。なおALTER文でCOMPRESSION=noneに全てのテーブルを変更したあとにLinkBenchを1時間実行した場合、以下のようにcompressed_pctが当然のことながら減少しました。

innodb_page_size=16kでzlib->noneのベンチマーク実行後
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 inf
ormation_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        20.6476 |             831.6133 |       1048.0000 |
| linkdb/linktable#P#p1  |        22.5837 |             529.5273 |        684.0000 |
| linkdb/linktable#P#p2  |        17.8877 |            1468.1680 |       1788.0000 |
| linkdb/linktable#P#p3  |        18.6294 |            1025.2695 |       1260.0000 |
| linkdb/linktable#P#p4  |        17.1622 |             947.6641 |       1144.0000 |
| linkdb/linktable#P#p5  |        17.2568 |            1366.9180 |       1652.0000 |
| linkdb/linktable#P#p6  |        18.7089 |            1937.9805 |       2384.0000 |
| linkdb/linktable#P#p7  |        20.2312 |             781.7344 |        980.0000 |
| linkdb/linktable#P#p8  |        20.9004 |             806.8164 |       1020.0000 |
| linkdb/linktable#P#p9  |        21.3016 |             513.1133 |        652.0000 |
| linkdb/linktable#P#p10 |        18.2654 |            1219.4805 |       1492.0000 |
| linkdb/linktable#P#p11 |        20.0674 |             489.1875 |        612.0000 |
| linkdb/linktable#P#p12 |        15.6215 |             664.9023 |        788.0000 |
| linkdb/linktable#P#p13 |        19.8355 |            1333.9375 |       1664.0000 |
| linkdb/linktable#P#p14 |        20.3784 |            1410.8945 |       1772.0000 |
| linkdb/linktable#P#p15 |        19.2842 |             749.0430 |        928.0000 |
| linkdb/counttable      |         0.1590 |            2168.5469 |       2172.0000 |
| linkdb/nodetable       |         0.2112 |            7771.5547 |       7788.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.01 sec)

mysql>

innodb_page_size=16kでlz4->noneのベンチマーク実行後
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  |        19.1485 |             847.3242 |       1048.0000 |
| linkdb/linktable#P#p1  |        21.0874 |             536.6055 |        680.0000 |
| linkdb/linktable#P#p2  |        16.2208 |            1494.6211 |       1784.0000 |
| linkdb/linktable#P#p3  |        17.1224 |            1044.2578 |       1260.0000 |
| linkdb/linktable#P#p4  |        14.9215 |             969.8945 |       1140.0000 |
| linkdb/linktable#P#p5  |        15.5323 |            1395.4063 |       1652.0000 |
| linkdb/linktable#P#p6  |        17.0397 |            1977.7734 |       2384.0000 |
| linkdb/linktable#P#p7  |        18.5834 |             797.8828 |        980.0000 |
| linkdb/linktable#P#p8  |        19.3214 |             822.9219 |       1020.0000 |
| linkdb/linktable#P#p9  |        20.0094 |             524.7383 |        656.0000 |
| linkdb/linktable#P#p10 |        16.7346 |            1242.3203 |       1492.0000 |
| linkdb/linktable#P#p11 |        18.5290 |             495.3438 |        608.0000 |
| linkdb/linktable#P#p12 |        14.0730 |             673.6680 |        784.0000 |
| linkdb/linktable#P#p13 |        18.2792 |            1353.2969 |       1656.0000 |
| linkdb/linktable#P#p14 |        18.2571 |            1451.7539 |       1776.0000 |
| linkdb/linktable#P#p15 |        17.4944 |             765.6523 |        928.0000 |
| linkdb/counttable      |         0.1593 |            2168.5391 |       2172.0000 |
| linkdb/nodetable       |         0.2220 |            7750.7578 |       7768.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

LinkBenchの実行

今回はinnodb_page_size=16kでzlib, lz4, noneで行いました。しかし、先に記載した通り、今回のデータ量ではinnodb_buffer_pool_sizeにデータ・INDEXが全て入ってしまう状態であったため明確な差が出ませんでした。これはwriteの時のみ圧縮すれば良い状態になってしまったためと考えられます。
DBの圧縮ではreadの展開の方が頻度が多く、writeのように1回だけ行えば良い訳では無いからです。そのためPart2ではバッファに載り切らない状態での測定を予定しています。

  • ベンチマーク環境
  • 実行コマンド
    • ./bin/linkbench -c config/MyConfig.properties -D maxtime=3600 -D requests=10000000 -D requesters=64 -r

innodb_page_cleanerを24に設定し、その他の設定は先日のはてダと同一です。

以下ベンチマーク結果です。

compressスコア
lz435316
zlib33371
none34673

なんかlz4が一番高くなりました。。。圧縮でwrite量が減ったのかな?、という謎。CPU使用率は各コアidleが30%ぐらいあったのでlz4のように高速性が売りなものだとこんな結果になるのかもしれません。

またlz4, zlibでデータを作成し、ALTERでCOMPRESSION=noneとした場合は以下のようになりました。

compressスコア
lz4->none33848
zlib->none34576

なんか不思議な感じです。

以下ベンチマーク中のグラフです。順番はzlib, lz4, zlib->none, lz4->none, noneの計5パターンです。
f:id:hiroi10:20151206002843p:image:w640
f:id:hiroi10:20151206003051p:image:w640
f:id:hiroi10:20151206003230p:image:w640
f:id:hiroi10:20151206003312p:image:w640
f:id:hiroi10:20151206003523p:image:w640
f:id:hiroi10:20151206003737p:image:w640
f:id:hiroi10:20151206003752p:image:w640
f:id:hiroi10:20151206004029p:image:w640

まとめ

使用についてはこれまでのInnoDBの圧縮よりは気軽に使える感じですが、Kernelが比較的新しいバージョンでないと使えないのが難点かと思います。
データ量がinnodb_buffer_pool_sizeに全て入るうちは明確な差が出ませんでした。Part2では100GB以上のデータを用意して傾向がどう変わるか見ていく予定です。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/hiroi10/20151206/1449330983
Connection: close