Chris, >Hi there, > >I've been experimenting with InnoDB and replication and now have a few >questions...Firstly, is it a known bug that "SHOW TABLE STATUS" screws up InnoDB >transactions? To insert data into my innoDB table I've been using: >set autocommit=0; >INSERT ... >INSERT ... >... >commit; > >In total there are just over 9 million inserts. If when performing these >inserts, at the same time I do a "SHOW TABLE STATUS" then the client doing >the inserts gets a: >ERROR 1213 at line 17909: Deadlock found when trying to get lock; Try >restarting transaction
this is a known 'feature' which is also mentioned in the manual. SHOW TABLE STATUS will set an exclusive lock to the end of table to look at the current auto-inc column value, which SHOW TABLE STATUS has in its output. Since there is an uncommitted inserted row at the end of the table, the exclusive lock must wait, but the next insert will wait for the waiting lock (InnoDB uses the next-key locking algorithm). That causes a deadlock. I have to consider making SHOW TABLE STATUS to bypass the transactional mechanism when it looks at the auto-inc column value. Currently, you can use innodb_monitor to monitor the progress of large batch jobs. It does not cause any deadlocks and prints a lot of interesting info. >Also I've noticed that when replicating a transaction like the above then >the slave mysqld is not aware of the start and end of the transaction so >decides that it's going to sit there doing 9 million fsync()'s. I know >this can be turned off with innodb_flush_log_at_trx_commit=0 but are there >any plans to make the SLAVE aware of the start and end of transactions in >future mysql versions? Yes, I asked Sasha to fix this by putting trx delimiters to the binlog. I am forwarding a copy of this email to Sasha. Maybe it is best just to run the slave with ..._trx_commit=0. In high-load applications you usually set it to zero, both in the master and in the slave. >And my final question.... is it possible to change the size of >innodb_log_file_size? If this is changed in my.cnf then mysql fails to >start - what's the procedure for changing this (I've not checked the >manual for this properly yet so just tell me to RTFM if it's covered there:-P ) Yes :), look at section 5 in http://www.innodb.com/ibman.html >Best regards, >Chris-- >Chris Wilson <[EMAIL PROTECTED]> >http://www.wapmx.com Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
