MySQL transaction size - how big is too big?

Question

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)

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.

• innodb_flush_method : This dictates how flush is handled and whether or not to allow the OS to cache writes to InnoDB (You must experiment between O_DSYNC and O_DIRECT)
• innodb_read_io_threads (Use the Max : 64)
• innodb_write_io_threads (Use the Max : 64)
• innodb_io_capacity (use higher values like 5000 or 10000, but the hardware will top it out for you, In other words, let the hardware decide the IO capacity once you assign a huge number)
• innodb_thread_concurrency : Default value is 0. I learned firsthand from Ronald Bradford at Percona NYC 2011 to leave this number at 0. It lets InnoDB decide the best number of concurrency tickets it could handle)

I have other posts about optimizing InnoDB for further research

• Optimizing InnoDB default settings
• How do you tune MySQL for a heavy InnoDB workload?
• Insert-heavy InnoDB table won't use all my CPU
• How to improve InnoDB DELETE performance?
• MySql - Clean ibdata1