Thanks for the questions, hopefully this will help: InnoDB, yes. It's version 4.1.11, not replicated.
I am familiar with KILL. It is definitely something I CAN do, but not necessarily something I SHOULD do at this point in time. Usually when you kill a process while it's running, it will roll back the transaction before releasing the process, which often takes as long as the commit: I'd rather not kill it and have it rolling back for two weeks if I can help it. Thanks! -- Joe On 9/6/05, Kevin Burton <[EMAIL PROTECTED]> wrote: > > 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 >
