MySQL 5.7の透過的ページ圧縮

MySQL 5.7で実装された新機能に、透過的ページ圧縮があります。
先に[取り上げた](http://qiita.com/hmatsu47/items/476d446887244de17ae4)透過的データ暗号化よりも古く、MySQL 5.7がGAになる前から存在する機能です。
以下に紹介記事があります。

[InnoDBの透過的ページ圧縮(MySQL Server Blogより)](https://yakst.com/ja/posts/2933)
[MySQLの新しいInnoDB ページI/O圧縮機能について解析してみた](http://d.ballade.jp/2014/04/innodb-compress.html)

また、[『詳解MySQL5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド』](http://www.shoeisha.co.jp/book/detail/9784798147406)にも取り上げられています。

これらの記事にあるように、本来はランダムアクセスに強く、IOPSの高いSSD／フラッシュストレージ向けの機能ですが、HDDを使った外部ストレージから仮想ボリュームを切り出して使う場合にどうなるのか、念のため試してみました。

# 透過的ページ圧縮とは
上のリンクの記事や書籍で詳しく説明されているので追加の説明は不要かもしれませんが、MySQL（InnoDB）による圧縮／展開処理と、OSのファイルシステム側の機能（スパースファイル）を組み合わせて実現する圧縮機能です。
そのため、ファイルシステムは「スパースファイル」（穴あきファイル）の機能を持っている必要があります。CentOS 7ではxfs、ext4などが対象となります。

細かい点は省略しますが、

・DBのテーブルを格納するためのデータファイルを、一旦非圧縮の場合と同様の容量分確保する
・データを記録するときに内容を圧縮する⇒空き領域（ブロック）が生じる
・空きブロックを「解放」（⇒パンチホール）し、他のデータファイルの記録領域として「転用」できるようにする

という形で、実際の使用容量を節約します。

以前から、MySQL（InnoDB）には圧縮テーブルの機能がありますが、処理のオーバーヘッドが大きいことが弱点です。対して、透過的ページ圧縮は、オーバーヘッドが小さいとのことです。

# 透過的ページ圧縮の使い方
テーブル作成時、以下のように、「COMPRESSIONオプション」を付けるだけです。

```mysql:
CREATE TABLE テーブル名 (テーブル列定義など) ENGINE InnoDB COMPRESSION='圧縮方式';
```
圧縮方式は「lz4」と「zlib」から選択可能です。「zlib」の場合、「innodb_compression_level」（1～9、デフォルト6）で圧縮レベルの調整が可能です。

# テストの内容
テストでは、以下の点について調べてみました。

・容量がどの程度節約できるのか
・圧縮方式による違いはどの程度か
・ファイルシステムのブロックサイズによる圧縮率の差はどの程度か
・HDDを使った仮想ボリューム対応ストレージ（10G iSCSI RAID6）で使えるのか

具体的には、以下のような条件でSQLを発行しました。

・数値と文字列を含む可変長のレコードを10,000行INSERT
・INSERTしたレコードを主キーで10,000行SELECT
・そのうち1,000行をDELETE
・残った9,000行をSELECT
・以上を1セットとして、1テーブル当たり5回繰り返す
・以上を10テーブル分繰り返す

CREATE文の例（以下はzlib。innodb_compression_levelは6のまま）

```mysql:
CREATE TABLE test_data (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  num_us MEDIUMINT UNSIGNED NOT NULL,
  string_l VARCHAR(400) NOT NULL,
  num_s BIGINT NOT NULL,
  string_s VARCHAR(200) NOT NULL,
  PRIMARY KEY (id)
) ENGINE InnoDB COMPRESSION='zlib';
```

INSERT処理の例（一部。なお文字列のカラムには、IPAの辞書（2.7.0）から単語をランダムに抽出・連結したものを格納）

```mysql:
INSERT INTO test_data SET num_us = 26686, string_l = '引きたてん御懇切末摘花よせつけん演習はいつくばえ阿品', num_s = -35074963011, string_s ='にげりゃ鴻池本一乗寺門口おさめ沈み';
INSERT INTO test_data SET num_us = 35952, string_l = '調べ情深し利文内積まくしたてよ', num_s = -16134749084, string_s ='誑かし洽く香川医科大上鬼柳ぶらさがれ';
INSERT INTO test_data SET num_us = 11657, string_l = '輸すもの憂い浜松労災病院みずっぽうたかぶピンアップ葉月口篭りゃ', num_s = -54937824442, string_s ='キングストン冬島黒沢峠お蔭天譴';
INSERT INTO test_data SET num_us = 68980, string_l = 'ベロア常盤御前', num_s = 64605251476, string_s ='三菱ガス化学追使えぶっこぬこずらかん福浜西';
INSERT INTO test_data SET num_us = 81469, string_l = '海老乃屋慥かめくるめれ', num_s = 88241214441, string_s ='川端下すえたのもしかろ驚歎ついばん掻っ切れ';
（後略）
```

なお、DISK I/Oを発生させるため、my.cnfの設定はほぼデフォルトに近い状態にしてあります。バイナリログの書き出しもします。メモリは2GBです。

```text:/etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1

character-set-server=utf8
default_password_lifetime=0

log_bin=/var/log-bin/mysql-bin.log
sync_binlog=1
expire_logs_days=7
max_binlog_size=1024M
binlog_format=row

innodb_file_per_table=1
```

# 結果
以下のとおりになりました。
![image](https://qiita-image-store.s3.amazonaws.com/0/123899/6e2751d0-29f3-f675-5181-25d9c09eb852.png)

バイナリログの書き出しがあること、トランザクションを使わずオートコミットのままSQLを流したことなどが影響したのか、圧縮のオーバーヘッドは一般に言われている10%よりもかなり小さくなりました。

また、用意したデータがまずかった部分もあったのか、lz4はほとんど圧縮効果が出ませんでした。zlib（圧縮レベルはデフォルト指定）では10%強圧縮されました。このあたりは、対象データによって大きく変わると思います。

ファイルシステムのブロックサイズによる差は、思ったほど生じませんでした。

HDDストレージについても、サーバの内蔵HDDではなく外部の仮想ストレージであれば、必ずしも物理アドレスと論理アドレスが1:1で紐づくわけではないため、実用的に使える可能性はありそうです。
但し、今回はデータ量も処理量もかなり少なく、OSやストレージコントローラのキャッシュの効果が大きく出た可能性がありますし、ほぼ同じパターンでの繰り返しとなったため、不規則に書き換えられる環境では、よりフラグメンテーションが発生し、利用を続ける間にI/Oが遅くなってくる可能性があります（少なくとも、処理の順番を入れ替え、処理量を4倍にした程度では、傾向の変化は見られませんでしたが）。

# まとめ
テストの内容が不十分なため、これだけで結論を出すのは危険だと思いますが、HDDストレージ（単体サーバの内蔵HDDのことではないので注意）での利用が「絶対に無理」ということでもなさそうです。
OSだけでなく、外部ストレージが管理するブロックのサイズ等も考慮しながらチューニングし、注意深く実利用に即したテストを行う必要はありますが、最初からあきらめずに、試してみる価値はあるかもしれません。

---

**【おまけ】**
MySQL 5.7関連投稿記事へのリンクを集めました。

 * **[Qiitaに投稿したMySQL 5.7関連記事](http://hmatsu47.hatenablog.com/qiita_mysql57)**