Managing Long-Running Queries in MySQL

Long-running queries can be a serious thorn in the side of your MySQL database performance, causing everything from sluggish response times to full-blown bottlenecks that affect every user. Getting a handle on these pesky queries—knowing what they are, why they happen, and how to manage them — is key to keeping your database running smoothly.

Whether it’s spotting them early, stopping them in their tracks, or setting up a way to handle them automatically, this guide has you covered.

What is a Long-Running Query?

A long-running query in MySQL is a query that takes an unusually long period to execute.
The specific duration that classifies a query as "long-running" can vary, depending on your application's performance standards. Generally, if a query is running longer than usual and begins to slow down your database, it's considered long-running.

Root Causes of Long-Running Queries

The causes of long-running queries can be diverse:

• Lack of Proper Indexing – Without appropriate indexing, MySQL must scan the entire table to retrieve the required data. This process is highly inefficient, particularly for large tables, as it consumes substantial time and resources.

• Heavy Load Situations – When the server handles a high volume of queries or processes a few complex ones simultaneously, the available resources (like CPU and memory) are stretched thin. This competition for resources can delay the execution of queries, leading to longer running times, especially during peak usage periods.

• Lock Contention – This occurs when multiple transactions require access to the same data concurrently but are blocked because other operations hold the necessary locks. For instance, if one transaction is updating a row, another transaction that wants to read or update the same row will have to wait until the first one completes and releases the lock.

• Improper Normalization – While normalization helps avoid data redundancy and improves data integrity, overly normalized databases can lead to complex queries involving multiple joins. These can degrade performance. On the flip side, under-normalization may lead to excessive data duplication, resulting in larger tables and slower queries.

• Large Joins – Queries that involve joining large tables, especially without proper indexes, can be slow. The database must match rows across the tables based on join conditions, a process that can be highly resource-intensive and slow without efficient indexing.

Identifying Long-Running Queries

To effectively manage long-running queries, you first need to identify them. Here are a few methods:

1. Using SHOW PROCESSLIST

The SHOW PROCESSLIST; command is a quick way to get a snapshot of all active queries running on your server. This command displays each query along with several pieces of key information, including how long each query has been running. Those with a high "Time" value are likely your long-running queries. Here's how you can use this command:

SHOW FULL PROCESSLIST;

This command will list all the current processes, show who started them, what type of command they're running, and, crucially, how long they've been at it. If you spot any queries that have been running for an unusually long time, those are your long-running queries. You can then decide whether to dig deeper into optimizing them or simply kill them if they're dragging your system's performance down.

2. The Slow Query Log

Setting up the slow query log is another great strategy for catching those problematic queries. This handy MySQL feature logs any query that takes longer to execute than a certain threshold. It's not just about catching long-running queries – it can also help you identify queries that aren't using indexes efficiently.

To get the slow query log up and running, you'll need to tweak a few settings in your MySQL configuration file (either my.cnf or my.ini):

• Slow_query_log – Set this to 1 to enable the log.
• Slow_query_log_file – Specify the file path where you want to save the log.
• Long_query_time – Set the minimum execution time (in seconds) that qualifies a query to be logged. For instance, setting it to ‘2’ will log any queries that take more than two seconds to execute.

3. Performance Schema

MySQL’s Performance Schema is invaluable for a more detailed investigation. This tool is designed to monitor server events and track performance metrics, giving you a clearer view of query execution and overall system performance.

Make sure it's enabled in your MySQL configuration by adding the following line:

[mysqld]
performance_schema = ON

Once it's activated, you can explore a variety of Performance Schema tables to analyze the performance of your queries. For instance, if you're looking to pinpoint long-running queries, you might want to look into the events_statements_history_long table. Here’s how you can query it:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS 'Duration (seconds)'
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 10000000000;

This query helps you find any queries that have been running for more than 10 seconds. It gives you details like the SQL text and how long each query has been running.

