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

前回、推測したibdata1が膨らむ理由にて考えたので
もうちょっと調べてみることにした。

http://nippondanji.blogspot.com/2010/03/innodb.html
漢(オトコ)のコンピュータ道: たった3秒でInnoDBのデータローディングが快適になるライフハック

http://mysql41.man.hive0.net/r/table-types.html#implementation
7.5.11. マルチバージョニングの実装

http://www.interdb.jp/techinfo/mysql/m-2-08.html
MySQLとは: ■2-08■ InnoDB型

これらの情報をみると、ロールバックセグメントにはUPDATEのUNDO領域(読み取り一貫性にも使用)とINSERTのUNDO領域があるらしい。

特に、僕の遭遇した「スクリーンなどのグラフを読み込んでいるときにibdata1の肥大化が起こる」というのはUPDATEのUNDOが肥大化してしまうということではないだろうか。前回は推測だけだったので、本当に起こっているのか見るコマンドがあるか調べてみた。

http://dev.mysql.com/doc/refman/5.1/ja/innodb-monitor.html

SHOW ENGINE INNODB STATUS\G

これによって現れるデータの中でも、下記の部分に

1
2
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0

この値が急激に増えていると、現象発生している可能性が高いと思われる。

http://yumewaza.yumemi.co.jp/2010/11/mysql_show_engine_innodb_statu_1.html
※詳細は上記ページが参考になるかと。

そして、この部分が初期値の512(8MB)から増えていないことや、Free buffersが0になっているならば、メモリチューニングが不十分であることを示していると考えられ、領域が増えるという原因になっている。

1
2
3
4
5
6
7
8
9
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17475088; in additional pool allocated 864256
Buffer pool size   512
Free buffers       493
Database pages     19
Modified db pages  0
Pending reads 0

肝心のUNDO領域はいまどれくらい?という情報についてだが、使用しているMysqlが5.0.77(CentOS5系)のため、
information-schemaで見ることができない。

http://search.net-newbie.com/mysql51/information-schema.html#files-table
Mysql5.1以上であれば、見えるし、undo用のファイルを割り当てることができるようなのだが…

安定性については難しいけれど、Mysqlのバージョンアップ(5.1や5.5)をすることによって、innodb の使い勝手も向上していると思われ、zabbixを使用するなら、高いバージョンでの構築も検討課題に入るのではないかと思う。

散々、Mysqlとinnodbについて書いてみたが、肝心のzabbixにおいてibdata1の肥大化はどうなったのかというと、

1
2
innodb_buffer_pool_size=96M
innodb_log_file_size=16M

これで、有効アイテム数3000、1秒当たりの取得アイテム120 のサーバは一週間ぐらい肥大化なし。2×8 のスクリーン表示(カスタムグラフ:アイテム数 4)しても問題なし。

その後、仮想サーバのメモリを384MB -> 1024MBにして、HTTPDやらMysqlやら余裕が出たので、安定稼働になった。
現在の使用容量とか計算するSQLを作ろうかなと思ったら、すでにデータベース単位だけれどやっている人がいた。
http://www.hirohama.biz/mysql/2007/12/05-171241.html

そのうち、アイテム数から1年分を割り出すようなSQL作ってみようと思う。