コネヒト開発者ブログ

コネヒト開発者ブログ

【1コマンドでOK】MySQLユーザーに贈る、スロークエリ解析の始め方

f:id:connehito:20151126162031j:plain

こんにちは！

CTOの島田(@tatsushim)です。前回の私の記事ではインフラ構成について触れました。
インフラを構築したらその運用が必要になりますね。今回は社内で行っているDBのスロークエリ解析について紹介したいと思います。

時間がない人向けに要点を3つにまとめると

  • ママリでは定期的にクエリの見直し時間をとっている
  • その理由は、レスポンスタイムがユーザーの滞在時間に大きく影響するため
  • pt-query-digestを使うとカジュアルにクエリログを解析できるから初心者にもオススメ

という感じです。それぞれについて解説していきます。

定期的にクエリの見直しをする

現在ママリでは、定期的にクエリの見直しをする時間を開発スケジュールに入れています。
それは、レスポンスタイムがユーザーの滞在時間に大きく影響するためです。
ORマッパーでコードを書いていると気づかないうちにスロークエリを発行してしまいがちなので、 定期的に対策を行うことで問題を未然に防いだり、コードを書く際にも発行クエリへの意識が向くようになりました。

スロークエリを解析してみる

元々MySQLにはスロークエリの閾値を決めて吐き出す機能がありますが、
どのクエリから対策をすれば良いかの取捨選択を行う際に、そのログを目grepするのは辛いので社内ではpt-query-digestを利用しています。

pt-query-digestとは

mysqlのslow queryのログ等を集計し、改善が必要なクエリを上位に並べてくれるツールです。

pt-query-digestで解析する

インストール

  • pt-query-digestコマンドを使うために、percona-toolkitをインストールする
  • mac環境でbrewが入ってれば、コマンドは以下のみ
brew install percona-toolkit

実行する

  • 以下コマンドで集計結果が出ます*1
  • pt-query-digestコマンドの引数にはスロークエリのログ(ここではmysql-slowquery.log)を渡します。
pt-query-digest mysql-slowquery.log

結果を見てみる

  • 結果には改善が必要なクエリが上位に出力されます。なので基本は上から潰していきます。
  • 頻度や実行時間を確認することができます。以下が1つの具体的な例です。*2
Attribute    pct   total     min     max     avg     95%  stddev  median
============ === ======= ======= ======= ======= ======= ======= =======
Count         19      44
Exec time     25     75s   514ms      3s      2s      3s   956ms      2s
Lock time      1     7ms   128us   263us   155us   214us    37us   131us
Rows sent     85   2.77M  20.33k 105.50k  64.42k 101.89k  38.80k  92.42k
Rows examine  26  36.57M 243.30k   1.40M 851.03k   1.39M 543.75k   1.03M
Query size     1  72.24k   1.64k   1.64k   1.64k   1.61k       0   1.61k
String:
Databases    foo
Hosts        10.1.0.193 (8/18%), 10.1.3.127 (6/13%)... 
Users        bar
Query_time distribution ← クエリの実行時間の分布
  1us
 10us
100us
  1ms
 10ms
100ms  ############################################
   1s  ################################################################
 10s+
  • このサンプルの場合、1sを超えることが多いクエリになっていますね。ここを改善すれば大きな改善になると思われます。

おわりに

いかがでしたでしょうか？ インストールから実行まで2コマンドでできるので、カジュアルにトライしてみると今まで見落としていたスロークエリが見えるかもしれません。
弊社ではスロークエリを減らし、レスポンスタイムが改善されたグラフを見て一緒に(･∀･)ﾆﾔﾆﾔしたい方を募集中です。

参考サイト

pt-query-digest

*1:--typeを指定することで、PostgreSQLのログ等にも対応が可能です。詳しくは参考サイトをご覧ください。

*2:中身はサンプルです。実際の解析結果ではありません