Use timeouts to prevent long-running SELECT queries from taking down your MySQL

You may be wondering if your users can slow down or even DOS your MySQL database with an unlucky request. If it contains just the wrong number of conditions in your advanced search form triggering a full table scan on a dataset that is much larger than you have anticipated they probably can.

If you provide advanced search, aggregated statistics, reporting or data export functionality in your application, then the occasional long running query is part of the design. You may not have suffered database overload and slowdowns yet, only because your users aren’t constantly using these features.

Up until MySQL 5.7 you had no easy way to protect yourself from large amounts of users DDoSing your database with long running queries. In addition the PHP max_execution_time does not apply to waiting for network calls as shown in this previous blog post on max_execution_time. This means your PHP scripts and MySQL connections can be blocked as long as a query runs, worst case for several minutes or hours.

When you Google this problem, the recommended solution is running a daemon or cronjob that monitors the process list and just kills the long-running queries. This certainly works, but you don’t know who is running the query and why, which prevents you from applying more fine granular timeouts based on the context (web vs. background process for example).

With MySQL 5.7 you can now use a new optimizer query hint to configure the max execution time of SELECT queries in milliseconds.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ status, count(*) FROM articles GROUP BY status ORDER BY status; 

Or you can set a session-wide or global timeout:

SET SESSION MAX_EXECUTION_TIME=2000; SET GLOBAL MAX_EXECUTION_TIME=2000; 

The timeouts only apply to read-only SELECT queries.

If your query takes too long, it fails with the following error:

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded 

You can handle this error centrally in your application and display a message to users that the response took too long and was aborted.

Using an Exception Tracking software (such as Tideways) you can log which users, transactions or pages are causing these queries to get aborted, how often they happen and if the query performance can be improved.