Many of the Google results you’ll get for searching along the lines of “mysql alter table hang” or “mysql alter table frozen” or “mysql alter table stuck” will correctly point out that it often takes a long time for an alter table to finish. They will also point out that killing an alter table is not an instant operation, it takes time for the kill to delete the temporary table. But none of these morsels of advice covers the situation we recently found ourselves in, so I will share some details.
We had a write-intensive database table with small row size and a moderately big size (~700mb). After waiting about 30 minutes for the alter table to complete, I finally got fed up and attempted to kill the alter table’s thread. The thread did indeed change to a “killed” state rather than a query, but that killed state was still there 30..60..90 minutes later (all the while claiming that it was “renaming results table”). Moreover, about 1800 threads that had become stuck waiting on the alter were sitting around on our Mysql process list.
I tried various remedies, from looking for deadlocks in “SHOW INNODB STATUS\G” (no deadlocks, just 1800 patiently waiting threads), to looking for the temp table that had supposedly been getting built to see if it was progressing (it wasn’t). About three hours into the event, I decided we might as well kill the 1800 stuck processes in case it would take a DB restart to finally clear this alter table. I followed these instructions on how to kill Mysql threads en mass, and got us down to just the alter thread along with a couple waiting threads that hadn’t been in my list of threads to kill.
Then, about two minutes later, something mysterious happened. The alter command finished.
All of the Googling and Mysql info delving I could manage would not reveal what it was that had left the ALTER command stuck so much longer than it typically takes for a table of its size. But whatever was keeping the alter command from finishing seems clearly to have been related to the 1800 threads that were sitting around waiting on the alter to complete. Once we purged these, the alter finished within a couple minutes.
Hopefully this is a helpful alternate idea to try if you should find yourself with an ALTER TABLE that refuses to die no matter what steps you take.
Most likely your threads weren’t completely stuck, but being processed very slowly. This can happen on tables that do table level locking, basically everyone was vying for priority. The problem is that when the alter thread takes priority (and the kill version) it has to load/unload from a tmp table, so the lock takes forever. Using innoDB tables should prevent this.
@Daniel – How to fix the priority issue?
It was exactly my problem, thank you very much for sharing! I killed all other queries and the ALTER TABLE query finished in less than a minute… I was waiting for 3 hours!! Thank you very much