停止時間を抑えつつ 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 でフラグメントしてたりするので、圧縮の効果のみではないのですが。