zabbixでMysql(innodb)のibdata1が膨らむ理由

インストール記事では、Postgresql にインストールしているのに、いきなりMysqlになっているのは、顧客のシステムでの話を後追い調査してみたから。

■環境
２０台程度のFreeBSDサーバと１５台程度のLinux(Redhat系)サーバを監視しているzabbixサーバがある。zabbixサーバのメモリは384MBで15GBのHDDを持つ（仮想サーバで運用しているため。）

■現象
zabbixサーバのディスク使用量が運用開始一か月後に90％を超えてしまった。
どの領域が肥大化しているかを調査すると、/var/lib/mysql/ibdata1 というファイルだった。
このibdata1はMysqlのinnodbが共有ディスク領域として使用しているファイル。

■調査内容
ibdata1が肥大するタイミングは不定期。
1時間ぐらい同じサイズだったのにいきなり7MBほど増えたりする。
スクリーン（カスタムのグラフをいくつも貼り付けている）を頻繁に読むとかなり発生していると思われる。

これらのことから、agentからのデータ入力とは関係ないことがわかる。

■Mysqlのチューニングについて調査や検証
innodb のデフォルトを使用していた。

※同じサーバが検証環境にあり、そちらのメモリを増やしてみた。

1
2

innodb_buffer_pool_size=256M
innodb_log_file_size=16M

しょぼいサーバなので、設定もデフォルトからちょっとしか変わっていないけど、ibdata1の肥大はかなり収まっている。(7GB程度)

参考URL
http://d.hatena.ne.jp/futsu-9/20070106/p1
（フツーな日常：InnoDBを使うときのパフォーマンスチューニング ）
http://q.hatena.ne.jp/1236080287
（人力検索はてな > コンピュータ > 以下の文章でMYSQL(innodb)の設定値の理解はあっていますでしょうか？）
http://blog.flatlabs.net/20100727_212649/
（flatlabs：　MySQLの「innodb_buffer_pool_size」と「innodb_log_file_size」の設定）

■気になったinnodbの挙動

http://subtech.g.hatena.ne.jp/mala/20100625/1277474101（Hatena::Group::subtech：InnoDBのデータファイル肥大化の傾向と対策）

ここに、DELETE、INSERTを繰り返すと、パージスレッドの処理が追いつかなくて、データファイルが肥大化すると書いてあった。ヒストリの削除と挿入はこの現象とあっていないだろうかと推測した。

※解決方法の一つとして
innodb_max_purge_lagを使えば、古いデータが一定量を超えた場合に、UPDATEやINSERTを遅延させて、パージが追いつくようにできるらしいが、zabbixでそれをやれるのか？ということを検証せねば怖くて使用できない。

■ibdata1の縮小について

・コマンドなどでは用意されていない
・ダンプをとって、ファイルを消して、リストアするらしい。
・innodb_file_per_table にて、テーブルごとに分割するのはできるけど、パフォーマンスが下がったりするし、ibdata1の大きさは一度膨らんでしまうと変わらないし。
・innodb_file_per_tableを設定してALTER TABLEすると、テーブルをコピーしなおしてくれるらしいのだが、my.cnf のinnodb_file_per_table記述を消してからMYSQLを再起動すると、データファイルを削除してくれるらしい。危険。
詳しくは下記のURLで。
http://hibinokoto.jp/archives/2008/03/post-257.html
（日々のこと：/var/lib/mysql/ibdata1 の圧縮）

■innodbへの不満

パージスレッドのパフォーマンスを上げるような設定はないものか？
ibdata1の大きさをデフラグするようなコマンドはないのか？

たぶん、メモリの部分を改善して、パージスレッドの遅延を発生させないようにするのが一番の解決策なんだろうなと思った。
また、ibdata1のサイズ上限を決めてしまうこともできるようだが、足らなくなったら使えなくなるってのは意味がない。

■まとめ
ibdata1の大きさが気にならないぐらい大きなHDDに移動するというのが一つの手で、もう一つはパージスレッドの処理を早くするために、メモリをしっかりと確保して、他のSQLにて全体のパフォーマンスが低下する状態を避けるってことで解決するしかないなと考えた。

続き書きました。(2011/02/27)
https://seedslight.com/wp/archives/93