mysql innodb_flush_log_at_trx_commit clarification

Question

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years)

So moving to a new engine is a little bit of a scary operation.

I am apprehensive about the move, as it has been on myISAM for so long without issue, but if it gives me peace of mind long term, then it is better for the DB.

I have read the whitepapers on InnoDB and it seems fairly straight forward, The one thing that I want to clear in my mind is the innodb_flush_log_at_trx_commit option

By default this is set to 1, however from my reading this causes additional overheads, if the data was credit card transactions or something like that, I can understand its needs to be there, but it seems when dealing with non life changing data, that innodb_flush_log_at_trx_commit=2 is a better option.

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

Answer 1

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

It only affects recovery.

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

Correct. There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control). If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Correct. To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance. I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance. It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?

Answer 2

It really depends on your IO subsystem on how much of a performance impact there is. No matter what value is set for innodb_flush_log_at_trx_commit, the same amount of data is being written, the only difference is when a fsync is called after the write. How much overhead the fsync is will depends on your drives, raid controllers, and even filesystem. If you have a hardware raid controller with a BBU and write cache, fsyncs are likely to be extremely fast. If you're on a laptop, a fsync may be much slower. You'll only know be testing via sysbench or similar.

Honestly, I've never found anything more then a couple of percent difference in performance, no matter which value I've set it to. Never enough to switch it away from default, even for systems that aren't important.

Answer 3

We need someone from Amazon to answer this question. I know that Aurora has a significantly different I/O layer than off-the-shelf InnoDB; I don't know if the differences hold true in AWS, too. If they do, then the details given by me (and probably by others) may not be complete.

With MyISAM, if you lose power in the middle of a multi-row UPDATE, some rows will be written to disk, some won't. MyISAM provides no way to clean up the mess.

With InnoDB, that UPDATE will be either completely finished, or not started (after automatically undoing the incomplete update).

With Aurora, the underlying I/O platform even assures that another instance of MySQL will have an equally accurate copy.

It's all magic.

Yes, you understand the tradeoffs wrt innodb_flush_log_at_trx_commit. 1 is secure; 2 is faster, with a slight risk. 1 limits the performance hit to approximately 1 fsync per COMMIT. This is fine on 90+% of systems.

Another workaround for the fsync is to have a hardware RAID controller with Battery Backed Write Cache. It makes most writes 'instantaneous'.

The COMMIT does not return to the client until the fsync is finished if flatc=1. (Sooner for 2 or 0, since not waiting for fsync.)

If you run with autocommit=ON, each DML effectively includes a COMMIT. With BEGIN...COMMIT, you are combining many DMLs into a single COMMIT -- thereby potentially running faster.

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Correct

There are several "delayed overhead" cases. Here's one: When a secondary, non-unique, index needs updating, its BTree is not immediately changed. Instead, information about the change is put into the "Change Buffer" (which lives in the buffer_pool). (Possibly similar to "delayed_key_write".) Later, multiple index changes will be batched together, thereby diminishing the number of reads and writes.

I'm pretty sure flatc=2 can be used on all nodes of a Galera cluster -- if a node crashes, it can be completely rebuilt automatically. Hence, no need for the fsync. (Group Replication may be similar.) (AWS -- I don't know.)

I/O request merging -- This probably refers to innodb_flush_neighbors, which can have a small benefit on HDD drives, but probably has no benefit on SDDs, nor with RAID caching.

Still -- Compared to MyISAM, InnoDB is inherently faster since it does not do table locking. Oracle will probably get rid of MyISAM because there will be (and mostly already is) no performance advantage to it.