Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
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 Jun 20 '12 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. –  RolandoMySQLDBA Jun 20 '12 at 19:42
    
Gotcha, thanks :) –  thinice Jun 20 '12 at 19:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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