innodb_flush_method: Configure InnoDB Flush Method

Summary: in this tutorial, you will learn about the InnoDB flush methods used to flush data from memory to disk and how to configure the InnoDB flush method using the innodb_flush_method configuration option.

Introduction to the InnoDB Flush Method

The InnoDB flush method determines how data flushes from memory to disk. The InnoDB flush method highly impacts performance and data safety.

MySQL uses the innodb_flush_method global variable to determine the InnoDB flush method.

InnoDB flush methods for Unix-like systems

The following table lists the main InnoDB flush methods on Unix-like systems, they can be a text string (fsync) or a number (0).

Method	Number	Meaning
`fsync`	0	This method utilizes the operating system’s `fsync()` function to ensure data is flushed to disk, enhancing data durability. However, it may impact performance due to disk I/O latency. The `fsync` is the default setting.
`O_DSYNC`	1	This method is similar to `O_DIRECT`, which bypasses the buffer cache but allows for metadata synchronization, providing a balance between durability and performance. It can be an effective choice if the file system supports `O_DSYNC` but not `O_DIRECT`.
`O_DIRECT`	4	This method bypasses the operating system’s buffer cache, writing data directly to disk, potentially improving write performance by eliminating double buffering. It requires a file system with direct I/O support. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
`O_DIRECT_NO_FSYNC`		This method utilizes `O_DIRECT` for flushing I/O but omits the `fsync()` system call after each write operation.

InnoDB flush methods for Windows

InnoDB uses the unbuffered flush method on Windows. This method writes data directly to the Windows file system without caching.

The unbuffered flush method can improve performance for write-intensive applications. But it comes with a risk of data loss in case of crashes.

Checking the InnoDB flush method

First, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, check the innodb_flush_method variable:

show global variables like 'innodb_flush_method';Code language: SQL (Structured Query Language) (sql)

On Windows, the output will look like:

+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| innodb_flush_method | unbuffered |
+---------------------+------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

On Unix-like systems, the output will be:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method | fsync |
+---------------------+-------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Setting the InnoDB Flush Method

First, open the MySQL configuration file and modify the InnoDB flush method:

innodb_flush_method = O_SYNCCode language: SQL (Structured Query Language) (sql)

Second, save the file and restart the MySQL server.

Third, connect to MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Third, show the variable innodb_flush_method:

show global variables like 'innodb_flush_method';Code language: SQL (Structured Query Language) (sql)

Output:


+---------------------+---------+
| Variable_name       | Value   |
+---------------------+---------+
| innodb_flush_method | O_DSYNC |
+---------------------+---------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

Summary

InnoDB flush methods determine how MySQL flushes data from memory to disk.
Use the innodb_flush_method configuration variable to configure the InnoDB flush method.

Was this tutorial helpful?