Re: InnoDB Rollback - 162 hrs remaining!?
John, Marko will add a commit at every 10 000 rows to CREATE INDEX in 4.1.11. Then this kind of a runaway rollback can not happen any more. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 12:33 AM Subject: Re: InnoDB Rollback - 162 hrs remaining!? John, - Original Message - From: John Taylor [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 11:37 PM Subject: Re: InnoDB Rollback - 162 hrs remaining!? Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. that is probably the .frm file for the temp table you need to DROP. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. I almost always shut down InnoDB with killall -9 mysqld It never gets corrupt. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
John, - Original Message - From: John Taylor [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 6:54 PM Subject: InnoDB Rollback - 162 hrs remaining!? Hi, We have a large InnoDB table to which we recently added an index. That index creation thread was issued a kill yesterday due to length of time, unfortunately according to 'show innodb status' the rollback is now 162 hrs away from completion (1 every 5 secs). We are not using per-table tablespaces. MySQL version is 4.1.9, server is 2x Xeon hyperthreading, 2Gb ram. 1. Can we safely kill the Linux PID of the thread doing this? What is the risk to the data? you can safely kill mysqld. Here you find advice how to DROP the temporary table the rollback is running on: http://dev.mysql.com/doc/mysql/en/innodb-troubleshooting-datadict.html http://dev.mysql.com/doc/mysql/en/forcing-recovery.html 2. We note the server has many default settings, looks like for a 256MB machine. Can we adjust some of those mentioned in the manual for performance tuning and see a hopefully positive effect on the time? http://dev.mysql.com/doc/mysql/en/innodb-configuration.html Many thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. On Sat, 26 Feb 2005 21:42:27 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: John, - Original Message - From: John Taylor [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 6:54 PM Subject: InnoDB Rollback - 162 hrs remaining!? Hi, We have a large InnoDB table to which we recently added an index. That index creation thread was issued a kill yesterday due to length of time, unfortunately according to 'show innodb status' the rollback is now 162 hrs away from completion (1 every 5 secs). We are not using per-table tablespaces. MySQL version is 4.1.9, server is 2x Xeon hyperthreading, 2Gb ram. 1. Can we safely kill the Linux PID of the thread doing this? What is the risk to the data? you can safely kill mysqld. Here you find advice how to DROP the temporary table the rollback is running on: http://dev.mysql.com/doc/mysql/en/innodb-troubleshooting-datadict.html http://dev.mysql.com/doc/mysql/en/forcing-recovery.html 2. We note the server has many default settings, looks like for a 256MB machine. Can we adjust some of those mentioned in the manual for performance tuning and see a hopefully positive effect on the time? http://dev.mysql.com/doc/mysql/en/innodb-configuration.html Many thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
John, - Original Message - From: John Taylor [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 11:37 PM Subject: Re: InnoDB Rollback - 162 hrs remaining!? Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. that is probably the .frm file for the temp table you need to DROP. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. I almost always shut down InnoDB with killall -9 mysqld It never gets corrupt. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
