/ MySQL

停止時間を抑えつつ InnoDB テーブル圧縮を有効化する

※ MySQL 5.7 からの透過的ページ圧縮 ではない です。

経緯

Zabbix のテーブルって膨れますよね。

# ls -l /var/lib/mysql/zabbix/*.ibd | sort -n -k 5 | tail -n 5
-rw-rw---- 1 mysql mysql    176160768 Dec 19 14:22 /var/lib/mysql/zabbix/events.ibd
-rw-rw---- 1 mysql mysql    494927872 Dec 19 14:02 /var/lib/mysql/zabbix/trends.ibd
-rw-rw---- 1 mysql mysql    591396864 Dec 19 14:32 /var/lib/mysql/zabbix/trends_uint.ibd
-rw-rw---- 1 mysql mysql 165217828864 Dec 19 14:32 /var/lib/mysql/zabbix/history_uint.ibd
-rw-rw---- 1 mysql mysql 183601463296 Dec 19 14:32 /var/lib/mysql/zabbix/history.ibd

ということで、テーブルをいくつか圧縮することにしました。

ALTER TABLE history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

調べると上記のような方法がよく見つかりますが、ALTER TABLE は実行中の書き込みがブロックされてしまうのと、一時的なテーブルにデータをコピーする動きをするのでディスク容量に余裕が必要です。

以前、似たような経緯で 100GB くらいの history テーブルを ALTER TABLE したところ、3 日くらいかかったという経験もあり、今回は別のアプローチを試みました。

別のサーバに一時的に DB を作り、テーブルを圧縮形式にしておき、そこにレプリケーションを張り、トランスポータブルテーブルスペースで ibd ファイルをコピーする、という流れです。
ibd ファイルのコピー・インポート中は停止時間になってしまいますが、3 日止まるよりはマシでしょう。

前提

便宜上、Zabbix のデータベースを master 、レプリケーションする先の別サーバを slave と呼ぶことにします。

単純化のため、テーブル history についてのみ手順を書きます。

master のデータベースは MySQL 5.6 で、my.cnf には下記の設定が入ってます。

[mysqld]
innodb_file_per_table
innodb_file_format = Barracuda

server-id = 1
log-bin = mysql-bin

手順

テーブル定義出力

対象テーブルの CREATE TABLE 文を出力します。
圧縮形式になるように sed しておきます。

[master]# mysqldump --default-character-set=binary -uroot \
--opt --skip-lock-tables --no-data -f zabbix history \
| sed -e 's/\(ENGINE=InnoDB.*\);$/\1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;/' \
> tbl_history.sql

ダンプの流し込み

まず、テーブル定義を slave に流し込み、圧縮形式のテーブルを作ります。

[slave]# cat tbl_history.sql | mysql -uroot zabbix

続いて、history テーブルのダンプを流し込みます。  
前述の通り、 master にはダンプを吐き出すほどの空き容量の余裕がないので、nc で slave に送信し、 slave で直接流し込みます。

[master]# mysqldump --default-character-set=binary -uroot \
--opt --skip-lock-tables --single-transaction --order-by-primary --master-data=2 --no-create-info -f zabbix history \
| tee >(grep 'CHANGE MASTER TO' >&2) \
| pv -B 4m | nc <slave> 60000

--skip-lock-tables--single-transaction でテーブルロックせずにダンプ処理をトランザクションで囲み、書き込みがブロックされないようにします。

tee で --master-data=2 での出力を確認しておくことがポイントです。
pv は、なんとなくの進捗がわかると精神的に良いのでつけてます。

nc の受け側は、こんな感じ。

[slave]# nc -l 60000 | mysql -uroot zabbix

レプリケーションを張る

ダンプの流し込みが終わりましたが、 slave には当然ダンプ開始時点までのデータしかないので、レプリケーションを張って master に追いつかせます。

特定のテーブルにしぼってレプリケーションしたいので、 slave の my.cnf は下記の感じにします。

[mysqld]
innodb_file_per_table
innodb_file_format = Barracuda

server-id = 2
replicate-do-table = zabbix.history

ダンプの際の --master-data=2 の出力を利用してレプリケーションを開始します。

-- @slave

CHANGE MASTER TO
MASTER_HOST = '<master>',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'replpass',
MASTER_LOG_FILE = 'mysql-bin.000002',
MASTER_LOG_POS = 14490978;

START SLAVE;

テーブルスペースのコピー

レプリケーションが追いついたら、トランスポータブルテーブルスペースで圧縮形式になった ibd ファイルを master に持っていきます。

サービスの停止とコピーの準備

Zabbix Server を停止して、

[master]# systemctl stop zabbix-server.service

レプリケーションを止めて、

-- @slave
STOP SLAVE;
RESET SLAVE ALL;

master のテーブルを圧縮形式に作り直します。

-- @master
DROP TABLE history;

↓ 本来は slave での SHOW CREATE TABLE の出力を使うべきです。ここでは手抜きしてます。

[master]# cat tbl_history.sql | mysql -uroot zabbix

いざトランスポート

master のテーブルの既存のテーブルスペースを破棄し、

-- @master
ALTER TABLE history DISCARD TABLESPACE;

slave でエクスポートのためにロックします。
(この時点で後述の cfg が作成されます。)

-- @slave
FLUSH TABLES history FOR EXPORT;

そして、 slave の、
/var/lib/mysql/zabbix/{history.ibd,history.cfg}
master の同じパスに持っていきます。

その後、 master でテーブルスペースを読み込みます。
(想定外だったけどこれも結構かかった…。3 時間くらい。)

-- @master
ALTER TABLE history IMPORT TABLESPACE;

サービスの再開

読み込めたら、Zabbix Server を再開しましょう。

[master]# systemctl start zabbix-server.service

お掃除

master に持っていった cfg ファイルは、インポート後は削除してしまって OK です。

また、 slave で取得したロックは下記で開放できます。

-- @slave
UNLOCK TABLES;

おわりに

この作業で、 history.ibd のサイズを 171GB から 82GB に縮めることができました。
※ Zabbix の housekeeping でフラグメントしてたりするので、圧縮の効果のみではないのですが。