職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している（いた）組織の意見ではない。

壊れた統計情報

MySQL

またもやMySQLで完全にハマったのでメモ。

症状

  • 投げたクエリが、帰ってくる気配が無い。
  • process list を見ると、当該のクエリは "Copying to tmp table" と言っている。
  • 当該クエリから ORDER BY 句を除いて実行すると、応答が無いのは同じだが、process listの表記が "Sending data" と言うようになる。

調査その１：tmp_table_sizeとmax_heap_table_size

"Copying to tmp table" でググったときにまず出てくるのが、これらのパラメータを変更しましょう！という情報。
mysql - "Copying to tmp table" extremely slow - Database Administrators

tmp data を格納する領域が十分でなければ、そりゃあcopyも莫大な時間がかかりますよね、という理屈。

もちろん試してみましたが、僕が遭遇した環境では効果無し。

調査その２：MySQL再起動

困ったときの奥の手。再起動。
試しても何も変わらず。

調査その３：サーバログ

mysqldが吐いてるログを見ても、手がかりは見つからず。

調査その４：当該クエリをexplain

この調査、再起動よりも先にやれよｗｗという野次が飛んできそうですが、僕の精神的余裕の無さから順序が入れ替わってしまいました。
しかし、explainの結果を見ても、特に異常な点（rowsが異常に多いとか）が見当たらず。

調査その５：当該indexを調査

explainで示されたindexの状況を見てみると、なんとcardinalityが0になっているではありませんか！！
もう少し詳しく言うと、Primary KeyのIndexのみは適切な値が出力されていて、それ以外のIndexのcardinalityが0の状態でした。
analyze table をかけたら、cardinalityには適切な値が入り、クエリも妥当な時間で結果を返すようになりました。
※注（追記＠2012.12.06）：phpMyAdminで見た時のcardinalityが0だということです。phpMyAdminのバージョンによっては見え方が違うかもしれません。実際のcardinalityは null です。

クエリの実行中に、何が起きているのか？（推測）

何らかの理由で統計情報が壊れた結果、クエリの実行中に optimizer さんが無限ループにでも陥ってしまったのでしょう（でも、この推測はクエリからORDER BY句を除いたときの症状と矛盾するような気もします。追加の調査や勉強が必要。。）。

なぜ統計情報が壊れたのか？

このQ&A MySQL MyISAM table index cardinality is zero - Stack Overflow の回答者さんがMySQLマニュアルから引用されている通り、データをロードした後は analyze table を実行すべきです。これはMySQLに限らず、他のRDBMSでも同じ事が言えます。
今回、僕が遭遇したサーバでは、実際にデータのロードを実施していました。
そう言えばデータのロードをする前は、当該のクエリも元気に動いていたなーというのを思い出しました。
統計情報が壊れる原因としては、データのロードの可能性が高いな、と思います（でも断定はできず）。

cardinalityがnullになっているindexを抽出

このSQLを時々流せば、幸せになれるかも？！（※実行は自己責任で。）

select table_schema, table_name, index_name
from information_schema.statistics
where table_schema = 'DB名'
  and cardinality is null
group by table_schema, table_name, index_name
order by table_schema, table_name, index_name

データが0件じゃないテーブルがこのリストに出てきたら危険信号、と考えても良いのではないでしょうか。

検証環境