Mysql - How To Identify And Kill Long Running Queries (Part 1)

Long-running queries will degrade the user experience, resulting in poor efficiency of your database. This guide will aid in identifying long-running queries and will assist you by terminating such queries.

Prerequisites

Before you begin this guide you'll need the following:

• One Ubuntu 18.04 server that is configured with a firewall and a non-root user. You can refer to the Initial Server Setup with Ubuntu 18.04 guide to configuring your server.
  
  
• A MySQL server is set up as detailed in this How To Install MySQL on Ubuntu 18.04 tutorial. Ensure you set a root password for the MySQL server.

Step 1 - Access Your Server And Mysql Environment 

First, ssh into your Ubuntu 18.04 server:

ssh user_name@your_server_ip

Then, run the following command to log in to the MySQL server as the root user:

sudo mysql -u root -p

Step 2 - Identify Long-Running Queries

Run the following command to show a list of active threads:

mysql> show processlist;

You should get an output similar to the following:

Output
+------+--------------+-----------------------+-----------------+------------+----------+---------------+-------------------------------------------+
| Id   | User         | Host                  | db              | Command    | Time     | State         | Info                                      |
+------+--------------+-----------------------+-----------------+------------+----------------------------------------------------------------------+
| 7777 | digitalriver | your_server_ip:3306   | digitalriver_db | Query      | 1718     | executing     | SELECT * FROM customer_data ORDER BY id   |
| 7778 | root         | your_server_ip:3306   | mysql           | Query      | 0        | init          | show processlist                          |
+------+--------------+-----------------------+-----------------+------------+----------+---------------+-------------------------------------------+
2 rows in set (0.02 sec)

The information presented includes the "Id", which is the thread_id we will refer to later. It also contains information about the user associated with the thread and the work they are doing (Info) if any. Review the "Time" field to identify the longest-running query.

Step 3 - Terminate The Identified Long-Running 

Query By Thread Id Once you've identified the problem thread, run the following command to kill it. In the example below, replace with the ID of the query you want to terminate:

mysql> kill <thread_id>;

Step 4 - Check that the thread is terminated 

You can check that the process is terminated using the following command again:

mysql> show processlist;

You should get an output similar to the following:

+------+------------+-----------------------+----------------+-------------+------------+-------------+---------------------+
| Id   | User       | Host                  | db             | Command     | Time       | State       | Info                |
+------+------------+-----------------------+----------------+-------------+------------+-----------------------------------+
| 7778 | root       | 203.0.113.7:3306      | mysql          | Query       | 0          | init        | show processlist    |
+------+------------+-----------------------+----------------+-------------+------------+-------------+---------------------+
1 row in set (0.02 sec)

Conclusion

You should now effectively be able to identify long-running queries and terminate them. Long-running queries/statements/transactions are sometimes inevitable in a MySQL environment. On some occasions, a long-running query could be a catalyst to a disastrous event. If you care about your database, optimizing query performance and detecting long-running queries must be performed regularly.