読者です 読者をやめる 読者になる 読者になる

InnoDBのテーブル統計情報について

こんばんは。
MySQL Casual Advent Calendar 2011 5日目担当のid:marqsです。
東京は12月6日になってしまったかもしれませんが、京都は霊力が強いせいかまだ12月5日のようです。
MySQLにまつわるCasualなネタ、なかなか思いつかなかったのですがちょっと前に調べたInnoDBのテーブル統計情報について書いてみます。

InnoDBのテーブルのインデックス統計情報ですが、基本的に以下のタイミングで更新されます。

  • テーブルがオープンされたとき
  • テーブル統計の情報が更新された後、テーブルの全行数の1/16が更新されたとき
  • テーブル統計情報の更新後、20億行以上の行が更新されたとき
  • ANALYZE TABLEが実行されたとき
  • SHOW TABLE STATUS, SHOW INDEX FROM …が実行されたとき

この統計情報の更新処理ですが、@nippondanjiさんがブログでも解説されているように、ランダムにページを8回抽出して近似的に統計値を算出するため、そんなに重い処理ではないようです。
ただ、テーブルのオープン時やSHOW TABLE STATUS , SHOW INDEX FROM … の実行時に毎回ランダムにページを取得して統計情報が更新されるので、管理用コマンドの実行やMySQLの再起動などにより、意図せず実行計画が変わる可能性があります。また、インデックスが多数ある場合、全くIOが発生しない処理かというとそうでもないので、統計情報の更新があまり頻繁に発生するとパフォーマンスに影響を与えることがあります。
こういった場合、innodb_stats_on_metadataという変数をOFFにしてやれば、管理系コマンドの発行時などに自動的にインデックスの統計情報を更新することはなくなるようです。(innodb_stats_on_metadataはMySQL 5.1.17以降で実装済)
しかし、それでも統計情報はサーバのリスタート等で消えてしまうので、実行計画に影響を与えないというわけではありません。
http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-parameters.html

MySQL 5.6では、この統計情報の管理部分がPersistent Optimizer Statsというトピックとしてさらに改善されているようで、特にANALYZE TABLEで取得した統計情報がディスクに書き込まれるようになったようなので、サーバの再起動後も同じ統計情報を参照できるようになり、クエリの実行計画は安定しそうです。MySQL 5.6では他にもperformance_schemaにかなりパフォーマンスチューニングに使えそうなサマリ集計(table_io_waits_summary_by_table等々)が実装される予定なので、個人的にはリリースがとても楽しみです。
http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html

また、今回ANALYZE TABLEがいつ統計情報を更新しているか、innodb_stats_on_metadataがどういったフラグとして動いているのかを知るために、興味本位でMySQLソースコードを少し読んでみたのですが、日常的にC/C++のプログラムを書かない僕でも意外と追いやすいソースコードでした。普段は、MySQLのコードなんか読まないひとも、たまにCasualにMySQLソースコードを覗いてみるのもよいかもしれません。

では