ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

Question

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC

Open log    Flush log   Open datafile   Flush data

O_SYNC       NONE           NONE         fsync ()                    

My First question is ,why do we need to Use O_SYNC to open log instead of O_DSYNC ?

With O_DSYNC, each transaction require one write, but with O_SYNC it requires two writes. I think that would make a big difference.

And I found another option in percona server ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files.

Tested this option use sysbench OLTP , single thread

response time changes from 20.11ms to 8.3 ms

Test option O_DIRECT_NO_FSYNC on mysql 5.7, it still call fsync to flush log files

  strace -p `pgrep -x mysqld`  -ff -e trace=desc >strace.out 2>&1 &
  tail -f strace.out |grep "(8\|(9"

  [pid 12258] pwrite(9, "\200\10K\376\2\0\0N\0\0\0L.\0\4\0\2018\0300\2018\2\0\2018\0304\226\240\4\0"..., 1024, 26740736) = 1024
  [pid 12258] fsync(9)                    = 0
  [pid 12258] pwrite(9, "\200\10K\377\2\0\0\25\0\0\0L\10\215\247\3\4\0\0\0\1\247\f\240\37\0\6\0\1\200\4\200"..., 1024, 26741248) = 1024
  [pid 12258] fsync(9)                    = 0

Second Question ,beside use ALL_O_DIRECT, any other method could stop innodb from writing metadata of log file into storage device ?

Answer 1

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.

Answer 2

Please research the kernel of the DB Server as to how journaling is done by the OS. Why? I recently came across a recent post from mysqlperformanceblog.com that says the following:

I also looked at the kernel ext4 sources and changelog. Up to recently, before kernel 3.2, O_DIRECT wasn’t supported with data=journal and MySQL would have issued a warning in the error log. Now, with recent kernels, O_DIRECT is mapped to O_DSYNC and O_DIRECT is faked, always for data=journal, which is exactly what is needed. Indeed, I tried “innodb_flush_method = O_DSYNC” and found the same results. With older kernels I strongly advise to use the “innodb_flush_method = O_DSYNC” setting to make sure files are opened is a way that will cause them to be transactional for ext4. As always, test thoroughfully, I only tested on Ubuntu 14.04.

Did you see that ?

O_DIRECT is mapped to O_DSYNC and O_DIRECT is faked

Huh? Seriously? That scares me.

What To Do Next

I have written about innodb_flush_method before (Clarification on MySQL innodb_flush_method variable). Granted, innodb_flush_method set to O_DIRECT lets InnoDB take charge of caching. If it is faked, you are at the mercy of the OS. To make matters worse, you are in the cloud. Now, you are really at the mercy of the OS.

Since the OS can be a bottleneck for flushing to disk, I would suggest setting innodb_flush_log_at_trx_commit to something other than 1.

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

With a value of 0, any mysqld process crash can erase the last second of transactions. The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.

With a value of 2, an operating system crash or a power outage can erase the last second of commit records. The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

I would go with innodb_flush_log_at_trx_commit set to 0 to get the log buffer flushed.

I would also increase innodb_log_buffer_size to 64M or 128M, depending on your write I/O.