【MySQL】肥大化したInnoDBテーブルを圧縮機能で縮小する方法！

こんにちは。インフラエンジニアの綿引です。

早速ですが、今回はMySQLのテーブル圧縮について記載したいと思います。
但し、MySQL 5.7から実装された透過性ページ圧縮でなく、
MySQL 5.1のInnoDB Plugin時代からある圧縮です！

個人で運用しているMySQLが5.6なのですが、
ストレージが逼迫して来たので、旧来の圧縮を試してみました。

MySQL 5.6以前で「ディスク容量が足りない！」という方がいらっしゃれば、
参考にして頂ければと思います。

圧縮の仕組み

まずは圧縮の仕組みについて図を作ってみました。

非圧縮ページ(16KB) と記載してあるものが通常のページだとお考え下さい。

今回、実施する圧縮の仕組みとしては、
通常はこの非圧縮ページがそのままストレージに保存される所を、
圧縮ページを作成しストレージに保存することによって、
ディスクの消費量を抑えられるというものです。

また select、insert などのテーブル操作も発行出来るという優れものです。
圧縮ページに操作を加える際は、バッファプール内で展開することで
クライアント側からの操作を可能にしています。

「んっ？ デメリット多いんじゃない？」と思われた方、デメリットは後述致しますね。
とりあえずやってみたいと思います！

パラメータの変更

まずは以下の2つのパラメータの変更です。

• innodb_file_per_table = 1
• innodb_file_format = Barracuda

innodb_file_per_table は 「テーブルごとに専用のテーブルスペースを作成する」 パラメータです。
有効にする”1″を設定します。

innodb_file_format は 「InnoDBのファイル形式を定義する」 パラメータです。
MySQL 5.6までのデフォルトは 「Antelope」 なのですが、
これを圧縮機能をサポートした最新の形式である 「Barracuda」に変更します。

尚、パラメータの内容は以下です。

Antelope では可変長カラム1つに対し 最大で768byteまでローカルページを消費するが、
Barracuda では可変長カラム1つに対し 最大で20byteしかローカルページを消費しないため、
最大行サイズの8KBに対し多く文字を格納出来る。という理解でいいかと。

因みに

「Antelope」はレイヨウ

「Barracuda」はオニカマス

らしいです。
オニカマスの圧が強いですね。

上記を変更後、確認します。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+

コマンド

続いて圧縮を行うコマンドを実施して行きます。

記載方法としては、
Create Table文や、Alter Table文に、
ROW_FORMAT句とKEY_BLOCK_SIZE句を付与することで圧縮が可能となります。

例としては以下です。

mysql> ALTER TABLE test ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;

ROW_FORMAT句とKEY_BLOCK_SIZE句について、
ROW_FORMAT句については先程のファイルのフォーマットに対して、行のフォーマットです。
全部で4種類あり、指定が可能ですが圧縮機能があるCOMPRESSEDに関しては、
ファイルフォーマットをBarracudaにしないと使えません。

次にKEY_BLOCK_SIZE句についてですが、
こちらは圧縮後のInnoDBのページサイズを指定するもので、
値としては1、2、4、8、16が設定出来ます。

InnoDBのデフォルトのページサイズは16KBのため、
例えば、8(KB)を指定した場合は容量が半分になります。

※ 但し、対象テーブルに対する圧縮が全て上手くいった場合です。
    圧縮する際に8KB以上となってしまう場合は、
    元ページを分割した後に、それぞれ圧縮を行うためその分容量が増えてしまうので注意です。

まずは既存テーブルのバックアップを取得します。

mysql> create table test_tbl2 like test_tbl;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into test_tbl2 select * from test_tbl;
Query OK, 1048407 rows affected (9 min 41.05 sec)
Records: 1048407  Duplicates: 0  Warnings: 0

因みにサイズはこのような感じです。

mysql> select table_name,engine,table_rows,avg_row_length,data_length
    -> from information_schema.tables
    -> where table_name like '%test%';
+------------+--------+------------+----------------+-------------+
| table_name | engine | table_rows | avg_row_length | data_length |
+------------+--------+------------+----------------+-------------+
| test       | InnoDB |     933488 |           2199 |        1958 |
| test2      | InnoDB |     930930 |           2218 |        1970 |

mysql> ALTER TABLE test2 ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (44 min 15.12 sec)

+------------+--------+------------+----------------+-------------+
| table_name | engine | table_rows | avg_row_length | data_length |
+------------+--------+------------+----------------+-------------+
| test       | InnoDB |     933488 |           2199 |        1958 |
| test2      | InnoDB |     832392 |           1229 |         976 |

avg_row_length と data_length がほぼ半分になりました。
table_rowsも若干減っています。これはおそらく Barracuda の効果でしょうか。

OSから確認したデータファイルのサイズもしっかり変更されております。

-rw-rw---- 1 mysql mysql 2327838720 2016-07-06 18:01 test.ibd
-rw-rw---- 1 mysql mysql 1031798784 2017-01-13 15:14 test2.ibd

圧縮出来ました。
因みに show table status で見るとこんな感じ。
圧縮後の確認の1つとして Row_format と Create_options を見ると確か。

mysql> show table status like 'test2'\G;
*************************** 1. row ***************************
           Name: test2
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 832392
 Avg_row_length: 1229
    Data_length: 1023410176
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1113463
    Create_time: 2017-01-13 15:14:41
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
        Comment: 
1 row in set (0.08 sec)

デメリット

実際にやってみて、効果があることが確認出来ました。
では冒頭で名言しなかったデメリットについて。

一つ目は負荷です。
テーブル操作を行うにあたり、必要に応じて圧縮・解凍を行うというパワーが必要となるため、
今までリソースに問題がなかったとしても、そこがネックになったりします。

二つ目はキャッシュヒット率です。
こちらは確かめたわけではないですが、
全体的なキャッシュヒット率が下がる可能性があると思います。
理由としては圧縮済みページと非圧縮ページがバッファプールに載るため
今まで運用上載っていたキャッシュがLRUにより追い出されることが想像されるためです。

まとめ

以上、検証は終了です。
個人的には効果も大きいけど、デメリットも大きいため場面を選ぶな。という印象でした。
今後はMySQL5.7の透過的ページの圧縮をやろうかなと思っております。

ご清覧頂きありがとうございました。
Wedding Parkでは一緒に技術のウエディングパークを創っていくエンジニアを募集しています。
興味のある方はぜひ一度気軽にオフィスに遊びにきてください。