How to Identify and Stop Long Running Processes
Long running processes can indicate that the the site might benefit from an index (SQL), that there’s inefficient or erroneous code (PHP), or that a deadlock has occurred. This article explains how to identify long running processes.
Long Running SQL Processes
To identify long running SQL processes you can use the tool
Mytop displays the running mysql processes in real time. To identify a long running SQL process with
Mytop, you can run the following command to inspect the duration of your SQL processes:
The output will look like something like this:
Alternatively you can request the processlist from MySQL yourself by using the commands:
mysql -Be 'SHOW FULL PROCESSLIST;'
The outcome will look like this (screenshot includes the commands that been used):To make your life easier, we created an alias myprocs to watch the processlist while devving. In some cases using this alias instead of
mytop will provide more accurate information.
Once you’ve identified the long running process(es) you can kill them with the command
kill and it’s ID number. The example below shows how we kill the longest running query:
If that doesn’t work, there is a more aggressive method of killing using the
-9 flag. This instructs the kernel to kill the process without running any cleanup handling and instantly stops a process:
kill -9 47630
And the outcome will be:
Long Running FPM Processes
To identify a long running PHP process you can run the following command to inspect the duration of your FPM processes in real time:
The output will look something like this: . The example below shows how we kill a all long running queries:
kill -9 26781 26782 26975
By typing several ID numbers after the kill command you can kill multiple long running processes. There won’t be any output, but you’ll see
IDLE behind the queries you killed. Your visitor will receive a HTTP Error 502 – Bad Gateway message in their browser.
Long Running SSH Process
To find long running shell processes you can use this command:
ps --sort etime -A -o etime,pid,user,args | grep php | grep -vE 'php-fpm|\-\-mode daemon|grep'
The output will look something like this:
Like SQL and FPM processes you can kill long running SSH processes with the command
kill and the process ID:
kill -9 22123