MySQLチューニングの基本「スロークエリログ」を知ろう！ |

　MySQL を利用したアプリケーションを構築・運用している際に、パフォーマンスの悪い SQL を特定するための基本となる「スロークエリログ」について解説します。

スロークエリログとは？

　スロークエリログ (Slow query log) は、MySQL で出力できるログの種類の1つです。 SQL の実行時間が指定した時間よりもかかってしまった SQL を全て出力することができます。これにより、アプリケーション構築時や本番運用時にパフォーマンスのボトルネックとなっている SQL を発見するのに大いに役に立ちます。

　また設定のオプションとして log-queries-not-using-indexes というオプションがあり、このオプションを指定するとインデックスを使っていない SQL も全て出力するようにできます。これにより、インデックスの設計し忘れ・設定し忘れを発見することもできます。

　MySQL のデフォルト状態ではスロークエリログは出力されませんので、出力するためには次で述べるような設定が必要です。

設定

　スロークエリログを出力させるためには、設定ファイル my.cnf (Windows版を利用している場合には my.ini) に設定の追加が必要です。 [mysqld] のセクションに以下のように設定します。


[mysqld]

(中略)

slow_query_log=ON

long_query_time=0.5

slow_query_log_file=slow_query.log

log-queries-not-using-indexes

　※スロークエリログを有効にするオプションは、MySQL 5.0までは log_slow_query という名称でしたが、MySQL 5.1からは slow_query_log というオプション名に変更になっています。ご利用の MySQL Server のバージョンにご注意ください。

　上の例では、SQLの実行に0.5秒以上かかったクエリを出力するように設定しています。 long_query_time というオプションで時間(秒)を指定しています。 slow_query_log_file オプションでログファイル名を出力し、上記の説明の通り log-queries-not-using-indexes オプションでインデックスを利用しなかった SQL も出力するように設定しています。
これでパフォーマンスのボトルネックになっている SQL がログに出力されるようになります。

スロークエリログを出力する場合のデメリット

　スロークエリログを出力する場合のデメリットとしては、ログファイルを吐くということはファイルI/Oが余計に発生するということですから、その分 MySQL のパフォーマンスはが低下します。よって、スロークエリログをもとにしたシステム改善が終了したらログの出力もやめるなど、スロークエリログを吐かせる運用ルールなどをプロジェクトで決定するのが良いでしょう。

スロークエリログが出力された場合の対策について

　スロークエリログが出力されたということは、応答に時間がかかった SQL があったということですので、その原因を調査して理解する必要があるでしょう。 SQL の応答が遅くなる原因は様々あり一概には言えませんが、次のようなことから調査するのが良いでしょう。

インデックスが適切に利用されていない

　一番ありがちな原因は、実行された参照(SELECT)の SQL の実行にインデックスが利用されずにテーブルのフルスキャンが行われた場合です。この場合は、実行された SELECT 文の実行計画を見直し、インデックスの見直しを行うことが必要になります。 (インデックスについては「MySQLにおけるインデックス作成・削除・一覧表示方法」をご覧ください。)

ディスクI/O が多発してボトルネックになっている

　システムの利用者が増えてきた場合などは、データベースのデータが保存されているディスクへのアクセスが増えたことで、ディスクI/Oがボトルネックになっているかもしれません。あまりにも単純で改善の予知もない SQL の実行でも時間がかかっている場合はこの場合があります。ディスクI/O を減らすには、クエリキャッシュ機能などが有効です。クエリキャッシュ機能については、別稿「MySQL クエリキャッシュの概要と導入・評価方法」を参考にしてください。