Pivopen

Sleep Queries – Determining the Source of Sleep Queries

A sleeping MySQL query is an inactive open connection. When too many exist at the same time, the MySQL server will run out of connections, causing the server to be unable to process queries. However, blindly killing sleeping queries can have bad results, such as database corruption or data loss. Therefore, one should determine which process created the sleeping query and try to address the issue from that process.

Step 1. Find the longest running sleep queries.

To find the longest running queries, run the following command: 

silo_mysql -e "SHOW PROCESSLIST" | sort -rn -k6 | head -5
# silo_mysql -e “show processlist” | sort -rn -k 6 | head -5
472447  root    127.0.0.1:55082 mysql   Sleep   979             NULL    0.000
472449  root    127.0.0.1:55086 mysql   Sleep   978             NULL    0.000
472560  root    127.0.0.1:55452 mysql   Sleep   971             NULL    0.000
472578  root    127.0.0.1:55512 mysql   Sleep   970             NULL    0.000
472573  root    127.0.0.1:55494 mysql   Sleep   970             NULL    0.000

Step 2. Determine which process created the sleeping query.

  1. Use the port number given in the previous step to help determine the process ID (pid) of the application opened that port with the MySQL server via the command: lsof -n | grep {port number} | grep -v mysql
# lsof -n | grep 55082 | grep -v mysqld
python2.7 80043             s-em7-core    6u     IPv4           60547227        0t0        TCP 127.0.0.1:55082->127.0.0.1:sync-em7 (ESTABLISHED)
  1. Use the pid returned to find the application via the command: ps -ef | grep {pid} | grep -v grep
# ps -ef | grep 80043 | grep -v grep
s-em7-c+  80043    3459  0 14:09 ?        00:00:03 /usr/bin/python2.7 /opt/em7/proc/dynamic_collect.py 0 0

s-em7-c+  80818  80043  0 14:10 ?        00:00:00 /usr/bin/python2.7 /opt/em7/proc/dynamic_collect.py 0 0

Step 3. Take corrective action.

It’s always best to allow applications to finish on their own, freeing up their connections cleanly.  However, if a process is hung or the system is on the verge of running out of connections, it may be necessary to kill the applications to free up the connections for other processes.

In the examples above, the dynamic_collect.py application (Dynamic Application Collection) has a sleeping query that’s been running for 979 seconds (just over 16 minutes).  By default, the Dynamic Application Collection process has about 16 minutes to complete before the Process Manager kills it completely (give or take a minute or two for its children processes to complete).  If the timeout value for the process was not modified and /var/log/em7/silo.log says that the Dynamic Application Collection process was SIGTERM’ed, but the instance of the application SIGTERM’ed is still running, then the process may be hung and require a manual `kill` command to get it to stop and release its MySQL connections. 

Add comment

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Most popular

Most discussed