innodb_flush_method

Tuning innodb_flush_method variable

Basic Details

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.

innodb_flush_method – Usage

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.

Selecting innodb_flush_method value for configuration

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.