Finding Long-Running Queries in MySQL

Poorly optimized queries and excessive connections can cause problems in MySQL, here's a quick way to identify and kill them.

First the query, then we'll explain parts of it:

SELECT *
     , concat('KILL ', processlist_id, ';')                 AS kill_command
     , concat('CALL mysql.rds_kill(', processlist_id, ');') AS rds_kill_command
FROM performance_schema.threads
WHERE type = 'FOREGROUND'
  AND processlist_command != 'Sleep'
  and processlist_command != 'Daemon'
  AND processlist_id != connection_id()
ORDER BY processlist_time DESC;

The performance_schema.threads table will return one row per server thread, and each row will have information about the thread ID, user, query, and more.

Some important columns to pay attention to:

• processlist_user: what user is executing the query, this might be helpful with identifying a culprit in a monolithic database
• processlist_time: the time in seconds that the thread has been in its current state
• processlist_state: the thread state
• processlist_info: the statement the thread is executing

And an explanation of the WHERE conditions:

• type = 'FOREGROUND' will filter to only user connection threads and will exclude internal server activity
• processlist_command != 'Sleep' will filter out threads that aren't executing anything - you might want to include these if you're debugging connection issues
• processlist_command != 'Daemon' will filter out the MySQL daemon thread
• processlist_id != connection_id() will filter out this performance_schema.threads query from the results

Note: your user will need the PROCESS privilege to access this table.

See "Finding Long-Running Queries in PostgreSQL" for the PostgreSQL version of this query.

Finding Long-Running Queries in PostgreSQL

Nov 26, 2021 · 2 min read

Poorly optimized queries and excessive connections can cause problems in PostgreSQL, here's a quick way to identify and kill them.

If you find yourself with an unexpectedly large amount of threads, you may need to investigate locks happening in your database.

Investigating Locks in MySQL

Jun 17, 2022 · 8 min read

Locking is an important part of an ACID-compliant database, but excessive locks can lead to performance degradation. Here are some strategies for investigating locks in MySQL.

Why not processlist?

These two statements are equivalent, the only difference is the length of data returned in the info column:

-- `info` returns up to 64KB
SELECT *
FROM information_schema.processlist;

-- `info` returns up to `max_allowed_packet` characters
SHOW FULL PROCESSLIST;

Note: your user will need the PROCESS privilege to see threads for other users.

But in MySQL versions before v8.0.22 (2020) , as well as v8.0 servers running without the performance_schema_show_processlist system variable enabled, these are locking operations.

From the documentation on the performance_schema_show_processlist system variable:

The default implementation [of the SHOW PROCESS statement] iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems.

This global mutex means no threads can be added or removed while the statement is executing. For that reason it's recommended to use performance_schema.threads.

Killing a single connection

The KILL statement can be used to either kill a connection (default) or just kill a query, given a processlist_id:

-- Kill the connection for 1234
KILL 1234;

-- Kill the connection for 1234
KILL CONNECTION 1234;

-- Kill only the query for 1234
KILL QUERY 1234;

The performance_schema.threads query above builds this statement per row.

Note: your user will need either the CONNECTION_ADMIN or the SUPER (deprecated) privilege to execute these statements.

Killing every query & connection

There isn't a way to kill multiple connections with one statement in MySQL, but we can use the individual KILL statements returned by the performance_schema.threads query above and execute them sequentially:

KILL 1234;
KILL 3456;
KILL 5678;

Killing a single Amazon RDS connection

Amazon RDS doesn't provide shell access to MySQL instances, and it restricts access to certain system procedures such as KILL. Here are the alternative statements Amazon provides:

-- Kill the connection for 1234
CALL mysql.rds_kill(1234);

-- Kill only the query for 1234
CALL mysql.rds_kill_query(1234);

The performance_schema.threads query above builds this statement per row.