Why Killing Slow Queries Often Fails and a Safer MySQL Timeout Solution

Background

Slow queries are common in MySQL; dynamic workloads can cause a few metrics to resonate, turning normally fast queries into full‑table scans, leading to an avalanche of slow queries and potential database failure.

Why Killing Slow Queries Is Problematic

Attempting to solve the symptom by killing slow queries faces many obstacles:

Hard to make it routine : The dynamic nature of services makes it impossible to predict when a query will become problematic, so an automated kill‑logic cannot be reliably applied.

Difficult to be accurate : Choosing thresholds (CPU load, IO, query_time, rows examined) is arbitrary; different instances and workloads require different limits, leading to over‑kill or missed kills.

Lack of automation : Deciding when to trigger a kill requires contextual knowledge that current AIDBA tools do not possess.

Hard to standardise : One‑size‑fits‑all metrics cannot cover diverse business lines, hardware, and query patterns.

DBA knowledge gap : DBAs manage the database but usually do not understand the business logic behind each SQL, making it unsafe to let them decide kill thresholds.

Business cannot decide : Business owners lack the technical expertise to set meaningful thresholds.

Responsibility ambiguity : Mistaken kills create blame‑shifting and endless tuning cycles.

Database must stay alive : Restart‑style fixes are unsuitable; the goal is to keep the service running, not to sacrifice queries.

Proposed Approaches

Three ideas are discussed to move the decision‑making to the application side.

1. Registration Model

Each SQL registers its maximum allowed execution time in a central configuration store. An agent on each MySQL instance periodically checks SHOW PROCESSLIST and kills queries that exceed the registered limit. Drawbacks: configuration explosion and heavy string‑matching overhead.

2. Signature Model

Embed a comment in the SQL, e.g.

/*!99999 21B2438F55 kill me when query_time > 10 app comments*/ select sleep(10);

The comment contains a version‑independent MySQL hint (99999), an MD5 signature, and a timeout clause. The agent matches the signature and kills the query when the timeout is exceeded. Benefits include precise targeting, lightweight parsing, and clear business‑driven responsibility.

3. Source‑code Modification

Patch MySQL to recognise a custom timeout clause inside the query and abort execution once the limit is crossed. This offers the highest control but requires maintaining a custom MySQL build, which is costly and risky.

Evaluation of the Signature Model

Advantages: precise, can be run continuously (normalised), risk is controllable because the timeout is defined by the business, DBA only executes a clear decision, and the implementation is lightweight.

Remaining risk: a rare race condition where a connection finishes a query just as a new one starts, causing an accidental kill. The probability is very low (milliseconds).

Conclusion

Killing slow queries indiscriminately is unsafe; a collaborative approach where the application tags queries with explicit timeout signatures provides a practical balance between performance protection and operational safety.