Identifying and stopping long running processes

in PerformanceStabilityTroubleshooting Tags: performanceSSH

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

mytop

The output will look like something like this:

Mytop output SSH

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

Output MySQL Processlist

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:

kill 47630

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:

Killed query

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:

livefpm

The output will look something like this:

Output livefpm

Once you’ve identified the long running process(es) you can kill them with the command ‘kill’ and it’s ID number (make sure you use the command ‘kill’ in lower-case letters). 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:

SSH process

Like SQL and FPM processes you can kill long running SSH processes with the command ‘ kill’ and the process ID:

kill -9 22123

0