The Batch Mode, used to update the catalog with backuped files, is designed to be able to handle multiple jobs in parallel (at least on transactional databases). But i discovered this morning that this is not the case with Innodb… By default, if you are doing massive insertion on a table that contains an AUTO_INCREMENT field, MySQL uses a lock to ensure that the increment order will be optimal.
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
To be able to use the Batch mode at full speed with Innodb, you need to use at least MySQL 5.1.21, and add the following to your my.cnf config file.
innodb_autoinc_lock_mode = 2
Warning, this mode isn’t compatible with cluster and replication features. Please read MySQL documentation for more information.
Eric Bollengier
Tags: Batch mode, Gotcha, Mysql, Performance