How to change innodb_flush_method in MySQL

innodb_flush_method defines the method for flushing data from RAM to disk.

I will give an example of viewing the current value:

show variables like 'innodb_flush_method';

I got a blank value, which by default means fsync (for Linux):

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method |       |
+---------------------+-------+
1 row in set (0.01 sec)

Possible values for Windows are unbuffered (default) and normal.
Possible values for Linux: fsync or 0 (default), O_DSYNC, littlesync (test), nosync, O_DIRECT, O_DIRECT_NO_FSYNC.

To change the value, specify in the MySQL server configuration file:

[mysqld]
innodb_flush_method=O_DIRECT

Restart the MySQL server to apply the changes:

service mysql restart

Let’s check the current value:

show variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+

The O_DIRECT method is reliable and at the same time can degrade performance because the system will try to avoid read/write caching. It is useful when the raid adapter does not have a battery, or when electricity often goes out and the server suddenly turns off, you also need to take into account that there should be enough IOPS performance of the disk system, if not enough, then you can choose, for example, the less reliable O_DSYNC method.

See my other articles on MySQL

Leave a comment

Leave a Reply