Colorkrew Blog

こんにちは。小宮です。 ある日chefのレシピをなおしていると、同僚がこんなことをいってきました。

「おきゃくさまがphpmyadminのinformation_schemaのリンクをクリックしたとたんに
　サイトが重くなってディスク容量が数十GBも増えて今下がって落ち着いたって言ってます。
　なにか原因わかりますか？」

phpmyadminでinformation_schemaをクリックするのが危険過ぎる - K52.NIKKI ver3.0というのが添えられていました。

よくわからなかったので現象を呟いたところ、親切なMySQLのACE（えーす）のyoku0825さんがinnodb_stats_on_metadata=1があやしいんじゃないかと教えてくれました。 ググってみたところ、innodb_stats_on_metadata に要注意 - TAKUMI SAKAMOTO’S BLOGというページを見かけて何が起きたかわかった気になったのでした。

起きたことの予測： 　・テーブルの更新状況が統計情報更新の発生条件に達している状態だった 　・information_schemaのリンクをクリックすることでshow table status相当の統計情報更新のきっかけになるクエリが走った 　・統計情報更新のためにANALYZE_TABLE相当の処理が走った 　・ディスクがあふれたのはテーブルのコピーがtmpdirに作られたためで挿げ替えられて処理が完了したために使用率が下がった 　・cpu負荷はALTER相当のTABLEメンテナンス処理とそれに伴うディスクI/Oによるものと考えられる

innodbの統計情報更新の発生条件については、漢のブログに書いてあるのをみつけました。いつもありがとうございます。 めでたしめでたし。とこれだけだと物足りないのでちょっと足します。

どのバージョンでONかOFFかについて、公式のマニュアルをみると5.6.6からOFFでそれ以前はONのようです。

つぎに仮にinnodb-stats-on-metadataを0にしたばあいメンテナンスを自分で行う必要が生じるけどどうしたらええんやという話について。

基本メンテ入れて統計情報更新するだけならANALYZE TABLEするかデフラグもしたいならALTER TABLEしたほうがいいです。 InnoDBのOPTIMIZEは内部的にALTERが実行されるだけなのでREADのロックがかかるのが嫌ならALTER TABLE使うといいです。 データ(とスペック)を極力本番同様にした試験環境でかかる時間を見積もるのがオススメです。 表にマニュアルのリンクを貼っておきました。左は5.1日本語で右は5.6英語のリンクです。

・参考サイト innodb_stats_on_metadata に要注意 - TAKUMI SAKAMOTO’S BLOG 漢(オトコ)のコンピュータ道: 大人のためのInnoDBテーブルとの正しい付き合い方。 漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。 MySQLトラブル解析入門 テーブル メンテナンス ステートメント MySQL :: MySQL 5.5 Reference Manual :: 13.7.2 Table Maintenance Statements Percona Toolkit pt-online-schema-change でサービス無停止スキーマ変更 | 外道父の匠 pt-online-schema-changeを安全につかう - Around the World