MySQL innodb_flush_method参数介绍-工具盒子

文章目录

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选项 {#title-0}

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()：
#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; }

|----------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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是针对特定文件的同步。

演示示例 {#title-1}

基于wlnmp源，安装MySQL8.4.3版本验证innodb_flush_method参数。

我这里通过sysbench工具，使用不同的底层磁盘架构，分别对fsync和O_DIRECT选项进行压测，该压测结果仅供参考！

通过sysbench工具，基于分布式存储架构进行测试

创建测试库
CREATE DATABASE test_db;

|---|--------------------------| | 1 | CREATE DATABASE test_db; |

创建测试表
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

|---|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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进行性能测试。
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 | 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 |

测试结果
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

|----------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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进行性能测试。

测试结果
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

|----------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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 |

{#tablepress-47-no-2}

事务处理能力：fsync 的事务处理能力 (470.62 TPS) 高于 O_DIRECT 的事务处理能力 (443.06 TPS)。这表明在使用fsync方法时，系统能够处理更多的事务。

查询处理能力：fsync的查询处理能力 (9412.43 QPS) 也高于 O_DIRECT (8861.10 QPS)，说明在查询性能上，fsync方法表现得更好。

通过sysbench工具，基于硬RAID5架构进行测试

创建测试库
CREATE DATABASE test_db;

|---|--------------------------| | 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 --threads=4 --time=60 run |

测试结果
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

|----------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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进行性能测试。

测试结果
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

|----------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 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 |

{#tablepress-46-no-2}

事务处理能力：fsync的事务处理能力 (43.57 TPS) 低于O_DIRECT的事务处理能力 (55.89 TPS)。这表明在使用O_DIRECT方法时，系统能够处理更多的事务。

查询处理能力：fsync的查询处理能力 (871.49 QPS) 低于O_DIRECT(1117.75 QPS)，说明在查询性能上，O_DIRECT方法表现得更好。

综上所述：吴昊这里仅做一个简单的演示，在实际生产数据库中，需要结合实际硬件情况，通过多种指标进行测试。