INNODB I assume? Replicated environment?
What version of mysql? See KILL in the SQL manual.. if you do a show processlist you can get the pid and you might be able to kill it. I believe that it's safe to do a KILL on an DELETE but any decision you make her is your own... That's a LOT of data... Also.. if the kill works you could still delete in the future but put a LIMIT on the delete clause. This way you can determine how long your delete's will take. Kevin On 9/6/05, Joseph Cochran <[EMAIL PROTECTED]> wrote: > > 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 > > -- Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
