MySQL database I/O problems

MySQL database I/O problems-MySQL database I/O problems

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

When looking at the http://www.mysqlperformanceblog.com, we found that Percona Server has been released to 5.1.58, and there is a major performance improvement in flush log files and doublewrite buffer, using fdatasync () instead of fsync (), the detailed description is as follows:

Fsync () has 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 ).

The following describes the fsync () and fdatasync () functions:

Traditional UNIX implementations have buffer caches or page caches in the kernel. most disk I/O is buffered. When writing data to a file, the kernel usually copies the data to one of the buffer zones. if the buffer zone is not full, it is not discharged into the output queue, instead, wait until it is fully written or when the kernel needs to reuse the buffer to store data from other disk blocks, then the buffer is queued to the output queue, and then when it reaches the queue, to perform the actual I/O operation. This output method is called delayed write ).

Delayed write reduces the number of disk reads and writes, but reduces the update speed of the file content, so that the data to be written to the file is not written to the disk for a period of time. When the system fails, this delay may cause loss of file update content. To ensure the consistency between the actual file system on the disk and the buffer cache content, UNIX provides three functions: sync, fsync, and fdatasync.

The sync function only queues all modified block buffers into the write queue, and then returns the result. it does not wait until the actual disk write operation ends. It should be said that the speed is the fastest.

The update system daemon periodically calls the sync function every 30 seconds. This ensures regular flushing of the kernel's block buffer. The command sync (1) also calls the sync function.

The fsync function only applies to a single file specified by the file descriptor filedes, and waits until the disk write operation ends, and then returns. Fsync can be used for applications such as databases. such applications need to ensure that modified blocks are immediately written to the disk. The slowest speed.

The fdatasync function is similar to fsync, but it only affects the data part of the file. In addition to data, fsync also updates the attributes of the file. The speed is between the two.

In Innodb, configure parametersInnodb_flush_methodThere are three values: fdatasync, O_DSYNC, and O_DIRECT. fdatasync is the default value. They control the InnoDB refresh log and data mode. this parameter has a great impact on MySQL Performance.

Fdatasync:

InnoDB uses the fsync () function to update logs and data files.

O_DSYNC:

InnoDB opens and updates the log file in O_SYNC mode, and uses the fsync () function to update the data file.

O_DIRECT:

Enable InnoDB in O_DIRECT modeData files, Log files do not use the O_DIRECT flag, and use the fsync () function to update logs and data files.

In our current online database, this configuration parameter is set to O_DIRECT. read/write operations on data files will skip the OS cache and read/write directly on the device (disk. Because there is no OS cache, O_DIRECT will reduce the efficiency of sequential file read/write (because the disk is operated directly ). If O_DIRECT is not used, the operating system is forced to open up a large number of caches to Cache read and write data, which not only does not improve the read and write performance, but causes a sharp drop in the read and write performance (Flush is equivalent to first Flush to the Cache, flush the cached results to the disk under certain conditions, which is equivalent to writing twice), and both the buffer pool data Cache and OS Cache the same data, this causes the Buffer Double (waste !), Therefore, the performance may be greatly reduced.

To use the O_DIRECT option, you usually need a RAID card with a Write cache and set it to a Write-Back policy (that is, the Write cache will be buffered on the raid card cache and will not be directly written to the hard disk ), this is the only typical method to maintain good performance.

Log buffer I/O

The log buffer must be persistently stored by dual channels, ensuring that committed transactions are completely persistent. this action can be controlled by the innodb_flush_log_at_trx_commit parameter.

0

Write the log buffer to the log file and refresh it once, but do not do anything when the transaction is committed.

1

Write the log buffer to the log file, and every transaction commit is refreshed to persistent storage.

2

The log buffer is written to the log file every time it is submitted, but it is not refreshed.

----------------------------------------------------- This is the refresh mentioned above, that is, the refresh policy we mentioned above.

For details, refer: