« Intruducing Incline - a synchronization tool for RDB shards | Main | Using a statistical approach to analyze / monitor MySQL bottleneck queries »

July 22, 2009

MySQL のボトルネックを統計的に監視・解析する方法

 MySQL のチューニング、と言った場合には、サーバーパラメータの調整や EXPLAIN コマンドを利用したクエリ実行計画の最適化が話題に上ることが多いです。しかし、発行する全ての SQL について、いちいち EXPLAIN コマンドを使って確認していては、いくら時間があってもたりません。チューニングを効率的に進めるには、まず、ボトルネックとなっている SQL クエリを特定し、次にその最適化を行うべきです。

 ではどのようにして、ボトルネックを特定するのか。MySQL Conference & Expo 2009 のキーノートにおいて Mark Callaghan 氏は、Google では SHOW PROCESSLIST コマンドを使った統計的アプローチを使っていると述べていらっしゃいます (参照: MySQLConf 09: Mark Callaghan, "This is Not a Web App: The Evolution of a MySQL Deployment at Google")。また、SHOW PROCESSLIST を使った統計的アプローチは商用製品である MySQL Query Analizer のウェブページでも競合手法として比較対象になっています。今回、ちょっと必要になったので、同様のことを行うスクリプト「mprofile」を書いてみました。こんな感じで使います。

SQL クエリをダンプ

 mpdump コマンドを利用して、定期的に実行中の SQL クエリ一覧を MySQL から取得します。デフォルトでは 0.1 秒間隔で 1,000 回サンプリングを行います (つまり、実行に100秒強かかります)。実行間隔やサンプリング回数、MySQL サーバ接続時のパラメータは引数で指定できるとで、詳しくは --help を参照ください。

% mpdump > mpdump.txt

SQL クエリをマージして統計表示

 mpfilter コマンドを使ってクエリの変数を「?」に書き換え、mpreport コマンドでクエリを負荷順にソートして表示します。mpreport は、「実行確率:SQLクエリ」の一覧を実行確率の降順で表示します。「クエリの実行確率」とは、そのクエリが実行されていた確率をパーセントで表示したものです。同一クエリが複数スレッドから実行されていれば、値は 100% を超えることもあります。以下の例は、パストラックの MySQL サーバの負荷を解析してみた結果です。どのようなクエリが一番負荷を与えているか (また、一番重たいクエリが、138.3% の確率=平均 1.383 スレッドによって実行されていること) が一目瞭然です。あとは、この一覧の順に、最適化が可能か、あるいは memcached でキャッシュすべきか、といったことを検討していくことになります。

% mpfilter < mpdump.txt | mpreport | head -100
138.300:select pt_sum(hits,?) as cnt from url inner join counter on url.id=counter.url where (pt_url_compress(?)<=rurlc and rurlc<pt_url_compress(?,?))
99.100:insert into tt select t1.url,cnt_mixed&? as cnt,********** where url.status_code=?
94.200:select id,pt_url_decompress(rurlc) as rurl from url where crawl_cnt<? and crawl_at>=? and crawl_at<?-?*pow(?,crawl_cnt) limit ?
22.000:select id,status_code from url where rurlc=pt_url_compress(?)
14.300:select id,pt_url_decompress(rurlc) as rurl,cnt from (select url,cnt from counter_by_day **********
9.300:insert into counter (url,referer,hits) values (?,?,pt_cnt_add(null,?,?)) on duplicate key update hits=pt_cnt_add(hits,?,?)
7.400:insert ignore into **********
6.100:select pt_sum(hits,?) as cnt from counter where url=?
5.900:select (crawl_at<? and -crawl_at<? and pt_sum(hits,floor(-crawl_at/?))>?) as recrawl from url inner join counter on url.id=counter.url where id=?
5.700:insert into **********
5.700:select t1.id,pt_url_decompress(rurlc) as rurl,cnt,score from **********
4.600:select id,name,dispname from category_ja order by disporder,id
4.300:select addr from **********
3.200:update url set crawl_at=?,lang=?,status_code=if(status_code=?,?,?) where i
d=?
(以下略)

 また、mpfilter を独自のフィルタに差し替えることで、特定のパラメータをもつ SQL クエリを別のグループとして統計に反映する、といったことも簡単に行えるようになっています。

まとめ

 ボトルネックとなっている SQL を解析する手段としては、他にスロークエリログを使う手法 (参考: MySQL 5.1のmysqldumpslowで快速チューニング - SH2の日記) や、MySQL Enterprise Monitor を使うという手もあります。これらと比較して、今回紹介した SHOW PROCESSLIST を使うメリットは、

  • 既存環境の変更が不要
  • サンプリングによるので負荷が軽い
  • スクリプトを組み合わせて、柔軟なチューニングや監視の仕組みを作ることができる

といった点になるかと思います。ボトルネックを解析する以外にも、たとえば apache に付属する rotatelogs と組み合わせて、mpdump 結果が日ごとに異なるファイルに保存されるようにしておけば、SQL クエリの負荷が長期的にどのように変化してきているかを把握して、サーバの増強計画に反映させたりすることもできるでしょう。

 最後になりましたが、今回書いた mprofile のソースコードは /platform/mysql/mprofile – CodeRepos::Share – Trac に置いてあります。それでは、have fun!

TrackBack

TrackBack URL for this entry:
http://bb.lekumo.jp/t/trackback/404050/20680945

Listed below are links to weblogs that reference MySQL のボトルネックを統計的に監視・解析する方法:

Comments

Post a comment