Re: innodb rollback question
Thanks everyone for the responses. Will put me on the right track here..something that was rolling through my head but I couldn't really define. I will be blogging about this later as I think it is fairly important, but often not understood by beginning/mid-level dbas. thank again, Keith William Newton wrote: Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
At 02:05 PM 11/16/2007, you wrote: How do you import the data? Load data from file is faster thought so better to export myisam -> file and then you do load data from file make sure you set autocommit=0 to make it faster Ady, Sure but won't the entire Load Data will still be wrapped in a single transaction? How long would it take to rollback that transaction if it had over 130 million rows? Mike On Nov 17, 2007 12:29 AM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > I have something to throw out. I just got done importing 140 million > rows from a myisam table to a innodb table. While it worked I had a > thought about 3/4ths of the way through. What if the transaction had > been canceled about 130 million rows in? It would have taken weeks to > roll back. > > The only way I know of to stop a rollback like that is to bring out the > sledgehammer and kill the mysql processes and then rip out the entire > database and re-import. Faster than the rollback granted - but not very > elegant. Not something you want to do on a production server either > (the only time I had this happen it was in a test environment so there > were no consequences to my subsequent actions :) > > Any better way to do this? > > Thanks, > > Keith > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
How do you import the data? Load data from file is faster thought so better to export myisam -> file and then you do load data from file make sure you set autocommit=0 to make it faster On Nov 17, 2007 12:29 AM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > I have something to throw out. I just got done importing 140 million > rows from a myisam table to a innodb table. While it worked I had a > thought about 3/4ths of the way through. What if the transaction had > been canceled about 130 million rows in? It would have taken weeks to > roll back. > > The only way I know of to stop a rollback like that is to bring out the > sledgehammer and kill the mysql processes and then rip out the entire > database and re-import. Faster than the rollback granted - but not very > elegant. Not something you want to do on a production server either > (the only time I had this happen it was in a test environment so there > were no consequences to my subsequent actions :) > > Any better way to do this? > > Thanks, > > Keith > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/
Re: innodb rollback question
Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
innodb rollback question
I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
