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

SHOW INDEX を使う時気は付けないと

MySQL
はじめまして、エンジニアの胡です。

先日ちょうどある事件が起こりました。と言っても、ほぼ MySQL のリファレンスマニュアルに書いてあることですが、軽く共有しておきたいと思います。

環境:
MySQL 5.1.50

とある機能で発行した SQL が一番効率的と思われる INDEX を使ってないことに気づきました。まぁ、別に珍しいことでもないから、FORCE KEY を入れようとしました。
しかし、使ってる ORM(DBIx::Class)が FORCE KEY 非対応のため、その部分では ORM を辞め、素直に SQL に書くことにしました。

このままいつもの平和の日々に戻ってもいいですが、どうしても ORM/SQL を混ぜて使うことに気が済まなくて、いつかきれいにしたくて、もちろんすぐには変えないですが、テーブルの INDEX を張りなおそうと考えました。
そうすると、FORCE KEY を使ってるため、該当 INDEX がなかったら怒られますね。問題なくスムーズに移行できるように、下記のようなロジックを加えました。
if ( the index exists ) {
  use sql with force key;
}
else {
  use orm method;
}
INDEX 存在してるかどうかの判断は SHOW INDEX を使って簡単に分かるので、さっさと改修しました。

コーディング、よし!
テスト、よし!
実装、よ、(゚ロ゚;)エェッ!?、DB が固まった!(((( ;゚д゚)))
ロールバック、よし!よし!

SHOW INDEX が大変重いの事実が分かりました。
シンジラレナイ!SHOW ですよ?
まぁまぁ、気持ちは分かるけど、事実なんで、信じましょう。

Google 先生に聞いたら、どうやら SHOW INDEX が実行される度に innodb が index 情報を更新するみたいです。テーブルのデータが多いほど遅いです。当たり前ですけど。

ちなみに、本番(slave)ではこんな感じだった。
mysql> show table status like 'hoge'\G
*************************** 1. row ***************************
           Name: hoge
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89591827
 Avg_row_length: 330
    Data_length: 29583671296
Max_data_length: 0
   Index_length: 50444500992
      Data_free: 491782144
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:
1 row in set (13.72 sec)
更新しないように設定を変えられます。
mysql> SET GLOBAL innodb_stats_on_metadata = 0;
この変更によって、業務に特に副作用がないため、これで問題解決しました。サーバ/MySQL 再起動することもあるので、my.cnf にも追加しました。

テスト、よし!
実装、よし!

あ~やっぱり平和一番!(* ̄∇ ̄*)エヘヘ

参考資料:
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/