1.0Jui-Nan Lin's Bloghttps://jnlin.orgjnlinhttps://jnlin.org/author/jnlin/<p><a href="http://www.percona.com/doc/percona-toolkit/2.2/pt-stalk.html">pt-stalk</a> 是 <a href="http://www.percona.com/software/percona-toolkit">Percona Toolkit for MySQL</a> 裡面的其中一項工具。它會連到 MySQL Server，監控指定的數值，當超過指定的 Threshold 時，收集當時資料庫執行的資訊（包含正在進行的 transaction、vmstat、lsof，甚至 gdb stack trace），方便分析解決問題。</p> <p>pt-stalk 需要以 root 權限執行。執行的方法如下：<br /> <code><br /> pt-stalk --daemonize --variable Threads_connected --threshold 400 -- --defaults-file=/etc/mysql/my.cnf<br /> </code><br /> 當同時有 400 以上的 Threads 時，便會收集資料，存到 <code>/var/lib/pt-stalk</code> 內。收集的資料範例：</p> <pre>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 </pre> <p>然後看 InnoDB status，可以看到很多 transaction 正在等 query cache lock：<br /> <code><br /> MySQL thread id 688253701, OS thread handle 0x7f6a591c9700, query id<br /> 19004545452 10.1.1.94 pixblog Waiting for query cache lock<br /> SELECT * FROM `blogarticle` WHERE (`blogarticle_blogid` = 3835626) AND<br /> (`blogarticle_date` < 1388605724) AND (`blogarticle_status` IN (2,3,5,7))<br /> ORDER BY `blogarticle_date` desc, `blogarticle_id` desc LIMIT 1<br /> ---TRANSACTION 144B7B32FC, not started<br /> MySQL thread id 688253702, OS thread handle 0x7f6a787bc700, query id<br /> 19004544621 10.1.1.191 pixblog Waiting for query cache lock<br /> SELECT * FROM `blog` WHERE `blog_id` = 3126963<br /> ---TRANSACTION 144B7B32FD, not started<br /> MySQL thread id 688253706, OS thread handle 0x7f6a887e2700, query id<br /> 19004544620 10.1.1.166 pixblog Waiting for query cache lock<br /> SELECT * FROM `bloglayout` WHERE `bloglayout_id` = 3063007<br /> ---TRANSACTION 144B7B33C7, not started starting index read<br /> mysql tables in use 1, locked 0<br /> </code></p> <p>發生問題的當時確實有一個跑統計報表的 Slow Query 正在執行，於是要求該 Query 不使用 Query Cache^{<a href="#footnote_0_688" id="identifier_0_688" class="footnote-link footnote-identifier-link" title=" http://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html ">1</a>} 來解決問題。<br /> </p> rich