Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

migrated from Mar 7 '14 at 5:23

This question came from our site for professional and enthusiast programmers.


What actually happens when you run KILL?

The command sytnax is KILL [QUERY | CONNECTION] , 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.

share|improve this answer
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 '15 at 15:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.