こんにちは。インフラエンジニアの綿引です。
早速ですが、今回は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 | 可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の先頭768byteを B-tree ノードのインデックスレコードに格納し、 残りをオーバーフローページに格納する |
| Barracuda | 可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の全てを 外部のオーバーフローページに格納し、クラスタインデックスレコードに そのページへのポインタ(20byte)のみを格納する |
Antelope では可変長カラム1つに対し 最大で768byteまでローカルページを消費するが、
Barracuda では可変長カラム1つに対し 最大で20byteしかローカルページを消費しないため、
最大行サイズの8KBに対し多く文字を格納出来る。という理解でいいかと。
因みに
「Antelope」はレイヨウ
「Barracuda」はオニカマス
らしいです。
オニカマスの圧が強いですね。
上記を変更後、確認します。
|
1 2 3 4 5 6 |
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ |
|
1 2 3 4 5 6 |
mysql> show variables like 'innodb_file_format'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ |
コマンド
続いて圧縮を行うコマンドを実施して行きます。
記載方法としては、
Create Table文や、Alter Table文に、
ROW_FORMAT句とKEY_BLOCK_SIZE句を付与することで圧縮が可能となります。
例としては以下です。
|
1 |
mysql> ALTER TABLE test ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8; |
ROW_FORMAT句とKEY_BLOCK_SIZE句について、
ROW_FORMAT句については先程のファイルのフォーマットに対して、行のフォーマットです。
全部で4種類あり、指定が可能ですが圧縮機能があるCOMPRESSEDに関しては、
ファイルフォーマットをBarracudaにしないと使えません。
| ROW_FORMAT | 内容 |
| REDUNDANT | MySQL 5.0.3 よりも前で使用されていた COMPACTよりも効率性が低い |
| COMPACT | MySQL 5.0.3 以降でのデフォルト REDUNDANTよりもデータサイズが小さい |
| DYNAMIC | Barracuda とともにのみ使用可能 圧縮は行わない |
| COMPRESSED | Barracuda とともにのみ使用可能 圧縮に対応 |
次にKEY_BLOCK_SIZE句についてですが、
こちらは圧縮後のInnoDBのページサイズを指定するもので、
値としては1、2、4、8、16が設定出来ます。
InnoDBのデフォルトのページサイズは16KBのため、
例えば、8(KB)を指定した場合は容量が半分になります。
※ 但し、対象テーブルに対する圧縮が全て上手くいった場合です。
圧縮する際に8KB以上となってしまう場合は、
元ページを分割した後に、それぞれ圧縮を行うためその分容量が増えてしまうので注意です。
まずは既存テーブルのバックアップを取得します。
|
1 2 |
mysql> create table test_tbl2 like test_tbl; Query OK, 0 rows affected (0.18 sec) |
|
1 2 3 |
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 |
因みにサイズはこのような感じです。
|
1 2 3 4 5 6 7 8 |
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 | |
|
1 2 |
mysql> ALTER TABLE test2 ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8; Query OK, 0 rows affected, 4 warnings (44 min 15.12 sec) |
|
1 2 3 4 5 |
+------------+--------+------------+----------------+-------------+ | 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から確認したデータファイルのサイズもしっかり変更されております。
|
1 2 |
-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 を見ると確か。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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では一緒に技術のウエディングパークを創っていくエンジニアを募集しています。
興味のある方はぜひ一度気軽にオフィスに遊びにきてください。
