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 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 query the INFORMATION_SCHEMA.PROCESSLIST table.
mysql> SELECT * FROM information_schema.processlist ORDER BY id;
You should get an output similar to the following:
+------+--------------+-----------------------+------------------+---------+-------+-----------+----------------------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +------+--------------+-----------------------+------------------+---------+-------+-----------+----------------------------------------------------------+ | 7777 | digitalriver |your_server_ip:3306 |digitalriver_db | Query | 2112 | executing | select * from customers | | 7778 | root |your_server_ip:3306 | mysql | Query | 0 | executing | SELECT * FROM information_schema.processlist ORDER BY id | +------+--------------+-----------------------+------------------+---------+-------+-----------+----------------------------------------------------------+ 2 rows in set (0.01 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
Once you've identified the problem thread, you can use the KILL command to kill it. There are two variations on the KILL command. In the example below, replace <thread_id> with the ID of the query you want to terminate:
**Option 1: Terminate the connection**
It terminates the connection associated with the given <thread_id>.
mysql> KILL <thread_id>;
**Option 2: Terminate the currently executing statement**
Terminate the currently executing statement, but leave the connection intact.
mysql> KILL QUERY <thread_id>;
Step 4 - Check that the thread is terminated
You can check that the process is terminated using the following command:
mysql> SELECT * FROM information_schema.processlist WHERE id = <thread_id>;
You should get an output similar to the following:
Output
+------+------------+-----------------------+--------------+---------+-------+-----------+----------------------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +------+------------+-----------------------+--------------+---------+-------+-----------+----------------------------------------------------------+ | 7778 | root |your_server_ip| mysql | Query | 0 | executing | SELECT * FROM information_schema.processlist ORDER BY id | +------+------------+-----------------------+--------------+---------+-------+-----------+----------------------------------------------------------+
1 row in set (0.01 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.
Comments
Post a Comment