… also counting the header lines. I modified it as follows: mysql> pager grep -v ‘\-\+\-’ | grep -v ” Id[ |]*User ” | awk -F ‘|’ ‘{print… -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
… ’337 out 346 connections are sleeping‘. Slightly more complicated now: you want to know the number of connections for each status: mysql> pager awk… of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND=’Sleep‘; +———-+ | COUNT(*) | +———-+ | 320 | +———-+ and counting the number of connection…
Post: Debugging sleeping connections with MySQL
February 8, 2007 By 30 Comments
… can find which connections does this process have. Here you can see it has couple of memcached connections. Few MySQL connections (to the same host, which if usually bad idea) and connection to some external web… happening with the process are not only helpful to debug sleeping connections with MySQL but many other cases when you see web application…
Post: Apache PHP MySQL and Runaway Scripts
May 20, 2008 By 20 Comments
… sometimes which is obviously the problem – they take Apache Slot, MySQL Connection and other resources. I had discussion today who was thinking… long time. For simplicity I did not use MySQL, but other system call – sleep() which has similar behavior for sake of experiment… do some cleanup work – for example you can open another MySQL connection to kill the query which was running (otherwise query may…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | 1 | msandbox | localhost | test | Sleep | 403 | | NULL | | 3 …MySQL server or causing all the connections to get depleted. There are some other interesting consequences as well for application that use MySQL…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`date +%s`-innodbstatus pid=$! sleep 30 kill ${pid} fi… iterations. And watch your server to find idle-in-transaction connections that are just sitting there doing nothing, and kill them…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
…=80 innodb_file_format=barracuda innodb_file_per_table max_connections=2000 table_cache=2000 Percona Server 5.1 Results For… replacement to proper future 5.6 tuning. MySQL 5.6.2 Results With Purge Sleeps Removed I’ve made a tiny change… purge sleeps is not enough to solve them. However, MySQL 5.6.2 is experimental code and I am sure that next MySQL…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing… | grep mysqld >> ps.log sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot… max_packet_size per connection) are all connection buffers and so if you close connection MySQL can clean them up. Killing connections (or stopping related…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and …Compressionblogpercona.commanual concurrent_insertblogpercona.commanual connect_timeoutblogpercona.commanual Connectionsblogpercona.commanual …thread_concurrencyblogpercona.commanual innodb_thread_sleep_delayblogpercona.commanual Innodb_truncated_…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
Even if you are using a GUI tool to connect to your MySQL servers, one day or another, you will have to… easily solved by using the pager command: mysql> pager more PAGER set to ‘more’ mysql> select title from sakila.film; +—————————–+ | title | +—————————–+ | ACADEMY… pager to the rescue: mysql> pager grep sequence PAGER set to ‘grep sequence’ mysql> show engine innodb status\Gselect sleep(60);show engine innodb…
