innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

Question

I use the default settings of MySQL 5.5 on various servers and I have the following table:

create table test._perf (
    name varchar(255) not null
) engine=InnoDB;

I have an .sql script which contains 1000 lines of:

insert into test._perf values('abc');

and I run it like that:

time mysql -u root < 1000-inserts.sql

Since it runs on auto commit, there are 1000 transactions happening, one after the other.

The default setting of innodb_flush_log_at_trx_commit is 1 which does full ACID. (documentation: https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)

The documentation also states that:

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1...

All of the servers I'm testing the 1000-inserts.sql script are modern ("fast") but very different between them (metal vs vm, windows vs linux, ext4 vs other filesystems, busy vs idle), but the results are always consistent and fall in one of the following two categories:

• 1000 inserts finish in 0.2-1 seconds (fast)
• 1000 inserts finish in 20-55 seconds (slow)

Setting innodb_flush_log_at_trx_commit to 2 in the "slow" servers makes them "fast".

I'd like to know the answer to the following questions:

1. can I assume that the servers with the "slow" behavior are the right and correct ones that really implement innodb_flush_log_at_trx_commit=1?
2. can I assume that the servers with the "fast" behavior are wrong and it is the behavior explained in the documentation? (Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not.)

Answer 1

It is obvious to have better performance for the value of innodb_flush_log_at_trx_commit as 2 than 1.

As commit operations are fairly dependent on underlying system even fooling-by-OS is an extra step one takes for every commit.

For value of 1 flushing to disk operations are bound to happen every transaction while for 2 it happens once a second (depending on OS scheduling)

1. Oprn. is "slow" because you do ensure the data durability & consistency.

2. Oprn. is "Fast" because you did less work (disk operations). Even for the "lies" mysqld goes an extra step.

For innodb_flush_log_at_trx_commit=1:

for every transaction {
do-log-write
do-disk-commit (actual / fake )
}

For innodb_flush_log_at_trx_commit=2:

for every transaction {
do-log-write
}
for every second {
do-disk-commit (actual / fake )
}

Also your tests you performed contains multiple parameters for comparisons. You might also want to test with sync_binlog and flush_method changes. Mainly consider variable optimizations for better performance.

Answer 2

Thanks for the interesting research.

The "fast" ones could be the result of

• Write cache in the drive and/or the controller. But will it survive a power failure?
• SSD instead of spinning drive. This is probably safe. But the manufacturer could be playing unsafe games.
• RAID controller with "Battery Backed Write Cache" -- this is honest. That is, it is both "fast" and "safe".

Two other things to look at:

• innodb_flush_method; typically recommend O_DIRECT. (Impacts speed a little; not safety.)
• innodb_doublewrite -- for avoiding "torn pages". ON is safer. OFF is safe on FusionIO SSDs because they guarantees atomicity of 16KB writes.