tcpdump

MySQL5.0以前のバージョンでは、スロークエリログの閾値に1秒未満の値を設定できない。
設定しようとすると、エラーログを吐き、1秒にアジャストされてしまう。

mysql> set global long_query_time = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> Bye
[satoshi@studio3104 ~]$ tail -1 /var/log/mysql/mysqld.log
121002 13:56:14 [Warning] option 'long_query_time': unsigned value 0 adjusted to 1

調査目的で、"一時的にlong_query_timeを0に設定してすべてのクエリをスロークエリログに吐くようにして、mysqldumpslowでサマリーを取る"っていうようなことをすることはまぁよくあることだと思いますが、そういうことが出来ません。

いや、出来ます。tcpdump + pt-query-digestを使いましょう。
Percona Toolkit のインストールについては割愛いたします。

tcpdump + pt-query-digest

tcpdumpでMySQLの通信をキャプチャして、pt-query-digestに食わせるといい感じに解析してくれます。
ココには基本的な使い方しか書きません。オプションがたくさんありますのでドキュメントを見ましょう。
pt-query-digest — Percona Toolkit Documentation

tcpdumpからパイプで繋いで直接pt-query-digestに食わせることも出来ますが、一旦ファイルに保存しておくのがいいでしょう。
pcap形式で保存してくとファイルサイズが小さくなって良いそうです。
※参考: tcpdumpとmk-query-digestでMySQLのクエリ解析 - mikedaの日記
コマンドがニ段階になるので、本エントリでは普通にファイルに吐きます。
\-cオプションで10000を指定して、10000個パケットを取得したら終了します。

[root@studio3104 ~]# tcpdump -s 65535 -x -nn -q -tttt -i bond0 -c 10000 port 3306 > /tmp/mysql.tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
10000 packets captured
10000 packets received by filter
0 packets dropped by kernel

pt-query-digest

\--explainオプションを付けて、EXPLAINもさせてしまいましょう。
DSNを引数に取るので、tcpdumpで出力したファイルを転送して、リモートホストでEXPLAIN付きの解析を行うことも可能です。
ドキュメントにも載っていますが、tcpdumpで出力したファイルサイズが大き過ぎると、CPU、メモリをたくさん使ってしまうので、ありがたい場合もありますね。

[root@studio3104 ~]# pt-query-digest --explain h=hostname,u=username,p=password --type tcpdump /tmp/mysql.tcpdump > /tmp/pqd.out

こんな感じに出力されます

tcpdump + pt-query-digest + pt-query-advisor

`pt-query-digest --print --no-report`とすると、スロークエリログライクなアウトプットをします。
これをpt-query-advisor に食わせてあげると、こんな感じで出力します。

[root@studio3104 ~]# pt-query-digest --type tcpdump /tmp/mysql.tcpdump --print --no-report | pt-query-advisor > /tmp/pqa.out

上に表示されている、ALIとかCOLとかRESってのは、どんなクエリでしたよーってことを示しています。
詳しくはドキュメントをごらんください。
pt-query-advisor — Percona Toolkit Documentation

スクリプト

冗長ですが書きました。実行すると今何が起きてるかってのをファイルに吐きますわかります。
いろいろ適当なので使わないほうがいいと思います。

#!/usr/bin/env perl

use strict;
use warnings;

my $tcp_outfile = '/tmp/mysql.tcpdump';
my $tcp_count   = 10000;
my $tcp_iface   = 'bond0';
my $tcp_port    = 3306;

my $dsn = "h=localhost,u=user,p=password";
my $now = `/bin/date +%Y%m%d_%H%M`;
my $pqd_outfile = "/tmp/pqd.$now";
my $pqa_outfile = "/tmp/pqa.$now";

open(TCP,"+>$tcp_outfile");
open(PQD,"+>$pqd_outfile");
open(PQA,"+>$pqa_outfile");

print TCP `/usr/sbin/tcpdump -s 65535 -x -nn -q -tttt -i $tcp_iface -c $tcp_count port $tcp_port 2>/dev/null`;
print PQD `/usr/bin/pt-query-digest --explain $dsn --type tcpdump $tcp_outfile`;
print PQA `/usr/bin/pt-query-digest --type tcpdump $tcp_outfile --print --no-report | /usr/bin/pt-query-advisor`;