This is a complex topic which is very interesting - I/O, caching - i.e. what's really happening?
Percona always have good stuff about every aspect of MySQL performance - check here. Mark Leith (a senior MySQL devlelopment manager) is worth a look here. Be sure to follow the links to Baron Schwartz's and Brendan Gregg's stuff, both big hitters in the MySQL world. Morgan Tocker is a MySQL community manager and has this to say.
[EDIT - in response to comment by OP]
As I said - it's complex. What is i/o as far as Google/Amazon are concerned? See my own answer (interpretation) to this question below? How do they factor in caching? I'm sure they do it in the background. I do notice that
Amazon appear to charge by GB/month in/out, so you don't care about disk or queries with them.
Google likewise appear to charge by data in/out. So, many small queries will be equal to a few large ones - seems fair.
Both also charge for RAM, so a complex app will also increase price - again, not unreasonably.
My reading of the data/in out charge is that this only counts for bandwidth between your end users and Amazon or Google - I/O processing done within Amazon/Google is covered by your arrangement with them.
Get your calculator out! :-) IANAL!
Yes, this O_DSYNC (the option) is fundamentally useless because MySQL/InnoDB engineers saw that it was dangerous in certain cases (corruption), and disabled it in many cases, making it a synonym to O_SYNC. it also does an fsync() for the same reason.
The main thing you want to tune is O_DIRECT vs. default (flushed), and maybe ALL_O_DIRECT on Percona.
In your case, I suppose you have good latency to the filesystem (where O_DIRECT shines). Sometimes, having the transaction logs on the filesystem cache can be hurtful (although such a difference seem a bit too much for me - did you compare it with O_DIRECT, available on all versions of MySQL, which only avoids data file caching?).
Also be careful, because sysbench may not be a good representative of your real-world load. Response times can vary a lot depending on flush_log_at_trx_commit, I assume you have that as 1.
According to Dimitri, from Oracle-MySQL, the solution upstream comes in 5.7. I like it for now in Percona, as you can choose.
Best Answer
INSERT) into the Change Buffer for later processing. Again, no write to disk (yet).innodb_flush_log_at_trx_commit = 1(else this write/fsync is delayed).COMMITis finished.The idea is to have no more than one fsync per
COMMIT. Otherwise, performance would be really bad.If you are using Replication, then the binlog is written to (see
log_bin) and possibly fsync'd (seesync_binlog). And the data is shipped to each Slave. I don't know exactly where these happen in the sequence.If you are using Galera / FXC / Group Replication, the syncing to the other nodes adds another dimension to the question. (I'll assume you don't need these details.)