Entry tags:

mysql

What statements are safe to KILL?
Recently I helped someone who accidentally ran ALTER TABLE my_table AUTO_INCREMENT=1234; on an InnoDB table, not realizing that this causes the *entire* table to be rebuilt[1]!

They were concerned that if they killed the ALTER process, InnoDB might have to ROLLBACK internally.. and it's possible that a ROLLBACK in InnoDB take up to about 30 times longer than applying the statement took.

So what's the answer? Killing SELECT and ALTER TABLE commands seem to be free of cost. Killing UPDATE, INSERT and DELETE statements is about as much fun as being stabbed in the eye:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                                      |
+----+------+-----------+------+---------+------+-------+-------------------------------------------+
|  1 | root | localhost | test | Killed  |   14 | end   | UPDATE my_innodb SET a = repeat('b', 255) | 
|  2 | root | localhost | test | Query   |    0 | NULL  | show processlist                          | 
+----+------+-----------+------+---------+------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

14 seconds in this example and still going. Can be much longer...

The second pitfall to this story, is that when they issued a KILL on the ALTER TABLE, it didn't die straight way (even though in InnoDB status it showed it wasn't rolling back). My theory as to what caused this related to what happens when you issue a KILL on a MySQL connection - it just sets a flag saying that the connection is now dead, relying on *the connection* to check this flag progressively through it's execution.

There probably are still a few places in the code where the flag is just not checked frequently enough (and hey, it probably has a small expense in doing so). The result was that they effectively had to KILL -9 their server to restart it, which is quite a shame to have to do from what was initially a simple mistake.

[1] There were a few small improvements to this in 5.1, but there's still a lot of work to do until everything is online.