1

I tested quick and dirty --no-autocommit vs. without this option on MacBook (installed with Homebrew latest MariaDB). I restored to an empty MariaDB a 4.6 GB *.sql file. The dump made without --no-autocommit took 5m42.072s to restore and with --no-autocommit it took 1 minute longer.

Other options used for dump are --max_allowed_packet=1G --flush-logs --single-transaction --all-databases

set autocommit=0;
INSERT INTO ...
UNLOCK TABLES;
commit;

When to use --no-autocommit? In what use case this option makes sense?

2

With autocommit=1, each INSERT will be committed before moving on to the next. There is less overhead. But, if the server crashes, the table will be half built because some INSERTs will be committed; some won't.

With

SET autocommit=0;
INSERT...
INSERT...
...
commit;

it will put all the info into a redo log for the entire table. For large tables, this is more overhead. Even for a tiny tables (only one INSERT), it might be a little slower due to the extra two statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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