innodb_flush_method

The innodb_flush_method variable instructs InnoDB on how to open and flush log and data files. Innodb_flush_method value is automatically configured to the default value when not explicitly defined.

Releem automatically tunes innodb_flush_method and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.

The innodb_flush_method variable is used to define how log and data files are flushed from InnoDB.
Optimizing this value may yield improvements in I/O throughput.

Innodb_flush_method configuration can cause unexpected and unwanted results. Testing and benchmarking is recommended. The most common configuration is setting innodb_flush_method to O_DIRECT. This reduces swap pressure by preventing unnecessary double buffering, which is when the operating system is buffering some data in its cache alongside InnoDB's caches.

For reference, the innodb_flush_method default values for MySQL, Percona, and MariaDB are listed above.

Innodb_flush_method variable configuration:

1. To change innodb_flush_method in the command line, enter:
   –innodb-flush-method= VALUE
   
   Replace the bolded value with the best option for your database.
   
2. You can also change the value of innodb_flush_method using a configuration file. To do so, you will use mysqld. You will need to restart MySQL:
   [mysqld]
   Innodb_flush_method NAME
   
   Adjust the bolded value with one of the valid variable values, to suit your database needs.

Reminder that changes to innodb_flush_method should be tested and benchmarked thoroughly beforehand to prevent unexpected behaviors. To select the best value for innodb_flush_method, you can learn more about each value below.

Innodb_flush_method configuration for Unix systems:
1. fdatasync:

• default for Percona, flushes only data

2. fsync():

• default for MySQL and versions of MariaDB <= 10.5 and >= 10.37
• flushes data, associated metadata, and log files
• causes double buffering

3. O_DIRECT:

• default for MariaDB >= 10.6
• the best configuration recommendation for most file systems
• O_DIRECT responsible for opening files
• Flushes only data files
• Flushes files to disk using fsync() method to ensures no double buffering
• All reads and writes goes directly to disk.
• While flushing, double buffering & read ahead is disabled

3. O_DSYNC:

• Flushes only data files
• All writes synchronous
• O_SYNC flag set to open()
• O_SYNC doesn't disable double buffering
• O_DSYNC will flush data, while O_SYNC flushes both data and metadata

4. O_DIRECT_NO_FSYNC

• O_DIRECT used when flushing I/O, afterwards fsync() is skipped
• Cannot be used with XFS file systems.
• Not recommended over O_DIRECT in most use cases

5. ALL_O_DIRECT

• Only available with XtraDB
• O_DIRECT used to open data and log files
• fsync() flushes data files but not logs
• Only recommended with large InnoDB files, or may experience performance deterioratio

6. littlesync

• Used for internal performance testing and is currently unsupported. Not recommended.

7. nosync

• Used for internal performance testing and is currently unsupported. Not recommended.