2014.02.03MySQLTunerを使ってMySQLを診断しよう!TweetPocketこんにちは、YAMAです。DBのチューニングはインフラ環境、アプリのつくり等、その時の状況で調整する必要があります。今回はMySQLTunerというMySQLを診断してチューニングをアドバイスしてくれるツールを使って、実際にMySQLのチューニングを行いました。MySQLTunerは警告項目に関してのみの情報は多いのですが、警告が出ていない項目に関する情報が少ないので、今回の診断結果をもとに詳しく解説したいと思います。【MySQLTunerのインストール】まずはMySQLTunerをインストールしましょう。MySQLTunerはPerlで作成されており、無料で利用できます。インストールといっても、「サイトからzipファイルを取得して解凍するだけ!」といった至ってシンプルな作業です。以下、インストールのサンプルコマンドです。mysqltuner.pl【MySQLTunerの診断結果】MySQLTunerを実行して、診断を行います。実行方法は「mysqltuner.pl」をそのままキックするだけです。今回診断した環境は以下のとおりです。なっている項目がチューニングの必要な項目として表示されます。それでは各項目1つ1つについてどのような内容か、説明していきます。script>MySQLTunerのバージョンチェックをスキップ。」とバージョンが表示されています。–NDBCluster>MySQLのストレージ構成がどのようなステータスかを表示しています。5)>MyISAMの情報を表示しています。今回検証したMySQLではストレージ容量を「324KByte」利用し、5テーブルあることがわかります。68)>InnoDBの情報を表示しています。今回検証したMySQLではストレージ容量を「3GByte」利用し、64テーブルあることがわかります。17)>PERFORMANCE_SCHEMAの情報を表示しています。今回検証したMySQLではストレージ容量を「0Byte」利用し、17テーブルあることがわかります。(つまり全然使用していないということですね。16>断片化されたテーブル数が警告表示されます。今回検証した環境では16テーブルが断片化されています。TABLE構文を使って、データファイルを最適化しましょう。set.>ユーザーにパスワードが設定されていないことをセキュリティ警告として表示しています。1B)>MySQLが起動してからの時間を表示しています。1%>MySQLTunerで測定した際の読込み/書込みの比率を表示します。合計で100%となります。threads)>全体の使用メモリと1スレッドあたりのメモリ消費量を表示しています。最大スレッド数も表示されます。最大スレッド数はmy.cnfで設定した「max_connections」となります。RAM)>稼働しているMySQLの最大使用メモリを表示しています。括弧に表示されたパーセントはサーバーのメモリとの対比です。今回の警告は現在のMySQL設定で使用する最大メモリは10.3G必要であり、サーバーのメモリが2Gしかないことで表示されています。サーバー自体のメモリを増設する必要があります。最大接続数を減らす、個々のメモリの割り当て量を減らすなどの対応も考えられます。(45/474K)>スロークエリが発生しているかを表示しています。(7/400)>稼働しているMySQLのこれまでの最大接続数を表示しています。16.0M/256.0K>MyISAMのIndexに関するメモリ使用量を表示しています。この項目で警告が出る場合はmy.cnfで「key_buffer_size」を増やすチューニングを行いましょう!また、InnoDBのみ利用する場合はこの設定は不要となります。reads)>インデックスのヒット率を表示しています。ヒット率が90%を下回らないようにチューニングする必要があります。上記項目と同様でMyISAMを利用する場合に警告が表示される場合は、「key_buffer_size」のチューニングを行いましょう。selects)>クエリキャッシュの効率性を表示しています。cached」で警告が表示されています。OFF」にしているかは別の機会で説明しようと思います。0>1日あたりのクエリキャッシュが削除された回数を表示します。クエリキャッシュサイズが小さい場合、より多くのキャッシュを必要とした際に、キャッシュが削除されます。sorts)>ソートに必要なTempテーブルの利用状況を表示します。total)>Tempテーブルの利用状況を表示します。TABLE構文を使って、データベースの最適化を行いましょう。connections)>スレッドキャッシュのヒット率を表示します。ヒット率が90%を下回らないようにチューニングする必要があります。_size」のチューニングを行いましょう。opened)>テーブルキャッシュのヒット率を表示します。ヒット率が90%を下回らないようにチューニングする必要があります。警告が表示される場合は、「table_open_cache」のチューニングを行いましょう。(11/2K)>MySQLで使用可能なファイル数の使用率を表示します。locks)>テーブルロックがすぐに取得できたかどうかを表示しています。この項目に警告が表示される場合は、クエリを最適化し、次にテーブルを分割するかレプリケーションの活用を考える必要があります。MyISAMであれば、InnoDBテーブルへの移行を検討する必要があります。3.5G/2.0G>InnoDBのデータサイズと「innodb_buffer_pool_size」の値を表示しています。今回の検証したMySQLではInnoDBのデータサイズが3.5GByteあり、バッファサイズが2GByteのため、警告表示されています。検証環境のメモリが2GByteのため、サーバーのメモリを増設した上で、「innodb_buffer_pool_size」を3.5GByte以上に設定する必要があります。[勧告]ここに表示されている内容を改善しましょう!といったMySQLTunerからの勧告となります。原則ここに表示された内容を改善できればチューニングOK!ということになります。TABLEを実行して、テーブルをデフラグしましょう!」と勧告されています。TABLEを実行しましょう。stability>「システムの安定化のためには、MySQLが必要とする全メモリを少なくした方がいい」と表示されています。今回検証したサーバーではメモリ不足のはずなのですが。これはMySQLでメモリを大量に使用しているため、OSやその他アプリケーションに割り当てるメモリが少なくてシステムが安定しないことを示しています。データ量が多いのであれば、サーバーのスペック自体の見直しをしましょう!ということですね。adjust:』[MySQL設定関連の改善勧告]ここに表示される内容がMySQLの設定アドバイスになります。***「MySQLで使用しているメモリ量が高く、危険です。MySQLをチューニングする前にサーバーのメモリを増設してください。今回の検証結果では当然の判断ですね。MySQLの設定について、実際の推奨値を表示してくれます。8M)」といった結果になることもありますので、勧告が表示されなくなるまで設定の見直しが必要です。「query_cache_limit」のみを変更するだけでなく、関連した設定も変更が必要となります。(「query_cache_limit」の場合、「query_cache_size」とか。各項目の詳細設定も別の機会にでも書こうと思います。)「table_cache」はMySQL5.1からは「table_open_cache」と設定値が変更されています。「innodb_buffer_pool_size」はサーバーのメモリを増設しなければ、設定しても意味をなさないので、今回は設定見直し対象外としました。今回の診断結果から設定を見直した項目は以下のとおりです。TABLEで全テーブルへのデフラグを実施しました。その結果は以下のとおりです。(Recommendationsのみ表示。3G)メモリ以外の勧告がすべてクリアされました。【まとめ】MySQLTunerはMySQLのチューニング診断に、とても有用なツールです。無料!ただ、MySQLTunerからアドバイスされた項目のみを変更して、MySQLが動かなくなったということにならないようにチューニングには深い知識が必要です。このツールに頼るのではなく、このツールをきっかけにより深い知識を身につけていく入り口として活用してはいかがでしょうか。広告システムについてのお問い合わせやご相談、パッケージ製品の詳細はこちらからどうぞ。Appのお問い合わせ・詳細はこちら。*メールアドレス(公開はされません。*技術者目線でADテク知識からネット広告業界最新ニュースまであらゆる情報を発信するブログ。