Rolling back 432 million rows in MySQL 5.0
Believe it or not, I haven't put myself into this position before. The day before yesterday I started a LOAD DATA INFILE for 760 million rows. Didn't really think too hard about it, figured I'd let it run until it finished.
Unfortunately MySQL did that thing where the row insertion rate slowed to molasses over time, and the box was hosed. I killed the query. So it started rolling back the transaction. Which was 431 million rows in.
Using `SHOW ENGINE INNODB STATUS` you can look at the number of undo entries a transaction has left to go:
---TRANSACTION 0 1161525892, ACTIVE 18598 sec, process no 20139, OS thread id 1131772224
ROLLING BACK , undo log entries 431301691
Something like that. I left it to rollback overnight. The next day, it had only moved through a few million entries. It's going to take a week or more! I could just drop the table but it's locked from the transaction (Is this always true?)
So, I made sure replicaton was stopped, flushed logs, ensured nothing was talking to the DB.
Then, tried to shut down mysql. It hung, waiting for the transaction. Waited 15 minutes.
Then, I kill -9'ed mysqld.
Then, I waited a few hours for InnoDB crash recovery to run (large 512M redo logs + no fast recovery patch).
Then, I see this:
InnoDB: Apply batch completed
[etc]
InnoDB: Starting in background the rollback of uncommitted transactions
100113 20:53:31 InnoDB: Rolling back trx with id 0 1161525892, 431119521 rows to undo
Still rolling back, but now in the background!
Finally, I dropped the table housing the offending transaction.
Bam, now the undo rows are being chewed through at the rate of 1,000,000 every couple seconds. It'll be undone in a few minutes and I can go finish the maintenance work on the DB without having to reslave it. (It's a few TB in size, reslaving is a little painful).
Is there a better way to do this? I had no idea if this process would work or not, and there is the undesireable step of kill -9'ing mysqld.
Unfortunately MySQL did that thing where the row insertion rate slowed to molasses over time, and the box was hosed. I killed the query. So it started rolling back the transaction. Which was 431 million rows in.
Using `SHOW ENGINE INNODB STATUS` you can look at the number of undo entries a transaction has left to go:
---TRANSACTION 0 1161525892, ACTIVE 18598 sec, process no 20139, OS thread id 1131772224
ROLLING BACK , undo log entries 431301691
Something like that. I left it to rollback overnight. The next day, it had only moved through a few million entries. It's going to take a week or more! I could just drop the table but it's locked from the transaction (Is this always true?)
So, I made sure replicaton was stopped, flushed logs, ensured nothing was talking to the DB.
Then, tried to shut down mysql. It hung, waiting for the transaction. Waited 15 minutes.
Then, I kill -9'ed mysqld.
Then, I waited a few hours for InnoDB crash recovery to run (large 512M redo logs + no fast recovery patch).
Then, I see this:
InnoDB: Apply batch completed
[etc]
InnoDB: Starting in background the rollback of uncommitted transactions
100113 20:53:31 InnoDB: Rolling back trx with id 0 1161525892, 431119521 rows to undo
Still rolling back, but now in the background!
Finally, I dropped the table housing the offending transaction.
Bam, now the undo rows are being chewed through at the rate of 1,000,000 every couple seconds. It'll be undone in a few minutes and I can go finish the maintenance work on the DB without having to reslave it. (It's a few TB in size, reslaving is a little painful).
Is there a better way to do this? I had no idea if this process would work or not, and there is the undesireable step of kill -9'ing mysqld.