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!


1 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.

  • I wouldn't be so quick on rebooting. Investigating the transaction which may cause the hanging is an option. Killing parent process might be needed. ps - aux and investigation might help as well. In any cases OPTIMIZE ... might be surrounded by internal engine operations which are impossible to follow and restart might be the only option. +1
    – ninjabber
    Apr 13, 2015 at 15:11
  • @ninjabber: restarting MySQL is not the same as restarting the server Sep 20, 2016 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.