MySQL DBの肥大化対応

目次

概要 †

以下はZABBIXのDBが肥大化した際の対応内容。
蓄積するデータが多すぎることにより、history.logのサイズが230GBを超えてストレージ（250GB）を圧迫した。

根本対策 †

肥大化する原因はデータ量が多すぎることに起因するため、監視間隔を疎に変更し、データ保持期間（特にヒストリの保持期間）を短縮することによりデータ蓄積量を減らすことで対応する。
監視間隔は毎分監視していた項目を3〜5分毎、グラフ化するためのデータ抽出は5分以上の間隔を空ける様に設定を変更。
ヒストリの保持期間は、死活監視などはヒストリを7日、トレンドは0日に設定。グラフ化などの可視化が必要な項目はヒストリを40日、トレンドを3650日に設定。
以上により、容量の増加が目に見えて分かる状況の回避には成功。
しかし、肥大化したhistory.logのサイズは変わらないため、これの縮小を試みることとなった。

InnoDB向け対策（ファイル分割） †

構築当初より、my.cnfにはinnodb_file_per_tableの設定を行っていたため、テーブル単位でのファイルの分割は行われていた。（故にhistory.logの肥大化を特定できていた）
このため、自動拡張でサイズが増えてしまったhistory.logをどの様に縮めるかが次の課題となった。

dumpの作成とリストア †

一度dumpを取得して、リストアすることでサイズを縮められるとの話を聞いていたことから、これを実行。

mysqldump -u root -p [DATABASE NAME] > [DumpFileName]

出力結果が140GB程度になったため、既存DBを破棄して書き戻しを試みた。

> dropdatabase [DATABASE NAME];
> create databse [DATABASE NAME];
> grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';
> flush privileges;
> quit

次に一応DBの初期内容を展開しておく。

cd /usr/local/src/zabbix
cd create/schema
mysql -u root -p zabbix < mysql.sql
cd ../data
mysql -u root -p zabbix < data.sql
mysql -u root -p zabbix < images_mysql.sql

ダンプをリストアする。

mysql -u root -p [DATABSE NAME] < [DumpFileName]

書き戻したら、230GB越えのサイズに戻ってしまった…
この方法では解消できないことが分かったため、次の手を考える。

ALTER TABLEによる対処 †

「ALTER TABLE テーブル名 TYPE=InnoDB;」の書式にて肥大化したDBの縮小が可能らしい。
処理の際に書き出すテンポラリファイルはテーブルサイズに比例するため、250GBの容量しかないストレージ上で200GB超えのテーブルに対して実行することが不可能であるという事実が判明。本処理を行う仮想マシンが格納されているVMFSのブロックサイズを1MBに設定していたため、最大で256GBの仮想ディスクファイルまでしか作ることができず、これ以上大きなサイズのディスクを用意できないという根本的な問題も顕在化。やむなく、DBを丸ごと容量のあるNFS上へ移動させ、NFS上でALTER TABLEを実行した。

mysql> alter table history_log;

実行後のサイズは228,885MB。
あまり変わらなかった。

OPTIMIZE TABLEによる対処 †

次にOPTIMIZE TABLEを試してみる。
InnoDBにおいてはOPTIMIZE TABLEもALTER TABLEも処理内容は同じらしい。

mysql> optimize table  history_log ;

実行後のサイズは227,996MB。
案の定、あまり変わらなかった。
NFS上で実行したところ一昼夜かかっており、実行時にはストレージを選んだ方が良いことが明白に分かる結果となった。

+----------------------+----------+----------+-------------------------------------------------------------------+
| Table                | Op       | Msg_type |  Msg_text                                                          |
+----------------------+----------+----------+-------------------------------------------------------------------+
| zabbix18.history_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix18.history_log | optimize | status   | OK                                                                |
+----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 day 22 min 1.41 sec)

# du -sm  /database/mysql-data/zabbix18/
228183  /database/mysql-data/zabbix18/

# du -sm  /database/mysql-data/zabbix18/
186236  /database/mysql-data/zabbix18/