MySQL innodb_flush_method/innodb_use_fdatasync

结论

8.0.26+ 配置 innodb_flush_method=O_DIRECT，innodb_use_fdatasync=ON
8.0.26- 配置 innodb_flush_method=O_DIRECT

环境

host1 Percona Server 8.0.26-16
host2 Percona Server 8.0.25-15
host3 sysbench 1.0.20

EL 7.6 4.14.35
ssd & ext4

sysbench:

--tables=30 --table_size=10000000 --time=600 --threads=64 --report-interval=10 /usr/share/sysbench/oltp_write_only.lua

MySQL:

innodb_buffer_pool_size                         = 8192M
innodb_buffer_pool_dump_at_shutdown             = OFF
innodb_buffer_pool_load_at_startup              = OFF
innodb_data_file_path                           = ibdata1:200M:autoextend
innodb_fast_shutdown                            = 0
innodb_io_capacity                              = 3000
innodb_io_capacity_max                          = 6000
innodb_log_buffer_size                          = 64M
innodb_log_file_size                            = 2000M
innodb_log_files_in_group                       = 3
innodb_page_cleaners                            = 8
innodb_read_io_threads                          = 16
innodb_rollback_on_timeout                      = ON
innodb_write_io_threads                         = 16
disable_log_bin

8.0.26
                         O_DIRECT+fdatasync   O_DIRECT+fsync   O_DIRECT_NO_FSYNC+fdatasync   O_DIRECT_NO_FSYNC+fsync   fsync+fdatasync   fsync+fsync
                         ------------------   --------------   ---------------------------   -----------------------   ---------------   -----------
innodb_flush_method    = O_DIRECT             O_DIRECT         O_DIRECT_NO_FSYNC             O_DIRECT_NO_FSYNC         fsync             fsync
innodb_use_fdatasync   = ON                   OFF              ON                            OFF                       ON                OFF

8.0.25
innodb_flush_method    = O_DIRECT/O_DIRECT_NO_FSYNC/fsync

测试结果

8.0.26

TPS1

TPS2

8.0.25

TPS1

TPS2

参考

• innodb_flush_method
• innodb_use_fdatasync
• 8.5.8 Optimizing InnoDB Disk I/O
• WL#14452: InnoDB: Add configuration variable to switch between fdatasync() and fsync()
• Under what conditions is it safe to use innodb_flush_method = O_DIRECT_NO_FSYNC? 2021-08-25
• 使用O_DIRECT_NO_FSYNC来提升MySQL性能 2020-04-22

.eof.