Colorkrew Blog

シゴトをたのしくするカラクリを、もっと。Colorkrewオフィシャルブログ

Colorkrew Blog
  • Tips
  • Work style
  • CEO
  • Colorkrew
  • Member
  • English
  • TOP
  • Tips
  • innodb_stats_on_metadata=1でディスク容量激増とCPU負荷が発生
  • ALTER_TABLE
  • ANALIZE_TABLE
  • diskfull
  • information_schema
  • innodb_stats_on_metadata
  • MySQL
  • OPTIMEZE_TABLE
  • phpmyadmin

innodb_stats_on_metadata=1でディスク容量激増とCPU負荷が発生

2015年2月20日
komi

komi

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

1
2
3
4
「おきゃくさまが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英語のリンクです。

構文 説明
ANALYZE TABLE インデックス統計情報の更新に使います
CHECK TABLE 破損の可能性があるテーブルをチェックする際に使います
REPAIR TABLE 破損が確認できた場合に、復旧を試みます
ALTER TABLE テーブルが再作成されることによりインデックス情報もデータも再作成され、デフラグメントが解消されます
OPTIMYZE TABLE MyISAMの可変長フォーマット(テーブル定義に可変長のカラムを持つ)と、InnoDBのデータ部分にフラグメンテーションが起こったのを解消するために実行します。MyISAMの場合は、インデックス統計情報の更新、デフラグの解消、インデックスページの並べ替えが行われます。InnoDBの場合は、内部的にALTER TABLEが行われてテーブルが再作成されることにより、インデックス情報もデータも再作成され、デフラグメントが解消されます。

・参考サイト
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

5.6からONLINEで一部のALTER TABLEができるようになってますが以下みといたほうがよさそうです。
日々の覚書: MySQL 5.6のオンラインALTER TABLEを試してたらinnodb_online_alter_log_max_sizeに遭遇した
MySQLがオンラインALTER TABLEでOOM Killerに殺されたはなし | GMOメディア エンジニアブログ

MySQLは使ってる人も詳しい人もいっぱいいるので何かあっても情報が多くていいなあと思います。
では読んでいただいてありがとうございました。

  • Tweet
  • Add to Hatena
  • ALTER_TABLE ANALIZE_TABLE diskfull information_schema innodb_stats_on_metadata MySQL OPTIMEZE_TABLE phpmyadmin

おすすめ記事

  • Tips

    MySQLでALTER TABLEでINDEXを作成するときの注意事項

    • komi
    • 2013年12月10日
  • Tips

    MySQLのMyISAMが混在する環境のバックアップについて注意事項

    • komi
    • 2013年10月7日
  • Tips

    mysqlのreplication関連リンクまとめ

    • komi
    • 2014年4月8日
くらまね for Azure

株式会社Colorkrewについて

株式会社Colorkrew(カラクル)は、東京にあるIT企業です。 たのしいをうみだしとどける!をミッションに掲げています。
 
Colorkrewは会社を”チーム”ととらえ、
”Team Colorkrew”は、” Color Your Work with Excitement” をビジョンにシゴトをよりカラフルに、エキサイティングにしていくことを目指している集団です。
 
一緒に世界を変えるエンジニア、デザイナー、セールスを募集しています!
Team Colorkrewの価値観
  • オープン
  • チャレンジ
  • キズナ
 
株式会社Colorkrew
〒111-0041東京都台東区元浅草3-7-1 住友不動産上野御徒町ビル5階 03-6705-5970
Colorkrew Inc.
Sumitomo Fudosan Ueno-Okachimachi Building 5F, 3-7-1 Motoasakusa, Taito-ku, Tokyo 111-0041, Japan 03-6705-5970
  • Corporate website
  • 採用情報

事例紹介

Goalous

Goalous

目標への活動プロセスを見える化するSNS型目標管理ツール「Goalous(ゴーラス)」
Mamoru PUSH

Mamoru PUSH

ニッポン発!簡単でセキュアなプッシュ通知& QR認証サービス
Colorkrew Biz

Colorkrew Biz

名もなき仕事を減らすビジネスコンシェルジュツール
くらまね

くらまね

クラウドの販売、システム環境の構築・運用を行うクラウドマネジメントサービス
 
   

もっと見る

Team Colorkrew

  • 中村 圭志 Nakamura Keiji
    中村 圭志 Nakamura Keiji 172
    株式会社Colorkrew 代表取締役。 1993年、千葉大学工学部卒業、同年4月に豊田通商株式会社入社。 2004年3月、Toyota Tsus...
  • komi
    komi 41
    サーバインフラ系主婦です。 さいきんわりとqiitaに書いてます。
  • 小柴貴生
    小柴貴生 35
    ColorkrewのHR担当。 Colorkrewで働く人たちがすき。ColorkrewのビジョンをColorkrewらしく叶えたい。 同じ方向見て走って...
  • ひらの あい
    ひらの あい 18
  • miyashitak
    miyashitak 17

Like us on Facebook

Follow us on Twitter

@colorkrewさんのツイート
Colorkrew Blogとは
 

「シゴトをたのしくするカラクリを、もっと。」をコンセプトに、Colorkrewの経営、組織運営に対する想い、エキストリームなおもしろネタ、Webサービスの開発・デザイン・マーケティングのTipsから、社内で活躍するメンバーのインタビュー、社内でのイベントなど、さまざまなコンテンツを発信しています。

About Us

東京・台東区にあるIT企業、株式会社Colorkrew(カラクル)。 ”Color Your Work with Excitement”をビジョンに掲げています。 2015年10月1日(木)より、日本初のバリフラットモデルを導入し、管理職0(ゼロ)、階層0(ナシ)、 チーム力∞(無限大)の組織運営をおこなっています。

 株式会社Colorkrew HPを見る
 
お問い合わせ

法人のお問い合わせ メディア・広告掲載について プライバシーポリシー

Copyright © 2020 Colorkrew Inc. All rights reserved.

このウェブサイトでは、よりよいユーザー体験のためにCookieを使用しています。詳細は、Cookieに関する方針をご参照ください。OK をクリックまたは本サイトの利用を続行することで、我々がCookieを使用することに同意したとみなされます。