0

I've set max_execution_time to a sensible value so I can protect MySQL from rogue queries done by users to prevent resource exhaustion.

I need though to find what queries are terminated, to analyse them and potentially improve them. Looking to the slow-query log I cannot find them, or rather I did not find a hint that the query did not complete due to the time threshold. Grepping the error 3024 (ER_QUERY_TIMEOUT) did not help neither.

Is there a way to find this information on mysql 5.7 using mysql tooling or external like percona (pt-query-digest did not helped) ?

Baptiste Mille-Mathias
  • 2,144
  • 4
  • 31
  • 37
  • Not sure about a direct way. But, try this out. `SELECT * FROM information_schema.PROCESSLIST WHERE STATE = 'executing' AND TIME > ?` where `?` can be set to `max_execution_time-2` seconds. This query will give the queries that will just timeout in 2 seconds. You have to keep polling this and logging the output. So this have to be written as some automated service that keeps running – Ishan May 04 '23 at 10:41
  • You may want to check other `STATE` values too other than `executing` - say there would be something that is returning result. Not sure if the query was executed within the set time out, but then returning the result overshot that limit, does it result it time out or not. – Ishan May 04 '23 at 10:44
  • Or, maybe just irrespective of the state, just based on the command: `SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > ?` – Ishan May 04 '23 at 11:40

0 Answers0