bash 当查询数超过 n 时，如何记录“show processlist”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9778913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I log "show processlist" when there are more than n queries?
提问by Ryan
Our mysql processes can sometimes get backlogged and processes begin queuing up. I'd like to debug when and why this occurs by logging the processlist during slow times.
我们的 mysql 进程有时会积压，进程开始排队。我想通过在缓慢的时间记录进程列表来调试何时以及为什么会发生这种情况。
I'd like to run show full processlist;via a cron job and save output to text file if there are more than 50rows returned.
show full processlist;如果50返回的行数多于行，我想通过 cron 作业运行并将输出保存到文本文件。
Can you point me in the right direction?
你能为我指出正确的方向吗？
For example:
例如：
echo "show full processlist;" | mysql -uroot > processlist-`date +%F-%H-%M`.log
I'd like to run that only when the result contains the text 50 rows in set(or greater than 50rows).
我只想在结果包含文本50 rows in set（或大于50行）时运行它。
采纳答案by Eduardo Ivanec
First of all, make sure MySQL's slow queries log isn't what you need. Also, MySQL's -eparameter allows you to specify a query on the command line.
首先，确保 MySQL 的慢查询日志不是您所需要的。此外，MySQL 的-e参数允许您在命令行上指定查询。
Turning the logic around, this saves the process list and removes it when the process list isn't long enough:
反过来，这会保存进程列表并在进程列表不够长时将其删除：
date=$(date +...) # set the desired date format here
[ $(mysql -uroot -e "show full processlist" | tee plist-$date.log | wc -l) -lt 51 ] && rm plist-$date.log
回答by Aaron Brown
pt-stalkis designed for this exact purpose. It samples the process list every second (or whatever time you specify), then when a threshold is reached (Threads_running is the default and is what you want in this case), collects a whole bunch of data, including disk activity, tcpdumps, multiple samples of the process list, server status variables, mutex/innodb status, and a bunch more.
pt-stalk正是为此目的而设计的。它每秒（或您指定的任何时间）对进程列表进行采样，然后当达到阈值时（Threads_running 是默认值，在这种情况下是您想要的），收集一大堆数据，包括磁盘活动、tcpdumps、多个进程列表示例、服务器状态变量、互斥锁/innodb 状态等等。
Here's how to start it:
下面是如何启动它：
pt-stalk --daemonize --dest /var/lib/pt-stalk --collect-tcpdump --threshold 50 --cycles 1 --disk-pct-free 20 --retention-time 3 -- --defaults-file=/etc/percona-toolkit/pt-stalk_my.cnf
The command above will sample Threads_running (--threshold; set this to your value for n), every second (default of --interval) and fire a data collection if Threads_running is greater than 50 for 1 consecutive sample (--cycles). 3 days (--retention-time) of samples will be kept and collect will not fire if less than 20% of your disk is free (--disk-pct-free). At each collection, a pcap format tcpdump will be executed (--collect-tcpdump) which can be analyzed with either conventional tcpdump tools, or a number of other Percona Toolkit tools, including pt-query-digestand pt-tcp-model. There will be a 5 minute rest in between samples (default of --sleep) in order to prevent from DoS'ing yourself. The process wil be daemonized (--daemonize). The parameters after --will be passed to all mysql/mysqladmin commands, so is a good place to set things like --defaults-filewhere you can store your login credentials away from prying eyes.
上面的命令将每秒（默认为）--threshold对Threads_running ( ; 将其设置为n的值) 进行采样，--interval如果连续 1 个采样 ( --cycles) 的Threads_running 大于 50，则触发数据收集。--retention-time如果您的磁盘可用空间不足 20% ( )，则将保留3 天 ( ) 的样本，并且不会触发 collect --disk-pct-free。在每个集合中，将执行 pcap 格式的 tcpdump ( --collect-tcpdump)，可以使用传统的 tcpdump 工具或许多其他 Percona Toolkit 工具进行分析，包括pt-query-digest和pt-tcp-model。样本之间将有 5 分钟的休息时间（默认为--sleep），以防止您自己进行 DoS。该进程将被守护进程（--daemonize）。后面的参数--将传递给所有 mysql/mysqladmin 命令，因此是设置诸如--defaults-file可以存储登录凭据的位置以防止窥探的好地方。
