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