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)
2 in the "slow" servers makes them "fast".
I'd like to know the answer to the following questions:
- can I assume that the servers with the "slow" behavior are the right and correct ones that really implement
- 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.)