Wedding Park／ウエディングパークの技術ブログ

全206件

2017/02/08

こんにちは。インフラエンジニアの綿引です。早速ですが、今回は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」はオニカマスらしいです。オニカマスの圧が強いですね。上記を変更後、確認します。 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 にしないと使えません。 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以上となってしまう場合は、     元ページを分割した後に、それぞれ圧縮を行うためその分容量が増えてしまうので注意です。まずは既存テーブルのバックアップを取得します。 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では一緒に技術のウエディングパークを創っていくエンジニアを募集しています。興味のある方はぜひ一度気軽にオフィスに遊びにきてください。

Wedding Park／ウエディングパーク の技術ブログ

コンテンツ

Wedding Park／ウエディングパークの技術ブログ