Re: LARGE operation stuck. What now?
Hello. Here is described the possible way of how to force the rollback (you can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback): http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Joseph Cochran [EMAIL PROTECTED] wrote: 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! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LARGE operation stuck. What now?
Thank you. This is a very promising answer. I don't know that we want to drop the table if we don't have to, but knowing that we can restart the DB without the rollback operation is a boon! We could certainly do a mysqldump of just that table (which works fine, we continue to run nightly backups), then restore it if we do need to drop it. -- Joe On 9/7/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Here is described the possible way of how to force the rollback (you can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback): http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Joseph Cochran [EMAIL PROTECTED] wrote: 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! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: LARGE operation stuck. What now?
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 slw. 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
Re: LARGE operation stuck. What now?
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 slw. 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
