金利0無利息キャッシング – キャッシングできます

 | 

2010-06-25

InnoDBのデータファイル肥大化の傾向と対策

22:55 | InnoDBのデータファイル肥大化の傾向と対策 - 金利0無利息キャッシング – キャッシングできます を含むブックマーク はてなブックマーク - InnoDBのデータファイル肥大化の傾向と対策 - 金利0無利息キャッシング – キャッシングできます

余裕でメモリに収まるサイズのデータファイルが膨れ上がって全てのクエリが遅くなる現象を過去に二例ぐらい経験したのでメモ。file per tableを有効にしている状態でも起こる。1台はもうサーバーごとリプレースしてしまったけど。

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

ユーザがテーブル内で大体同じくらいの比率で小さめのバッチの行を挿入、削除するというようなシナリオでは、パージ スレッドが遅れをとり始め、そしてテーブルが大きくなり続け、全てがディスクに頼った状態になり操作がとても遅くなる可能性があります。テーブルがたったの10MB の有効データしか持っていないとしても、たくさんの 「停止した」 行が10GB を占めるほどにまで大きくなる事があります。

確認するにはinnotopでトランザクションのステータスを開く。

CXN            History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts
xxx.xxxxxxx        509      1959  0 0      19.46%     93.92%   201       00:02        0

これで表示されるHistoryとVersionsの数値が異常に多くなっていて減らない状態だとデータファイルのサイズが肥大化していってしまう。100万行のテーブルで1億とかになったりする。

ドキュメントに書いてある通り、innodb_max_purge_lagというパラメータを調整することで解消することが出来る。

innodb_max_purge_lagはドキュメント読むと説明がクソわかりづらいのだけど、

  • 削除マークが付いている古いレコードを何行まで許容するか
  • この数値をオーバーしたらupdateやdeleteに遅延を入れてpurge処理が追いつくように調整する

みたいな感じだと思う。問題の起きてるDBでは100000にした。通常はいじる必要がない。あまり小さい数字にするとupdateやdeleteが大幅に遅くなる。

innodb_max_purge_lag調整後はデータファイルサイズが肥大化する現象が起こらなくなった。一度肥大化したらoptimize tableかけないと戻らないと思う。

トラックバック - http://subtech.g.hatena.ne.jp/mala/20100625
 |