'Kill slow SELECT queries' under MySQL Governor doesn't help

Issue

MySQL queries that are stuck for several hours can't be killed by MySQL Governor for some reason.

Most of the time when we see this - when slow 'Select' query is a part of 'Update' (or another) query, then such 'Select' slow query is not being killed in time for some reason. An example of SQL query:

UPDATE pmd_listings l SET l.impressions_weekly=(SELECT COALESCE(SUM(count),0) FROM pmd_statistics;

Environment

• MySQL Governor
• any CloudLinux server

Solution

1) check if Slow queries option is enabled/configured in the config:

cat /etc/container/mysql-governor.xml | grep slow_queries <logqueries use="on"/><slow_queries log="/var/log/kill.log" run="on"/> <restrict_mode unlimit="60s" use="limit"/></governor>

2) check the logs to find the messages like "Can't execute sql request. Kill query"

To kill an SQL query governor calls "KILL CONNECTION %ld" query where %ld is processlist_id
The query should start with the "SELECT" string.

If you found a similar warning as above in the logs, then it appears that the root cause is that processes are stuck in "Waiting for table level lock" status, and this issue should be resolved first. One of the possible solutions you can find here:
https://support.cpanel.net/hc/en-us/articles/1500004842842-Queries-to-MyISAM-table-stuck-in-Waiting-for-table-level-lock-state

Cause

The following SQL queries states used to kill:
"Copying to tmp table",
"Copying to group table",
"Copying to tmp table on disk",
"removing tmp table",
"Sending data",
"Sorting for group",
"Sorting for order"
If the query is stuck in other states - like "Waiting for table level lock" - it wouldn't be killed.