MySQL Database io problem

--mysql Database IO Issues

----------------------2014/05/25

looking at http://www.mysqlperformanceblog.com, I found that Percona server has been released to 5.1.58, which has a significant performance improvement in the Flush log file and Doublewrite In buffer, use Fdatasync () instead of Fsync (), as described below: Fsync () have been replaced with fdatasync () to improve perfomance where possible. The former is intended to sync the metadata of the file also (size, name, access time, etc.), but for the transaction log And the doublewrite buffer, such sync of metadata isn ' t needed. Bug fixed: #803270 (Yasufumi Kinoshita). below are the following Fsync () and Fdatasync () functions: Traditional UNIX implementations have a buffer cache or a page cache (OS cache) in the kernel, and most disk I/O is buffered. When writing data to a file, the kernel usually copies the data into one of the buffers, and if the buffer is not yet full, it is not queued to the output queue, but waits for it to be full, or when the kernel needs to reuse the buffer to hold other disk block data, then the buffer is queued to the output queue, and then when it arrives at the first To perform the actual I/O operation. This type of output is called deferred write (delayed write).  deferred write reduces disk read and write times, but reduces file content updates so that data written to the file is not written to disk for a period of time. In the event of a system failure, this delay may result in the loss of file update content. To ensure consistency between the actual file system on the disk and the content in the buffer cache, the UNIX system provides sync, Fsync, and Fdatasync three functions.  The sync function simply queues all the modified block buffers into the write queue and then returns, not waiting for the actual write disk operation to end. It should be said that the speed is the fastest.  the System daemon, commonly referred to as update, periodically calls the sync function (typically every 30 seconds). This ensures that the block buffers of the kernel are flushed periodically. Command Sync (1) also calls the Sync function.  The Fsync function works only on a single file specified by the file descriptor Filedes, and waits for the write disk operation to end and then returns. Fsync can be used in applications such as databases, which need to ensure that modified blocks are immediately written to disk. The slowest speed.  The Fdatasync function is similar to Fsync, but it affects only the data portion of the file. In addition to the data, Fsync also synchronizes the properties of the updated file. The speed is somewhere between the two.  in InnoDB, the configuration parameter Innodb_flush_method has three values, fdatasync,o_dsync , and o_direct, respectively, Where Fdatasync is the default value. They control the InnoDB refresh log and Data mode, which has a significant impact on MySQL performance.  Fdatasync:InnoDB uses the Fsync () function to update the log and data files. O_dsync:InnoDB use O_sync mode to open and update the log file, and use the Fsync () function to update the data file. O_direct:InnoDB uses o_direct mode to open the data file , the log file does not use the o_direct tag, and the Fsync () function to update the log and data files.

Our current online library, this configuration parameter is set O_direct, the data file read/write operation will skip the OS cache, directly on the device (disk) read/write. Because there is no OS cache, O_direct reduces the sequential read and write efficiency of the file (because it operates directly on the disk). If you do not use O_direct, the operating system is forced to open up a large number of caches to cache read and write data, not only not improve read and write performance, but it caused a sharp decline in read and write performance (flush, the equivalent of first flush to the cache, and then under certain conditions to flush the results of the cache to disk, Equivalent to writing two times), and the buffer pool data cache and OS cache cache the same data, resulting in a double of buffer (wasted!). ), so it is possible to cause a significant decrease in performance.

With the O_direct option, a RAID card with write cache is usually required and is set to the Write-back policy (that is, the write is buffered on the RAID card cache and not directly to the hard disk) because it is typically the only way to maintain good performance.

Log buffer io

The log buffer must be persisted by the two-channel persistence store, which ensures that committed transactions are fully persisted, which can be controlled by parameter innodb_flush_log_at_trx_commit variables.

0

Writes the log buffer to the log file and refreshes it beautifully, but does nothing when the transaction commits.

1

Writes a log buffer to a log file, and each transaction commit is flushed to the persisted store.

2

Log buffers are written to the log file on each commit, but are not refreshed.

-----------------------------------------------------This is the mentioned refresh, which is using the refresh strategy we mentioned above.

Specific reference: