With InnoDB becoming the default storage
engine in MySQL 5.5 and higher, the tips and guidelines for
InnoDB tables are now part of the main
optimization chapter. See Section 8.5, “Optimizing for InnoDB Tables”.
If you are doing a huge batch insert, try avoiding the "select from last_insert_id" that follows the insert as it seriously slows down the insertions (to the order of making a 6 minute insert into a 13 hour insert) if you need the number for another insertion (a subtable perhaps) assign your own numbers to the id's (this obviously only works if you are sure nobody else is doing inserts at the same time).
Posted by Wagner Bianchi on January 24 2010 1:58am
innodb_flush_method = O_DSYNC really did a big difference when I worked with restore and InnoDB on my environment. In conjunction of that, having the correct size of transaction logs, log buffer and start the restore with autocommit = 0 makes a good differece too.
© 2013, Oracle Corporation and/or its affiliates
User Comments
On NetBSD "innodb_flush_method parameter to O_DSYNC"
makes a HUGE difference.
If you are doing a huge batch insert, try avoiding the "select from last_insert_id" that follows the insert as it seriously slows down the insertions (to the order of making a 6 minute insert into a 13 hour insert) if you need the number for another insertion (a subtable perhaps) assign your own numbers to the id's (this obviously only works if you are sure nobody else is doing inserts at the same time).
innodb_flush_method = O_DSYNC really did a big difference when I worked with restore and InnoDB on my environment. In conjunction of that, having the correct size of transaction logs, log buffer and start the restore with autocommit = 0 makes a good differece too.
Add your own comment.