Manually Killing Long-Running Queries

When you've identified a query that's taking too long and straining your system's resources, you have the option to manually terminate it. This is done using the KILL command followed by the specific process ID of the query.

You can find the process ID by running the SHOW PROCESSLIST command, which displays all the current running processes and their respective IDs. Look through the list for any queries that show a high "Time" value, which indicates how long they've been running.

Once you've identified a problematic query and noted its process ID, you can terminate it by using the KILL command:

KILL [process ID];

Replace [process ID] with the actual number from the SHOW PROCESSLIST output.

Be careful with this approach. Abruptly stopping a query can sometimes cause issues, such as leaving your data in an inconsistent state if the query was in the middle of writing or updating information.

How to Automate the Killing of Long-Running Queries

Setting up automation to handle long-running queries can be a real lifesaver, preventing those sluggish or unoptimized queries from hogging your database resources and slowing down, or even locking up, the entire system. But tread carefully—using this tool without the right checks in place can actually hide deeper performance problems that need your attention.

Always ensure that you have comprehensive logging and monitoring in place to analyze the impact of killed queries on your application, and consider improving those queries rather than just killing them automatically. Think of automatic termination as part of a bigger strategy for optimizing performance, not as a fix-all solution.

1. Enable the Event Scheduler

Firstly, you need to enable the MySQL Event Scheduler, which is disabled by default. The Event Scheduler allows you to create and schedule tasks that you want the server to execute automatically at predefined times. Run the following command:

SET GLOBAL event_scheduler = ON;

2. Create an Event to Kill Long-Running Queries

With the scheduler enabled, the next step is to define the actual event that will monitor and kill the long-running queries. The event will run every minute to check for queries running longer than a specified threshold (say 60 seconds). Once identified, it will automatically kill these queries. Here's a breakdown of the SQL code to set up this event:

CREATE EVENT kill_long_running_queries
ON SCHEDULE EVERY 1 MINUTE -- Specifies how often the event runs
DO
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE proc_id INT; -- Variable to store the process ID of each query
  DECLARE cur1 CURSOR FOR SELECT ID FROM information_schema.processlist
                          WHERE Command = 'Query' AND Time > 60;  -- Change '60' to your threshold in seconds
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO proc_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    KILL proc_id; -- Kills the process identified by proc_id
  END LOOP;

  CLOSE cur1;
END;

3. Set Up Max Query Execution Time in MySQL

Controlling the maximum execution time for a query helps prevent the database from being tied up by overly long-running queries. This is done using the max_execution_time system variable in MySQL 5.7.8 and later versions by setting a system-wide execution time limit for all read-only SELECT queries:

SET GLOBAL max_execution_time = 2000;

This sets the limit to 2000 milliseconds (2 seconds)

Remember, this setting does not apply to stored procedures, functions, or triggers and is reset to default upon server restart unless added to your MySQL configuration file:

[mysqld]
max_execution_time = 2000

4. Set Up Max Statement Time in MariaDB

MariaDB, while forked from MySQL, offers a similar but distinct approach to managing query execution times. Starting from MariaDB 10.1.1, you can use the max_statement_time system variable for this purpose:

SET GLOBAL max_statement_time = 2;

This limits execution time to 2 seconds for all queries.

For persistent configuration through server restarts, add this line to your MariaDB configuration file:

[mysqld]
max_statement_time = 2

Identify and Remedy Long-Running Queries with Query Analytics from Releem

Releem's query analytics tool revolutionizes how you monitor and optimize your database performance. It automatically gathers detailed information on the top 100 queries, providing key metrics like average execution time and each query's overall impact on your database’s operational efficiency.

With Releem, there's no need to manually dig through the PROCESSLIST output or look into the slow query log to identify underperforming queries. The tool features an intuitive dashboard that allows you to effortlessly sort and spot queries that are lagging or consuming excessive time. This immediate insight helps you identify and resolve bottlenecks in no time.