The show processlist trick is not quite right: it is also counting the header lines. I modified it as follows: mysql> pager grep… ” | awk -F ‘|’ ‘{print $6}’ | sort | uniq -c | sort -r’ mysql> show processlist; 5 Sleep 2 Connect 1 Query 8 rows in set (0.00…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…. With pager, it is straightforward: mysql> pager grep Sleep | wc -l PAGER set to ‘grep Sleep | wc -l’ mysql> show processlist; 337 346 rows in set (0.00 sec) This should be read as ’337 out 346 connections are sleeping…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL… | 1 | msandbox | localhost | test | Sleep | 22 | | NULL | | 3 | msandbox | localhost | test | Query | 0 | NULL | show processlist | | 6 | msandbox | localhost |…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist‘ > /root/tcpdumps/`date +%s`-innodbstatus pid=$! sleep 30 kill ${pid} fi …the resulting tcpdump file (after inspecting the captured SHOW INNODB STATUS and SHOW PROCESSLIST to make sure you haven’t caught a …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual…show_privilegesblogpercona.commanual Com_show_procedure_codeblogpercona.commanual Com_show_procedure_statusblogpercona.commanual Com_show_processlistblogpercona.commanual Com_show_profileblogpercona.commanual Com_show_profilesblogpercona.commanual Com_show…_thread_sleep_delayblogpercona….
Post: Debugging sleeping connections with MySQL
February 8, 2007 By 30 Comments
Have you ever seen connection in the SHOW PROCESSLIST output which is in “Sleep” state for a long time and you have no… in the “Host” filed of SHOW PROCESSLIST output not only host but also port is specified, showing you something like “192.168.1… with the process are not only helpful to debug sleeping connections with MySQL but many other cases when you see web application…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…. This is done by adjusting innodb_thread_sleep_delay variable. Value is specified in microseconds. mysql tables in use 1, locked 0… information, there is generic statement information which is visible in SHOW PROCESSLIST showed in SHOW INNODB STATUS, such as statement which is being executed…
Comment: MySQL for Hosting Providers - how do they manage ?
November 28, 2008 By 15 Comments
… | show full processlist | | 282 | root | localhost | NULL | Query | 3 | init | select sleep(10) | +—–+——+———–+——+———+——+——-+———————–+ mysql> show full processlist; +—–+——+———–+——+———+——+——-+———————–+ | Id…
Post: MySQL for Hosting Providers - how do they manage ?
November 28, 2008 By 15 Comments
… (included in Percona releases) or Log analyzes as well as PROCESSLIST monitoring. This allows you to crack down on users which… do this by very light queries which would not show up in the PROCESSLIST or will not take too much combined time…”,1000000); select sleep(1); SET @a12:=repeat(“a”,1000000); select sleep(1); SET @a13:=repeat(“a”,1000000); select sleep(1); … Causes MySQL to “leak…
Post: Percona Toolkit by example - pt-stalk
January 3, 2013 By 3 Comments
… 24/7, because problems such as MySQL lockups or spikes of activity typically … and run in background (–daemonize). $ pt-stalk –sleep=3600 –threshold=0 –iterations=24 \ –daemonize — –… “show processlist” command, in this case, I’m using the “Host” variable. $ pt-stalk –function processlist –…
