MySQL – Relevance of innodb_log_file_size Without Transactions

MySQL

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.

Best Answer

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.

Related Question