Re: InnoDB Deadlock cannot find free slot for undo
Bryan, - Original Message - From: Bryan Heitman [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, April 01, 2004 1:53 AM Subject: InnoDB Deadlock cannot find free slot for undo Looking for some help on tuning my InnoDB settings. I received a InnoDB deadlock. From analyzing show innodb status -- * There were a ton of processes in the active status not doing anything. * The log flushed up to was moving but the last checkpoint at was extremely old. Error in mysql error log scrolling repeatedly: 16:21:37InnoDB: Warning: cannot find a free slot for an undo log. Do you have too many active transactions running concurrently? version | 4.0.17-standard-log current innodb settings set-variable = innodb_buffer_pool_size=768M set-variable = innodb_additional_mem_pool_size=20M set-variable= innodb_log_file_size=256M set-variable= innodb_log_files_in_group=3 Any help would be appreciated. you probably have 500 open transactions at a time, and InnoDB ran out of undo log slots in the header of its rollback segment. You should commit your transactions. Best, Bryan Heitman [EMAIL PROTECTED] 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Deadlock cannot find free slot for undo
No, we did not run out of disk space, we had 102GB free using reiserfs partition. We are running all the defaults for transactions, so we have REPEATABLE-READ as our tx_isolation. We should have allowed enough time for this transaction to finish, this is in a production environment that has run fine for over a year now with InnoDB without this problem. We do not commit anything, its all auto_commit. Something must have put an extreme burden on the box to cause this undo log to fill up, but what must we increase so that we have a bigger log? I'm unsure if the problem really was related to our undo log needing to be bigger, though it may have. What is really weird is that we had ~500 MySQL processes that were sitting in the Sleep status on MySQL's SHOW PROCESSLIST for over 2000 seconds, but in SHOW INNODB STATUS they were sitting in the ACTIVE status. While these were like this, InnoDB would not move it's last checkpoint time. We had to manually kill each one of the MySQL processess sitting in the Sleep/ACTIVE status, once we accomplished this, InnoDB continued to clean up it's group log files, and teh Last CheckPoint caught up with the sequence number/flushed up to numbers. Any help or a point in the right direction in this situation would be helpful Bryan Heitman FuseMail - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Bryan Heitman' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 01, 2004 12:15 AM Subject: RE: InnoDB Deadlock cannot find free slot for undo Did you run out of disk space? If not what transaction model are you using? Is it repeatable read? If so, are you allowing the transaction to finish with a commit? If not your filling your transaction log and that's how your getting this message. Call commit on the session OR SET AUTOCOMMIT=1 on connection. Additionally set your innodb_deadlock_timeout var. -- DVP -Original Message- From: Bryan Heitman [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 2:53 PM To: [EMAIL PROTECTED] Subject: InnoDB Deadlock cannot find free slot for undo Looking for some help on tuning my InnoDB settings. I received a InnoDB deadlock. From analyzing show innodb status -- * There were a ton of processes in the active status not doing anything. * The log flushed up to was moving but the last checkpoint at was extremely old. Error in mysql error log scrolling repeatedly: 16:21:37InnoDB: Warning: cannot find a free slot for an undo log. Do you have too many active transactions running concurrently? version | 4.0.17-standard-log current innodb settings set-variable = innodb_buffer_pool_size=768M set-variable = innodb_additional_mem_pool_size=20M set-variable= innodb_log_file_size=256M set-variable= innodb_log_files_in_group=3 Any help would be appreciated. Best, Bryan Heitman [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB Deadlock cannot find free slot for undo
Did you run out of disk space? If not what transaction model are you using? Is it repeatable read? If so, are you allowing the transaction to finish with a commit? If not your filling your transaction log and that's how your getting this message. Call commit on the session OR SET AUTOCOMMIT=1 on connection. Additionally set your innodb_deadlock_timeout var. -- DVP -Original Message- From: Bryan Heitman [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 2:53 PM To: [EMAIL PROTECTED] Subject: InnoDB Deadlock cannot find free slot for undo Looking for some help on tuning my InnoDB settings. I received a InnoDB deadlock. From analyzing show innodb status -- * There were a ton of processes in the active status not doing anything. * The log flushed up to was moving but the last checkpoint at was extremely old. Error in mysql error log scrolling repeatedly: 16:21:37InnoDB: Warning: cannot find a free slot for an undo log. Do you have too many active transactions running concurrently? version | 4.0.17-standard-log current innodb settings set-variable = innodb_buffer_pool_size=768M set-variable = innodb_additional_mem_pool_size=20M set-variable= innodb_log_file_size=256M set-variable= innodb_log_files_in_group=3 Any help would be appreciated. Best, Bryan Heitman [EMAIL PROTECTED] -- 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]
