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

  • このエントリーをはてなブックマークに追加
  • Pocket

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

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

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

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

圧縮の仕組み

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

スクリーンショット-2017-01-30-11.41.43

非圧縮ページ(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」はレイヨウ

Antelope1

「Barracuda」はオニカマス

Barracuda1

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

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

コマンド

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

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

例としては以下です。

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以上となってしまう場合は、
    元ページを分割した後に、それぞれ圧縮を行うためその分容量が増えてしまうので注意です。

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

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

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

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

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

デメリット

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

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

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

まとめ

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

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

  • このエントリーをはてなブックマークに追加
  • Pocket

SNSでもご購読できます。

コメント

コメントを残す

*