Tag: mysql

Maintaining a performant MySQL-backed application requires smart tuning. Without it, connections may pile up, queries stall, and user experience suffers dramatically, especially due to slow queries.

If you’re using persistent connections, idle or long-running queries can accumulate in SLEEP mode for a log time. One quick solution – especially on MySQL ≥ 5.1 – is to periodically scan the process list and terminate any query that’s been running longer than your acceptable threshold and currently in SLEEP mode.

In this post, we will learn some techniques to identify these slow queries and kill them to improve the performance of the application.

Step 1: Generate KILL QUERY Statements

This SQL query will produce the necessary KILL QUERY commands for any non-system query exceeding your time limit (e.g., 1200 seconds = 20 minutes):

SELECT
  GROUP_CONCAT(
    CONCAT('KILL QUERY ', id, ';')
    SEPARATOR ' '
  ) AS kill_cmds
FROM information_schema.processlist
WHERE user <> 'system user'
  AND time >= 1200;

You can customize the filter via the info column – for instance, if you want queries from specific database (db) or certain query patterns, you can apply those filters to info column and update the above query accordingly.

This query gives you the complete list of slow queries as per your filters. But, you need to run it everytime, whenever you want this data.

So, to automate this process, add this query to cronjob via a shell script.

Step 2: Automate with a Shell Script

Wrap this logic in a shell script and schedule it with cron to run at regular intervals. Here’s a refined example:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`

if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

This ends all slow MySQL queries running over 20 minutes and can be scheduled every few minutes in crontab.

Alternative: Log and Kill via a Temporary SQL File

Alternatively, if you want to log all the slow MySQL queries before killing them, here’s a safe method:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

This approach gives you a chance to log or inspect the kill commands before they run.

Want to learn more about tuning MySQL performance? Get High Performance MySQL book from https://amzn.to/4dVaueF

Built-In Per-Session Execution Timeout (MySQL 5.7+)

If you’re using MySQL 5.7.4+, a cleaner solution exists:

SET GLOBAL max_execution_time = 5000; -- Timeout in milliseconds

This applies a soft execution limit to all SELECT queries (read-only) at runtime. Once exceeded, MySQL aborts the query with an error but keeps the session alive.

You can also set per-session limits:

SET SESSION max_execution_time = 2000;

Or use the hint syntax on specific queries:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM your_table; 

Conclusion

Choose the approach that fits your environment:

• Older MySQL (< 5.7): Use a cron-driven shell script targeting INFORMATION_SCHEMA.PROCESSLIST.
• MySQL 5.7.4 and above: Prefer max_execution_time or per-query hints for cleaner, built-in enforcement.

They all help safeguard against runaway queries that could otherwise lock up your server.

Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.