MySQL hangs in killed state

Question

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.

Scenario to reproduce:

• OS: CentOS 6.5
• MySQL: 5.6
• Engine: MyISAM (with at least one BLOB/LONGTEXT column)
• mysql config 'myisam_repair_threads' = 2

I have a table with 40MB of data and 1500 records. With myisam_repair_threads set to 1, OPTIMIZE completes within a second. Settings myisam_repair_threads to 2 takes forever to complete. After killing the query, it remains in the 'killed' state (at moment of writing already 3 hours). Seems like this is a bug in mysql.

However, the big question: How can I force to kill this query? It currently holds a lock on the meta data of the table, so some processes can't execute because they are waiting for this lock.

Any advice is appreciated!

Answer 1

From the DBA Square blog:

What actually happens when you run KILL?

The command sytnax is KILL [QUERY | CONNECTION] <thread_id>, where thread_id is the value from the first column of the process list output. The optional argument determines whether only running query or should the entire session be terminated. It defaults to the latter, so specifying CONNECTION is not required.

Running the command doesn’t actually do anything except for setting a special flag inside the selected thread. Therefore the kill operation doesn’t happen synchronously with the corresponding request. In many cases it takes some time for a thread or a query to stop. The flag is checked at various stages of statement execution. When it happens exactly, or how frequently, depends on the work a thread is actually doing.

...

No matter what the real cause is, it is impossible to get rid of such hanging threads without a database restart. They should, however, simply be allowed to go away on their own.

There is also no way of forcing a kill that would execute instantly like kill -9 in Unix systems.