使用 pt-stalk 找出 MySQL 效能問題

pt-stalk 是 Percona Toolkit for MySQL 裡面的其中一項工具。它會連到 MySQL Server，監控指定的數值，當超過指定的 Threshold 時，收集當時資料庫執行的資訊（包含正在進行的 transaction、vmstat、lsof，甚至 gdb stack trace），方便分析解決問題。

pt-stalk 需要以 root 權限執行。執行的方法如下：

pt-stalk --daemonize --variable Threads_connected --threshold 400 -- --defaults-file=/etc/mysql/my.cnf

當同時有 400 以上的 Threads 時，便會收集資料，存到 /var/lib/pt-stalk 內。收集的資料範例：

2014_01_13_00_25_09-df              2014_01_13_00_25_09-lock-waits      2014_01_13_00_25_09-netstat         2014_01_13_00_25_09-ps
2014_01_13_00_25_09-disk-space      2014_01_13_00_25_09-log_error       2014_01_13_00_25_09-netstat_s       2014_01_13_00_25_09-slabinfo
2014_01_13_00_25_09-diskstats       2014_01_13_00_25_09-lsof            2014_01_13_00_25_09-opentables1     2014_01_13_00_25_09-sysctl
2014_01_13_00_25_09-hostname        2014_01_13_00_25_09-meminfo         2014_01_13_00_25_09-opentables2     2014_01_13_00_25_09-top
2014_01_13_00_25_09-innodbstatus1   2014_01_13_00_25_09-mpstat          2014_01_13_00_25_09-output          2014_01_13_00_25_09-transactions
2014_01_13_00_25_09-innodbstatus2   2014_01_13_00_25_09-mpstat-overall  2014_01_13_00_25_09-pmap            2014_01_13_00_25_09-trigger
2014_01_13_00_25_09-interrupts      2014_01_13_00_25_09-mutex-status1   2014_01_13_00_25_09-processlist     2014_01_13_00_25_09-variables
2014_01_13_00_25_09-iostat          2014_01_13_00_25_09-mutex-status2   2014_01_13_00_25_09-procstat        2014_01_13_00_25_09-vmstat
2014_01_13_00_25_09-iostat-overall  2014_01_13_00_25_09-mysqladmin      2014_01_13_00_25_09-procvmstat      2014_01_13_00_25_09-vmstat-overall

然後看 InnoDB status，可以看到很多 transaction 正在等 query cache lock：

MySQL thread id 688253701, OS thread handle 0x7f6a591c9700, query id
19004545452 10.1.1.94 pixblog Waiting for query cache lock
SELECT * FROM `blogarticle` WHERE (`blogarticle_blogid` = 3835626) AND
(`blogarticle_date` < 1388605724) AND (`blogarticle_status` IN (2,3,5,7))
ORDER BY `blogarticle_date` desc, `blogarticle_id` desc LIMIT 1
---TRANSACTION 144B7B32FC, not started
MySQL thread id 688253702, OS thread handle 0x7f6a787bc700, query id
19004544621 10.1.1.191 pixblog Waiting for query cache lock
SELECT * FROM `blog` WHERE `blog_id` = 3126963
---TRANSACTION 144B7B32FD, not started
MySQL thread id 688253706, OS thread handle 0x7f6a887e2700, query id
19004544620 10.1.1.166 pixblog Waiting for query cache lock
SELECT * FROM `bloglayout` WHERE `bloglayout_id` = 3063007
---TRANSACTION 144B7B33C7, not started starting index read
mysql tables in use 1, locked 0


發生問題的當時確實有一個跑統計報表的 Slow Query 正在執行，於是要求該 Query 不使用 Query Cache¹ 來解決問題。