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.

Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like

INSERT INTO SomeTable (column1, column2) VALUES (val1,val2),(val3,val4),... ?

share|improve this question
add comment

migrated from stackoverflow.com May 21 '13 at 11:40

This question came from our site for professional and enthusiast programmers.

2 Answers

According to the documentation, AUTOCOMMIT should be turned off in InnoDB.

When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert.

When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 instead of the default value 1

share|improve this answer
    
Every bulk INSERT in my case inserts 40000 values. So after how many inserts it is better to do COMMIT query? –  zavg May 15 '13 at 15:39
    
After the entire INSERT is completed. –  Kermit May 15 '13 at 15:41
1  
Unless you have a specific reason to commit more than once before the end of it. Like if your system is not stable enough and you fear it may crash or anything alike then you may commit as you see fit to protect your self from messing up the entire bulk –  Prix May 15 '13 at 15:43
    
FreshPrinceOfSO, @Prix, I have 15 machines (Amazon AWS EC2 instances) which write continuosly to the high-memory MySQL server (Amazon RDS instance). Every machine write hundreds of million of rows. I think it is better in this case to make intermediate COMMITs? –  zavg May 15 '13 at 15:46
1  
Every machine write hundreds of million of rows per second ? per minute ? what is your benchmark, decide that after you know how many records you can insert within a range of time. –  Prix May 15 '13 at 16:31
add comment

You have a trade-off you need to be aware of.

Granted, it is true that a log flush happens with each INSERT involved with autocommit=1. Nevertheless, are there any consequences of setting autocommit=0 ?

Think about the redo logs (ib_logfile0,ib_logfile1) and the undo tablespace (inside ibdata1). Change information must be stored somewhere in case the INSERTs need to be rolled back or recovery is initiated after a crash.

Either way, there will be some disk I/O to contend with.

Additionally, consider innodb_log_buffer_size

  • Smaller Log Buffer means more frequent flushes
  • Bigger Log Buffer means you will save on disk I/O flushes but commits will hang longer
  • In most cases, the default values should suffice

These are facts. Let the buyer beware.

share|improve this answer
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.