Linux Device IO research and database performance tuning

This article mainly teaches you howLinuxIn the systemDatabaseAnd DevicesIOLibrary optimization, I believe it will be of great help for Linux beginners!

The database system is based on a file system. Its performance is closely related to the read/write mechanism of devices. Three file I/O operations closely related to database performance:

Open File

Write a file

The fdatasync flush operation caches files to disks ).

1. Open Operation

open("test.file",O_WRONLY|O_APPDENT|O_SYNC))

The system calls Open to provide a file descriptor fd for the process. O_WRONLY | O_APPDENT | O_SYNC is used to open the file:

1. O_WRONLY indicates that we open it in "write" mode, telling the kernel that we need to write data to the file;

2. O_APPDENT tells the kernel to write files in the "APPEND" mode;

3. O_DSYNC tells the kernel that when writing data to a file, only when the data is written to the disk, the write operation is deemed to have completed the write operation to return success ).

4. File logos similar to O_DSYNC, including O_SYNC, O_RSYNC, and O_DIRECT.

(1) O_SYNC is more strict than O_DSYNC, which requires that data has been written to the disk, and the attributes of the corresponding data file, such as the file length, must be updated before the write operation is successful. It can be seen that O_SYNC requires more operations than O_DSYNC.

(2) O_RSYNC indicates that the OS cache of the file must be flushed to the disk when the file is read;

(3) If you use O_DIRECT to open a file, read/write operations will skip the OS cache and read/write directly on the devicedisk. Because there is no OS cache, O_DIRECT will reduce the efficiency of sequential file read/write.

Ii. Write operations

write(fd,buf,6)

After opening the file with open to obtain the file descriptor, we can call the write function to write data. write will behave differently according to the preceding open parameters.

Iii. Flush stage

fdatasync(fd) == -1

After the write operation, we also call fdatasync to ensure that the file data is flushed to the disk. After fdatasync returns a successful result, you can think that the data has been written to the disk. Such flush functions include fsync and sync.

1. The difference between Fsync and fdatasync is equivalent to the difference between O_SYNC and O_DSYNC.

2. the Sync function indicates that the data in the file in the OS cache is put into the write queue, and you are not sure whether the disk is actually written. Therefore, sync is not supported.

Ignore the process of opening a file. We usually say that there are two stages to "write a file". One is to call the write phase, which is actually affected by the open parameter.) Call fsync or fdatasync) we call it the flush stage. Block device operations on Linux can be divided into two types:

The first type is to use the fopen/fread/fwrite series functions in the C standard library. We can call it buffered I/O.

The specific I/O path is as follows:

Application<->Library Buffer<->Operation System Cache<->File System/Volume Manager<->Device

Library buffer is the buffer of the user space provided by the standard library. You can use setvbuf to change its size.

The second type is the open/read/write series functions called by the Linux system. We can call them non-buffered I/O.

Application<-> Operation System Cache <->File System/Volume Manager<->Device

In addition, we can set the open O_DIRECT flag to implement Direct I/O or Raw I/O), that is, bypass the OS Cache, directly read the Device (that's what we want ^ o ^), which is equivalent to replacing the OS cache with the managed cache. However, we recommend that you use posix_fadvice and madvice instead of Linus in the mail list. It indicates that Direct I/O is much better than buffered I/O.

In MySQL, The Innodb_flush_method (Linux) parameter can be set to Fdatasync, O_DSYNC, and O_DIRECT. Let's take a look at how these three parameters affect MySQL's operations on logs and data files:

	Open log	Flush log	Open datafile	Flush data
Fdatasync		Fsync ()		Fsync ()
O_DSYNC	O_SYNC			Fsync ()
O_DIRECT		Fsync ()	O_DIRECT	Fsync ()

Fdatasync is considered safe because fsync is always called in MySQL to flush data. Using O_DSYNC is risky, and some operating systems ignore this parameter.

We can see that O_DIRECT and fdatasync are similar, but it will use O_DIRECT to open the data file. Data shows that O_DIRECT improves the efficiency if a large number of random write operations are performed. However, both sequential write and read efficiency are reduced. Therefore, exercise caution when using O_DIRECT.

Related mysql innodb parameters:

Innodb_flush_method has three values: fdatasync, O_DSYNC, and O_DIRECT. fdatasync is the default value.

They control the mode in which InnoDB refreshes logs and data.

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

O_DSYNC: InnoDB uses the O_SYNC mode to open and update log files, and uses the fsync () function to update data files.

O_DIRECT: InnoDB uses O_DIRECT mode to open data files and uses the fsync () function to update logs and data files.

We can see that O_DIRECT and fdatasync are similar, but it will use O_DIRECT to open the data file. Data shows that O_DIRECT improves the efficiency if a large number of random write operations are performed. However, both sequential write and read efficiency are reduced. Therefore, exercise caution when using O_DIRECT.