Killing Slow Queries in MySQL: Problems, Drawbacks, and Practical Design Solutions

Background – Slow queries are ubiquitous in MySQL, but in dynamic environments they can trigger a resonance effect that escalates into a full‑blown avalanche, causing severe database failures.

Why killing slow queries is not a panacea – The author lists seven major drawbacks of automatically killing slow queries: difficulty achieving normalisation, accuracy, automation, uniformity, DBA knowledge gaps, lack of business decision authority, and unclear responsibility, all of which can lead to mis‑kills and further instability.

Proposed design approaches

1. Registration system – DBA provides an agent that reads a central configuration of SQL statements with allowed execution times and kills matching queries. This approach suffers from scalability issues as the configuration grows.

2. Signature‑based killing – Embed a special comment in the SQL that includes a version marker, an MD5 signature, and a kill condition, e.g.: /*!99999 21B2438F55 kill me when query_time > 10 app comments*/ select sleep(10); The agent parses the comment, matches the signature, and kills the query when the condition is met.

3. Source‑code modification – Alter MySQL’s source to recognise a timeout clause in the statement and abort execution once the limit is exceeded. This method is powerful but requires high maintenance effort and deep expertise.

Benefits of the signature‑based method – Precise per‑statement control, easy normalisation via a lightweight agent, high efficiency, clear business‑driven responsibility, and low risk of accidental kills (the probability of killing a different statement in the millisecond window is negligible).

Remaining risks – The only notable risk is occasional mis‑kill when a new statement starts on the same connection just as the kill is issued; however, this is rare and can be mitigated.

Conclusion – Killing slow queries must be done cautiously; a practical solution is to let the application annotate SQL with timeout metadata, allowing an external agent to enforce execution limits safely, thereby protecting database stability while keeping responsibilities clear between developers and DBAs.