About MySQL Innodb_flush_method

Innodb_flush_log_at_trx_commit determines when the transaction log Write,flush

Innodb_flush_method determines how the log and data files are write and flush. "Show variables" shows that the variable is empty, which means that the default value is set (Fdatasync)

Let's begin by understanding how files are opened, written, and written to disk on Linux IO.

General file I/O operation of the three procedures open, write, Fdatasync, respectively, open files, writing files, flush operations (the file cache brush to disk)

Open phase:

The system calls open (), using the o_wronly| o_append| O_sync Open File:

O_wronly says we open the file in a "write" way.

O_appdent writes the file in an append way.

O_dsync when writing data to a file, the write operation does not complete until the data is written to the disk (write will return success).

It corresponds to:

O_sync: This is stricter than o_dsync, when you write data to a file, you write some meta information about the data to disk, such as file length.

O_rsync indicates that the cache of the file must have been flush to disk when the file was read.

O_direct Open the file, the read/write operation skips the OS cache and reads/writes directly on device (disk). (This will reduce the efficiency of the file's sequential read and write)

Write phase: Dependencies and open phase.

Flush phase: Writes the data brush to the disk.

Fdatasync () to ensure that the data files are flush to disk.

It corresponds to:

Fsync () Fdatasync () The difference is equal to O_sync and O_dsync

The sync () function that writes a file to the OS cache is considered a write success (so this is unreliable, but performance does improve)

This column more highlights: http://www.bianceng.cn/database/MySQL/

For Linux Platform Innodb_flush_method () set to Fdatasync default, O_sync, O_direct

For Fdatasync:

Innodb uses the Fsync () function to brush data and logs. FYSNC () requires more IO (metadata information) than the Fdatasync () function, and Fdatasync () causes InnoDB crashes in some cases.

The disadvantage of using Fsync () is that the OS caches part of the data. Because Innodb can manage its own cache (Innodb buffer pool) more intelligently than the OS. This can cause a double buffering waste. (The advantages of the double buffering section: Some file systems accumulate and execute them, potentially efficient sorting, or write to the device in parallel.) Can do a pre-read), the specific situation can be specific test. When Innodb_file_per_table is enabled, the Fsync () function is used separately for each file, and cannot be merged into a single IO operation when written to more than one table. http://blog.51cto.com/user_index.php

For O_direct:

The data file uses this tag, and this option does not affect the log file, for Linux, FreeBSD, Solaris support. , this time the OS does not cache data, completely disables OS caching and enables all read and write actions to be directly attached to the storage device (avoiding double buffering), using Fsync () to write file brushes to disk (this setting does not prohibit advance reading on RAID cards, but prevents the OS from being read ahead), The cache for the raid card is still recommended for opening. Little effect on innodb_file_per_table (warm-up time will be lengthened to meet buffer pool)

For O_dsync:

This call to the log file, open () uses the O_SYNC flag, all writes are synchronized, and the data is written to disk only as valid. It does not affect data files.

It does not prohibit the OS cache.

Specific relationship:

Openlog Flush Log opendata file flush datafile

Fdatasync Fsync () Fsync ()

O_dsync O_sync Fsync ()

O_direct Fsync () o_direct Fsync ()