実録MySQLのチューニング 春の陣

long_query_time = 0.5 とか閾値を小さめにしてもスロークエリが出なくなったけど、CPU(user)使用率高いとか、なんか足引っ張ってるクエリがあるっぽいなぁという場合のお話です。

「実録」の通り、現在絶賛進行中ですので、逐次動きがあったら書き足していくつもりです。

「あれを見た方がいい」とか「これをあーした方がいい」とかあれば、コメントかTwitterで @hirose31 までお知らせいただけるとうれしいです！

使用しているのは、MySQL 5.1.41 です。

前提: サーバーリソースのグラフ

GangliaでもCactiでもMuninでもなんでもいいんですが、サーバリソースのグラフ化は必須です。チューニングした際の効果測定や、そろそろリソース食い潰してやばいとかの予測にも使えます。

自分はDBサーバの場合このあたりをグラフ化してます。

• CPU使用率 (user, nice, system, wait, idel)
• 使用メモリ量 (used, shared, cached, buffered, free, swapped)
• ロードアベレージ
• ネットワークトラフィック (パケット数, バイト数)
• ディスク使用量 (バイト数, inode数)
• ディスクI/O (バイト数, IOPS)
  • /proc/diskstats のフィールド1, 3, 5, 7 あたり。詳しくは linux kernel付属の Documentation/iostats.txt を参照。
• プロセス数 (running, I/O blocked)

• クエリ [q/s] (select, insert, update, delete)
• DBコネクション (connected, running)
• InnoDBの空き容量
• スロークエリの数
• 各種ヒット率 (innodb_buffer_pool_hit, key_cache, thread_cache などなど)
• InnoDB buffer pool の使用状況 (バイト数)

インデックスを使ってないクエリをあぶりだす

フルテーブルスキャンやフルインデックススキャンといった非効率なクエリでも、データセットが小さくてメモリに全部乗っかっていると実行時間が小さいためスロークエリには出てきません。しかし、クエリの発行回数が多いと負荷に与える影響は無視できない場合があります。事例として、スロークエリログには何も出ていないがCPU (user)の使用率が著しく高い現象の原因がこれだったことがあります。

MySQLでは、log_queries_not_using_indexes をオンすると、long_query_timeを超過していなくてもフルテーブルスキャンやフルインデックススキャンのクエリがスローログに出るようになります。

log_queries_not_using_indexesをオンにするには、my.cnfに

log_queries_not_using_indexes = 1

と書くか、稼働中の MySQLで

set @@global.log_queries_not_using_indexes = 1;

とします。

現在の値を確認するのはこれで。

select @@global.log_queries_not_using_indexes;

結構、もりもりスローログに出てくると思うので、数分〜数十分間有効にしてサンプリングしたらオフにした方がいいかもです。

thread_cache_size

グラフを見て、thread_cache_size が足りてないようなら増やす。

readのI/Oが発生していないか (ディスク)

グラフを見て、read の I/O が発生していないか確認する。

メモリにすっかりのっていれば、ディスクの read I/O はまったく発生しない。

readのI/Oが発生していないか (InnoDB)

show innodb statusの「FILE I/O」のreads/sと「BUFFER POOL AND MEMORY」のBuffer pool hit rateを見る。

0.00 reads/s, 0 avg bytes/read, ...

Buffer pool hit rate 1000 / 1000

とかならいい感じ。

• 参考: show innodb status でチェックする値 - kazuhoのメモ置き場

統計的アプローチ

gprofなどのプロファイラでプログラムの最適化を計るときと同じように、実行されたクエリを解析して実行時間や実行回数順に並べて最適化のヒントにするアプローチです。

いくつかの方法があります。

• mysqldumpslow
  • ファイルに出力されたスロークエリログを元に解析する
  • MySQL 5.1のmysqldumpslowで快速チューニング - SH2の日記
• mprofile
  • show full processlistでサンプリングしたデータを元に解析する
  • Kazuho@Cybozu Labs: MySQL のボトルネックを統計的に監視・解析する方法
• Maatkitのmk-query-digest
  • ファイルに出力されたスロークエリログの他、binlogやgeneral log、なんとtcpdump(!!!)の結果なども入力データとして使える
  • インフラ系エンジニア勉強会 "hbstudy"でMaatKitについて発表してきました - marqs blog

上記3種類を一通り触ってみたのですが、ぼくの場合はこんな感じでmk-query-digestを使おうかなと思っています。

• ログサーバーにおいて、DBサーバからスロークエリログファイルをscpなりしてきてmk-query-digestにかる、という解析をdailyで行う。
  • mk-query-digest --limit 50 --order-by Query_time:sum --since YYYY-MM-DD 00:00:00 --until YYYY-MM-DD 23:59:59 slow.log > YYYY-MM-DD_by_qtime.txt
  • mk-query-digest --limit 50 --order-by Query_time:cnt --since YYYY-MM-DD 00:00:00 --until YYYY-MM-DD 23:59:59 slow.log > YYYY-MM-DD_by_count.txt
• 「log_queries_not_using_indexesをオンにしたので今だけちょっとリアルタイムなスロークエリログを見たい」という場合には、mysql.slow_logテーブルをselectしてもらう。
  • grant select on mysql.slow_logすると全DBのスロークエリログが見えちゃうので、複数プロジェクトでDBサーバ相乗りしてて閲覧権限が同じではない場合は問題になるので注意。
  • ファイル出力のスロークエリログのquery timeとlock timeの制度はusecなのに対し、テーブル(mysql.slow_log)の場合の時間の制度はなんと秒であるのに注意。細かい時間をみたいならファイルのスロークエリログを参照するしかない。

以下、つづく。。。かも。。。