Optimizing MySQL Database Performance: Identifying and Managing Long Queries

When working with databases, we often encounter situations where queries require significant time to execute. Long database queries can be a cause of intensive resource usage, such as CPU, I/O, and memory. In this article, I will explore the issue of long queries in MySQL, examining methods for assessing the current database workload and identifying as well as stopping long queries in MySQL.

To identify long queries in the MySQL database, you can execute the following query against the system table information_schema.processlist:

SELECT * 
FROM information_schema.processlist 
WHERE command = 'Query' AND time > <threshold_in_seconds>;

where is the threshold value of query execution time, expressed in seconds. This query will return information about all active sessions where queries are being executed and the execution time exceeds the specified threshold.

mysql> SELECT *  FROM information_schema.processlist  WHERE command = 'Query' AND time > 20;
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| ID | USER | HOST            | DB          | COMMAND | TIME | STATE      | INFO              |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| 48 | root | localhost:49454 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 49 | root | localhost:49462 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 50 | root | localhost:49476 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 51 | root | localhost:49492 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 52 | root | localhost:49502 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 53 | root | localhost:49508 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 45 | root | localhost:49430 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 54 | root | localhost:49510 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 46 | root | localhost:49434 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 47 | root | localhost:49438 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
10 rows in set, 1 warning (0.01 sec)

mysql> 

You can group the query results by the query text using the GROUP BY operator. Here's how you can do it:

SELECT SUBSTRING_INDEX(info, ' ', 1) AS query,
       COUNT(*) AS query_count,
       MAX(time) AS max_execution_time
FROM information_schema.processlist
WHERE command = 'Query' AND time > <threshold_in_seconds>
GROUP BY query;

This query will group the results by the query text (info), counting the number of executions for each query (query_count), and determining the maximum execution time (max_execution_time) for each query.

mysql> SELECT SUBSTRING_INDEX(info, ' ', 1) AS query,        COUNT(*) AS query_count,        MAX(time) AS max_execution_time FROM information_schema.processlist WHERE command = 'Query' AND time > 5 GROUP BY query;
+--------+-------------+--------------------+
| query  | query_count | max_execution_time |
+--------+-------------+--------------------+
| SELECT |          10 |                  6 |
+--------+-------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

To illustrate the above, you can simulate ten parallel requests to the MySQL database using threads or connections to the database from your application. Here's an example Python code that utilizes the mysql-connector-python library to create ten parallel queries:

import threading
import mysql.connector

def execute_query():
    try:
        # Connect to the database
        connection = mysql.connector.connect(
            host="your_host",
            user="your_username",
            password="your_password",
            database="your_database"
        )

        if connection.is_connected():
            print("Connected to MySQL database")

            # Create a Cursor object to execute SQL queries
            cursor = connection.cursor()

            # Execute the query
            cursor.execute("SELECT SLEEP(120)")  # Simulating a 2-minute query

            # Close the cursor and connection
            cursor.close()
            connection.close()
            print("MySQL connection closed")

    except mysql.connector.Error as error:
        print("Error while connecting to MySQL", error)

# Create and start threads for parallel query execution
threads = []
for i in range(10):
    thread = threading.Thread(target=execute_query)
    threads.append(thread)
    thread.start()

# Wait for all threads to complete
for thread in threads:
    thread.join()

print("All queries completed")

Please make sure you have mysql-connector-python installed (you can install it using pip install mysql-connector-python). Replace the values of your_host, your_username, your_password, and your_database with the corresponding data of your MySQL database.

This code creates ten threads, each of which executes a query to the database that takes 2 minutes (SLEEP(120) delay in the query).

mysql> SELECT *  FROM information_schema.processlist  WHERE command = 'Query' AND time > 20;
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| ID | USER | HOST            | DB          | COMMAND | TIME | STATE      | INFO              |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| 48 | root | localhost:49454 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 49 | root | localhost:49462 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 50 | root | localhost:49476 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 51 | root | localhost:49492 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 52 | root | localhost:49502 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 53 | root | localhost:49508 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 45 | root | localhost:49430 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 54 | root | localhost:49510 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 46 | root | localhost:49434 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
| 47 | root | localhost:49438 | my_database | Query   |   44 | User sleep | SELECT SLEEP(120) |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
10 rows in set, 1 warning (0.01 sec)

mysql> 

mysql> SELECT SUBSTRING_INDEX(info, ' ', 1) AS query,        COUNT(*) AS query_count,        MAX(time) AS max_execution_time FROM information_schema.processlist WHERE command = 'Query' AND time > 5 GROUP BY query;
+--------+-------------+--------------------+
| query  | query_count | max_execution_time |
+--------+-------------+--------------------+
| SELECT |          10 |                  6 |
+--------+-------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

How to stop a long-running query in MySQL?

To stop a long-running query in MySQL, you can use the KILL command to terminate the execution of a specific process. Find the session identifier (ID) and use the KILL command with that ID.

KILL <id>;

Replace with the session identifier that you want to terminate.

For example:

mysql> SELECT *  FROM information_schema.processlist  WHERE command = 'Query' AND time > 10;
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| ID | USER | HOST            | DB          | COMMAND | TIME | STATE      | INFO              |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
| 56 | root | localhost:56172 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 64 | root | localhost:56226 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 57 | root | localhost:56162 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 58 | root | localhost:56184 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 59 | root | localhost:56186 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 60 | root | localhost:56200 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 61 | root | localhost:56214 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 62 | root | localhost:56218 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 55 | root | localhost:56154 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
| 63 | root | localhost:56224 | my_database | Query   |   15 | User sleep | SELECT SLEEP(120) |
+----+------+-----------------+-------------+---------+------+------------+-------------------+
10 rows in set, 1 warning (0.01 sec)

mysql> KILL 58;
Query OK, 0 rows affected (0.00 sec)