前回、推測した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作ってみようと思う。