How does autocommit=off affects bulk inserts performance in mysql using innodb?

Question

I know that turning off autocommit can improve bulk insert performance a lot according to:

Is it better to use AUTOCOMMIT = 0

And I have made experiments to confirm that conclusion. But what I want to know is, why turning off autocommit can improve bulk inserts? And what happens internally in inno db when a transaction is commited?

Answer 1

There are 3 ways to do transactions. All 3 of the following involve transactions.

SET autocommit = ON;

INSERT ... VALUES (1,2), (2,3), ...;  -- This is one transaction
another INSERT/UPDATE/etc;  -- This is another transaction

Or...

SET autocommit = OFF;

INSERT ... VALUES (1,2), (2,3), ...;
another INSERT/UPDATE/etc;  
COMMIT;     -- Terminate the ONE transaction (with 2 statements)

Or

BEGIN;   -- aka START TRANSACTION;
INSERT ... VALUES (1,2), (2,3), ...;
another INSERT/UPDATE/etc;
COMMIT;     -- Terminate the ONE transaction (with 2 statements)

If you are doing a bulk INSERT, you probably want it to be a transaction unto itself, since you have already combined a lot of 1-row inserts into a "bulk" insert.

Speed:

• A transaction has some overhead.
• A statement has some overhead.

Therefore:

• Fastest: Bulk INSERT (one statement in one transaction)
• Medium: A bunch of 1-row INSERT statements between BEGIN and COMMIT is
• Slowest: 1-row INSERTs, each one being a separate transaction.

Tip: Don't use autocommit=0 because you might forget to issue the COMMIT.

Answer 2

If autocommit is turned off, you can always do a ROLLBACK to get back to the state the database was in before you started doing something. COMMIT makes sure all changes to the database are written permanently, doing this for every INSERT takes more time than doing this for a lot of INSERTs, because not only the records needs to be inserted, also the indexes needs to be updated. Doing this last piece for a lot of records is more efficient than doing this for 1 record at a time.