innodb_flush_method选项用于控制InnoDB存储引擎如何将数据刷新到磁盘。
本文基于MySQL8.4.3版本。MySQL8.4官方参考手册：https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_method
innodb_flush_method选项
fsync或0：使用fsync()系统调用确保数据被安全地写入磁盘，由于每次写入后都要进行fsync()，这可能导致性能下降，尤其是在高并发或大量写入操作时。
O_DSYNC或1：该选项在许多不同的Unix系统上都存在问题，不推荐。
littlesync或2：该选项用于内部性能测试，目前不受支持，使用风险自负。
nosync或3：该选项用于内部性能测试，目前不受支持，使用风险自负。
O_DIRECT或4：如果操作系统支持O_DIRECT，则使用此选项。允许应用程序绕过操作系统的文件系统缓存，直接与磁盘进行I/O操作。默认值如果支持，则为O_DIRECT，否则为fsync。如果在具有RAID控制器的缓存策略和电池备份策略的系统上，O_DIRECT可以帮助避免InnoDB缓冲池和操作系统文件系统缓存之间的双缓冲。
O_DIRECT_NO_FSYNC或5：该选项使用O_DIRECT进行直接I/O，但在写入后不调用fsync()。这意味着数据可能不会立即持久化到磁盘，增加了数据丢失的风险。
unbuffered（Windows选项）：Windows上的默认值。使用未缓冲的I/O操作，数据会直接写入磁盘，而不经过操作系统的缓存。这种方法可以提高数据的持久性，但可能会影响性能，因为每次写入都需要直接与磁盘进行交互。
normal（Windows选项）：允许使用操作系统的缓冲机制，数据首先被写入操作系统的缓存，然后在适当的时候写入磁盘。这种方法可以提高性能，因为它减少了每次写入操作的磁盘I/O，但在系统崩溃时可能会导致未写入的数据丢失。
所以综上所述，大多数场景下（不考虑Windows），fsync或O_DIRECT选项是最佳实践。
fsync()补充说明
fsync()是一个系统调用，用于将文件系统中的数据从内存缓冲区强制写入到磁盘。这个操作确保在调用fsync()之前对文件的所有修改都被持久化，也就是说，数据不会仅仅保留在内存中，而是被写入到物理存储设备上。
以下是详细解释
1、内存和磁盘：当你对文件进行写操作时，操作系统通常会将这些数据首先写入内存，以提高性能。这是因为写入内存的速度远快于写入磁盘。然而，内存中的数据在系统崩溃或电源故障时可能会丢失。因此，仅依赖内存是不安全的。
2、fsync() 的作用：fsync()的作用是通知操作系统将与指定文件相关的所有数据和元数据（如文件大小、最后修改时间等）从内存写入到磁盘。通过调用fsync()，程序可以确保在此之后，文件的内容在磁盘上是安全的，即使系统崩溃也不会丢失这些数据。
3、使用场景：fsync()通常用于数据库、文件系统和其他需要高数据可靠性的应用中。例如，在数据库中，确保每次事务提交后数据都已安全写入磁盘是非常重要的。但是在一些情况下，使用fsync()可能会导致性能下降，因为写入磁盘的速度比写入内存要慢得多。
示例
以下是一个简单的示例，展示如何在C语言中使用fsync()：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
#include <fcntl.h> #include <unistd.h> #include <stdio.h> int main() { int fd = open("example.txt", O_WRONLY | O_CREAT, 0644); if (fd == -1) { perror("open"); return 1; } const char *data = "Hello, World!"; write(fd, data, 13); // 写入数据 // 确保数据写入磁盘 if (fsync(fd) == -1) { perror("fsync"); close(fd); return 1; } close(fd); return 0; } |
在这个示例中，数据被写入到文件example.txt中，随后调用fsync(fd)确保数据被写入到磁盘。
更简单一点理解，相当于在Linux上先执行echo "Hello, World!" > example.txt，然后通过sync命令将数据同步磁盘，只不过sync是针对整个文件系统的同步，fsync是针对特定文件的同步。
演示示例
基于wlnmp源，安装MySQL8.4.3版本验证innodb_flush_method参数。
我这里通过sysbench工具，使用不同的底层磁盘架构，分别对fsync和O_DIRECT选项进行压测，该压测结果仅供参考！
通过sysbench工具，基于分布式存储架构进行测试
创建测试库
|
1 |
CREATE DATABASE test_db; |
创建测试表
|
1 |
sysbench --db-driver=mysql --mysql-host=localhost --mysql-user=root --mysql-password=whsir --mysql-db=test_db --mysql-socket=/tmp/mysql.sock oltp_read_write --tables=1 --table-size=100000 prepare |
将innodb_flush_method设置为fsync，使用sysbench进行性能测试。
|
1 |
sysbench --db-driver=mysql --mysql-host=localhost --mysql-user=root --mysql-password=whsir --mysql-db=test_db --mysql-socket=/tmp/mysql.sock oltp_read_write --tables=1 --table-size=100000 --threads=4 --time=60 run |
测试结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 4 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 395388 write: 112968 other: 56484 total: 564840 transactions: 28242 (470.62 per sec.) queries: 564840 (9412.43 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0083s total number of events: 28242 Latency (ms): min: 3.57 avg: 8.50 max: 102.70 95th percentile: 17.63 sum: 239940.75 Threads fairness: events (avg/stddev): 7060.5000/16.59 execution time (avg/stddev): 59.9852/0.00 |
修改innodb_flush_method为O_DIRECT，重启MySQL，再次使用sysbench进行性能测试。
测试结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 4 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 372218 write: 106348 other: 53174 total: 531740 transactions: 26587 (443.06 per sec.) queries: 531740 (8861.10 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0067s total number of events: 26587 Latency (ms): min: 3.56 avg: 9.02 max: 406.89 95th percentile: 21.50 sum: 239940.16 Threads fairness: events (avg/stddev): 6646.7500/9.58 execution time (avg/stddev): 59.9850/0.00 |
结果对比
| fsync 方法 | O_DIRECT 方法 |
|---|---|
| 事务数: 28242 (470.62 每秒) | 事务数: 26587 (443.06 每秒) |
| 查询总数: 564840 (9412.43 每秒) | 查询总数: 531740 (8861.10 每秒) |
| 写入查询数: 112968 | 写入查询数: 106348 |
| 读取查询数: 395388 | 读取查询数: 372218 |
| 最小延迟: 3.57 ms | 最小延迟: 3.56 ms |
| 平均延迟: 8.50 ms | 平均延迟: 9.02 ms |
| 最大延迟: 102.70 ms | 最大延迟: 406.89 ms |
| 95th 百分位延迟: 17.63 ms | 95th 百分位延迟: 21.50 ms |
事务处理能力：fsync 的事务处理能力 (470.62 TPS) 高于 O_DIRECT 的事务处理能力 (443.06 TPS)。这表明在使用fsync方法时，系统能够处理更多的事务。
查询处理能力：fsync的查询处理能力 (9412.43 QPS) 也高于 O_DIRECT (8861.10 QPS)，说明在查询性能上，fsync方法表现得更好。
通过sysbench工具，基于硬RAID5架构进行测试
创建测试库
|
1 |
CREATE DATABASE test_db; |
创建测试表
|
1 |
sysbench --db-driver=mysql --mysql-host=localhost --mysql-user=root --mysql-password=whsir --mysql-db=test_db --mysql-socket=/tmp/mysql.sock oltp_read_write --tables=1 --table-size=100000 prepare |
将innodb_flush_method设置为fsync，使用sysbench进行性能测试。
|
1 |
sysbench --db-driver=mysql --mysql-host=localhost --mysql-user=root --mysql-password=whsir --mysql-db=test_db --mysql-socket=/tmp/mysql.sock oltp_read_write --tables=1 --table-size=100000 --threads=4 --time=60 run |
测试结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 4 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 36680 write: 10480 other: 5240 total: 52400 transactions: 2620 (43.57 per sec.) queries: 52400 (871.49 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.1225s total number of events: 2620 Latency (ms): min: 3.39 avg: 91.76 max: 612.75 95th percentile: 240.02 sum: 240415.79 Threads fairness: events (avg/stddev): 655.0000/5.83 execution time (avg/stddev): 60.1039/0.02 |
修改innodb_flush_method为O_DIRECT，重启MySQL，再次使用sysbench进行性能测试。
测试结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 4 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 47026 write: 13436 other: 6718 total: 67180 transactions: 3359 (55.89 per sec.) queries: 67180 (1117.75 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0985s total number of events: 3359 Latency (ms): min: 2.68 avg: 71.54 max: 1057.64 95th percentile: 215.44 sum: 240290.52 Threads fairness: events (avg/stddev): 839.7500/7.08 execution time (avg/stddev): 60.0726/0.03 |
结果对比
| fsync 方法 | O_DIRECT 方法 |
|---|---|
| 事务数: 2620 (43.57 每秒) | 事务数: 3359 (55.89 每秒) |
| 查询总数: 52400 (871.49 每秒) | 查询总数: 67180 (1117.75 每秒) |
| 写入查询数: 10480 | 写入查询数: 13436 |
| 读取查询数: 36680 | 读取查询数: 47026 |
| 最小延迟: 3.39 ms | 最小延迟: 2.68 ms |
| 平均延迟: 91.76 ms | 平均延迟: 71.54 ms |
| 最大延迟: 612.75 ms | 最大延迟: 1057.64 ms |
| 95th 百分位延迟: 240.02 ms | 95th 百分位延迟: 215.44 ms |
事务处理能力：fsync的事务处理能力 (43.57 TPS) 低于O_DIRECT的事务处理能力 (55.89 TPS)。这表明在使用O_DIRECT方法时，系统能够处理更多的事务。
查询处理能力：fsync的查询处理能力 (871.49 QPS) 低于O_DIRECT(1117.75 QPS)，说明在查询性能上，O_DIRECT方法表现得更好。
综上所述：吴昊这里仅做一个简单的演示，在实际生产数据库中，需要结合实际硬件情况，通过多种指标进行测试。
注意事项
1、只读参数：innodb_flush_method的选项是只读的，不能动态临时修改，必须在配置文件中配置，配置完成后，重启数据库生效。
2、硬件性能：不同的存储设备、存储架构，会对不同的innodb_flush_method设置表现出不同的性能，应结合实际情况进行配置。
3、测试和监控：在更改innodb_flush_method后，务必进行充分的测试和监控，以评估其对系统性能和数据完整性的影响。
4、参数选项：结合业务场景以及性能压测，理应优先选择fsync或O_DIRECT选项。
总结
选择合适的innodb_flush_method值需要根据具体的工作负载、存储硬件以及对性能和数据安全性的要求来决定。在进行更改时，建议在测试环境中验证性能和数据一致性，以确保选择的配置最符合你的需求。
附，MySQL my.cnf配置文件生成器：https://dbcnf.wlnmp.com/
原文链接：MySQL innodb_flush_method参数介绍，转载请注明来源！
