0

I am trying to understand the variables:

innodb_log_file_size
innodb_flush_log_at_trx_commit

As I understand the innodb_log_file_size is for undo and redo purposes for when cancelling a transaction and for when server crashes. Does this mean that I can kind of ignore this if I don't plan on writing queries of the form:

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 98;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 42;
COMMIT;

I also heard somewhere that

Every INSERT, UPDATE, DELETE is a transaction if autocommit=1.

What does this mean?

If I do zero transactions, will I gain any performance by setting innodb_flush_log_at_trx_commit=0? Since I won't need to sync after every transaction commit. But again I heard on youtube that if autocommit=1, then every insert, update, and delete "is a transaction".

I am confused.

1
  • simple form - You have not transactions only if Your server read-only, in all other cases - You have transactions. What is Your real problem, and what are Your try to achieve? – a_vlad Mar 11 '17 at 6:24
3

If you are not in a transaction (i.e. if autocommit is 1 and you didn't BEGIN a new transaction), then every query you run implicitly starts its own transaction, and the transaction is committed or rolled back (depending on circumstances, such as when certain errors occur) immediately when the query finishes executing.

In other words, all DML is done in a transaction... it's only a matter of whether it's implicit or explicit. In InnoDB, everything (except DDL of course) is a transaction.

So, whether you control transactions yourself or not doesn't impact the range of appropriate settings, here. Like almost all other variables, you should leave innodb_log_file_size at the default value unless you have a specific and valid reason to change it and understand the rationale for the change... and note that the suggestions output by tuning scripts do not constitute a valid reason to change anything. As a rule, stay away from those.

innodb_flush_log_at_trx_commit controls strict ACID compliance, and should be set to 1 if the database is critical (typical setting for master), 2 if it's important (typical setting for replicas) or 0 if you don't care all that much. It's a little more complex than that, but safest to less safe, the order is 1, 2, 0. Fastest to slowest is generally the opposite, 0, 2, 1. 2 is a relaxed version of 1 but the difference between 1 and 2 is typically more significant than the difference between 2 and 0. The impact of this variable is also fundamentally the same whether your transactions are explicit or implicit because, again, everything in InnoDB is a transaction.

2
  • I think BEGIN (aka START TRANSACTION) overrides autocommit=1. – Rick James Mar 14 '17 at 0:02
  • That's true, @RickJames. I was referring to autocommit = 1 in the same sense as OP -- meaning the session is in autocommit mode, which (sadly) does not necessarily coincide with the value of the @@autocommit variable, as you point out -- starting a transaction leaves the session not in autocommit mode, but doesn't change the variable. The variable is more of an indicator of the mode the session returns to after each COMMIT or ROLLBACK, rather than the current state. Edited the first paragraph to clarify that a bit. – Michael - sqlbot Mar 14 '17 at 0:46

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.