I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.

There are many aspects, and the imports may yield anywhere between 100k-1mil+ records. This equates to a payload ranging from several MB to a few hundred MB of data.

I know temp tables are another option - but this method seems so handy.

Are there any caveats to be aware of regarding this kind of practice with a large amount of data manipulation between commits? (Outside of the typical write/indexing load burst once committed)

  • Personally, I like to have a balance. I do imports in 1k or 10k transactions, 'cause I just know that it would get to around 900k rows and then crash because of buffer size or something else ridiculous. Pretty easy to pick up from that, and not as much I/O. Commented May 13, 2016 at 20:47

1 Answer 1


One bottleneck to be aware of is the InnoDB Log Buffer. The size is set by innodb_log_buffer_size. Here is what the MySQL Documentation says about it:

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

The InnoDB Log Buffer should not be be confused with the InnoDB Buffer Pool. The major difference between them is their purpose. The InnoDB Log Buffer will basically record short-term changes that get written to the redo logs (ib_logfile0, ib_logfile1). The InnoDB Buffer Pool (sized by innodb_buffer_pool_size) caches data and index pages that are to be committed (if the pages are dirty) and eventually written) to disk. Once committed, the changes pages remain in RAM until removed via LRU rules.

Big transactions must funnel through the Log Buffer. As mentioned, a larger log buffer will reduce disk I/O. Only a large commit would present a bottleneck.

You may want to look into other InnoDB options to configure.

I have other posts about optimizing InnoDB for further research

  • somehow I knew you'd be on this. Thanks for the thorough answers that you seem to always give. Side question: Do you have any resources regarding the use of innodb_io_capacity? When the documentation suggests a 5400/7200RPM consumer SATA have a value of 100, is your strategy you suggest to just 'remove the limit' by setting that value so high?
    – thinice
    Commented Jun 20, 2012 at 19:38
  • I usually set innodb_io_capacity higher and let the hardware top it out. I'll add this to my answer right now. Commented Jun 20, 2012 at 19:42
  • @RolandoMySQLDBA What about max_allowed_packet? Commented Apr 19, 2021 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.