Here's the situation. I have a table of about 900 million rows, consisting of a bigint and an int in each row. There's an index on the bigint. The table is referenced in a SELECT in other DB operations roughly 15 or 20 times per day.
We're under tight deadlines and some operations on the table were getting too slow, so two weeks ago I initiated a DELETE from the table that should remove about 600 million of the rows, based on values in the int column. It's still running. In the interim, it appears that the transaction has finished (doing a select will yield rows that should be there while rows that should not are indeed excluded), but the transaction is still "updating" in the processlist and hasn't returned to the prompt that issued it. Further, MySQL appears to be thrashing the disk, running about 250 transactions per second of about 20-25 KB per transaction. The end result of this is that any DB operation we run that's small enough to be in memory is unaffected, but anything that starts swapping to the disk is sloooow. We have changed our workflow and I've changed the code so that the affected table isn't hurting us directly anymore, but now it's two weeks on and very soon our deadline will have passed and I'll need to resolve this if the transaction doesn't finish. At this point I don't care what I should or should not have done, or what went wrong to cause this sort of hang. I need to know what my options are when we have time to work on the server without impacting other people's deadlines, which means I need a few educated guesses on what's going on with the DB. What will happen if I stop the transaction from my client? If I kill the process? What will happen if we reboot the server? What the heck is MySQL DOING that's thrashing the disk anyway? Why is it not using a higher throughput to the disk? We're okay for now, but we're kind of without a net in that backups take 16 hours to run because of the slow disk access. So we have something, it's just not very optimal. But at some point in the next few weeks I'm going to have to take some sort of action if this thing doesn't commit, and I don't want it to be offline for several days undoing whatever it's done. Any advice would be most appreciated. -- Joe
