Aug 172014
Article MySQL

Sometimes, the system administrator may find that a user, or application, has started the execution of a SQL sentence that is taking minutes, hours or even days to execute. This may happen for large databases where a suitable index has not been defined to speed up the query. The long running query may be taking a large amount of the available CPU and memory resources, hindering the normal execution of other applications and services running in the system. Killing the offending application does not help, as the query continues being executed by the database server.

This post explains how to identify and kill the MySQL process that is running the query, without affecting other operations that the database server might be performing in response to requests issued by other applications.

The processlist table

There is a database “information_schema” in each MySQL instance, that is used for administrative and tuning purposes. This database includes a “processlist” table, that can be queried to retrieve information about active MySQL processes.

For instance, to get the list of SELECT sentences that are currently running, and have been running for more than 10 seconds, the processlist can be queried with the following sentence:

Note: As can be seeen, the command limits the query to processes running a “SELECT” query. It is also possible to abort the execution of processes running queries of types “INSERT”, “UPDATE” or “DELETE”, but it is advisable not to do so, as that might leave the databases in an inconsistent state.

A sample output from the above sentence might be:

This output states that the MySQL process that is executing the long running query is the process identified by ID=179205.

The INFO field contains the query string for the query that is taking more than 10 seconds to complete.

With the process ID, the DBA can proceed to abort the process to restore the service. Besides, the query string can be analyzed to find out the kind of optimization that can be made to prevent the same issue to happen again in the future.


Killing the process

The process ID retrieved in the previous step is an internal MySQL identifier. There is no relationship between this ID an the operating system process id of the processes/threads running the mysql service. To stop the process, a command “mysqladmin kill” needs to be used, as follows:

Analyzing the statement

In order to optimize the problematic statement, The DBA can analyze the sentence with the “explain” command:

Automating the procedure to stop long running queries

The procedure explained above can be added to a shell script, to set up an automated cleanup of long running semtemces:

This example assumes that the authentication required to access the database (username and password) have been added to the configuration file “~/.my.cnf”, and therefore do not need to be given as arguments to the mysqladmin command.

Index of posts related to MySQL

 Posted by at 9:53 am

 Leave a Reply