How can I log "show processlist" when there are more than n queries?

Question

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.

I'd like to run show full processlist; via a cron job and save output to text file if there are more than 50 rows returned.

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 50 rows).

Solution 1

First of all, make sure MySQL's slow queries log isn't what you need. Also, MySQL's -e parameter allows you to specify a query on the command line.

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

Solution 2

pt-stalk is 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.

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-digest and 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-file where you can store your login credentials away from prying eyes.