cles::blogのチューニング

さすがに昨日の一件には参ってしまったのですが、最近MySQLの負荷が高すぎることは確かなので少しチューニングをしてみることにしました。

その昔、Oracleバリバリだったときにはチューニングばかり勉強したりしていた時期もあったので、チューニングで何をしなければならないかというのは大体わかっているつもりです。今回はそのときの経験を生かして、MySQLのチューニングに挑戦してみます。

† まずはボトルネック解析から

まずはなぜ遅いのかという原因を絞り込みます。これはDBのチューニングに限らず全てのチューニングという作業に共通したものですよね。これをやらずにチューニングやリソースの増強を行なうのは愚の骨頂です。

さて、遅いという現象は基本的に何らかのリソース不足なわけなので、どんなリソースがＤＢにとって不足しているのかということを調べることになります。

原因は大きく分けて以下の５つでしょうか。
　・CPU
　・メモリ
　・ディスク
　・ネットワーク
　（・リソースの競合[デッドロック]）

これらの見分け方なんですが、OSがUNIX系の場合にはsarとかvmstatとかiostatとかfreeのようなコマンドを使うと比較的簡単にわかります^*1。

今回の症状は明らかにCPUネックでした。

† パラメータをいじってみる

リソースを増強するのは最終手段なので、まずはタダでできるパラメータをいじるということから始めます。MySQLの場合、パラメータはmy.cnf^*2に書くことができます。この件でいろいろ調べてわかったのですが、バージョンがあがるたびにパラメータも少しずつ増えているようです。

Oracleなんかだと公式、非公式^*3を含めてかなりチューニング方法がありますが、MySQLのチューニングっていうのはあまり聞きません。もともとデフォルトでそこそこのパフォーマンスを出してくれるということと、いじれるパラメータが少ない^*4ことが関係しているのだと思います。

とりあえずMySQLのチューニングについては公式マニュアルの「5 MySQL の最適化」に基本的なことは載っているので、このあたりのドキュメントを読めば基本的なことはつかめると思います。

† 遅いクエリを特定して、改善する

ボトルネック解析でCPUネックになっていることがわかったので、次にやることは時間のかかるQueryを特定することです。具体的にはmy.cnfに以下の行を追加することにより、実行に時間がかかっているQueryを特定してみました。

実行に５秒以上かかるQueryを(DATA_DIR)/slow.logに記録する

long_query_time=5
log-slow-queries=slow.log

しばらくサイトを立ち上げておくと、いくつかの怪しいクエリがlogに吐き出されていましたので、このクエリと、クエリが使っているテーブルに問題がないかを確認します。

確認したのはこの３点
　・テーブルに適切なINDEXが設定されているか
　・Where節にINDEXを使わせないような条件を指定していないか^*5
　・実行時にINDEXが適切に使用されているか

この確認はSQLにEXPLAINを付加してみることで^*6簡単に確認できます。この部分の修正で、速度が３割ほどアップしました。

† さらにクエリキャッシュを有効にしてみる

これでもまだ遅いので、MySQLのパラメータの設定情況を確認してみます。パラメータの確認はmysqladminを使って確認します。

mysqladmin variables

これで確認してみたところquery_cache_sizeが0になっていることが判明しました。

6.9. MySQL クエリキャッシュ

（一部の）テーブルがそれほど頻繁には変更されず、同じクエリが何度も実行される環境では、クエリキャッシュが非常に役立ちます。 動的コンテンツを大量に持つ多くの Web サーバでは、このような状況が一般的です。

実行コストが高いクエリであっても、結果をキャッシュすることで負荷は劇的に改善するはずということで、下記の内容をmy.cnfに追加してみました。

query_cache_limit=1M
query_cache_min_res_unit=4k
query_cache_size=32M
query_cache_type=1

これの効果は絶大で、これまで１０秒以上かかっていたクエリがコンマ数秒で実行できるようになりました。CPU負荷も許容範囲内に収まっています。

まぁ、キャッシュされているんですからCPU使ってなくて当たり前ですね。

ケチケチしないでもっとメモリを割り当ててあげればいいのかもしれないのですが、query_cache_sizeの値は様子を見ながら必要に応じて大きくしていきたいと思います。

とりあえず、今回はこれで一件落着。

• ^*1: これらのコマンドは呪文のような数値をたくさん出力するのですが、各数値の意味がわかれば答えもおのずとわかります。
• ^*2: Windowsの場合はmy.ini
• ^*3: 通常の手段では入手できない隠しパラメータとかあったりします。
• ^*4: いい意味で使っています。Oracleのパラメータ設定についてはノーコメントで。
• ^*5: 具体的には式の評価順序や、Where節で計算式を使っていなかいどうかなどです。
• ^*6: 別件ですが、Explainの例はこちらにあります。