The following editor will bring you an article on the innodb_flush_method value method (explanation with examples). The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let’s follow the editor and take a look.
Several typical values of innodb_flush_method
##
fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting. O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix. O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions,FreeBSD, and Solaris.
How to get the value, mysqlThe official document recommends this
How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment
Test scenario 1
innodb_flush_method is the default value, which is fsync,cachepool 512M, table data volume 1.2G , excluding the impact of the cache pool, the stable result
mysql> show variables like '%innodb_flush_me%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_flush_method | | +---------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +--------------------------+ | SUM(outcome)-SUM(income) | +--------------------------+ | -191010.51 | +--------------------------+ 1 row in set (1.22 sec) mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +--------------------------+ | SUM(outcome)-SUM(income) | +--------------------------+ | -191010.51 | +--------------------------+ 1 row in set (1.22 sec) mysql> explain SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | journal | ref | account_id | account_id | 62 | const | 161638 | Using index condition | +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ 1 row in set (0.03 sec)
Test scenario 2
innodb_flush_method is changed to o_direct, excluding the impact of the cache pool, the stable resultmysql> show variables like '%innodb_flush_me%'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | innodb_flush_method | O_DIRECT | +---------------------+----------+ 1 row in set (0.00 sec) mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +--------------------------+ | SUM(outcome)-SUM(income) | +--------------------------+ | -191010.51 | +--------------------------+ 1 row in set (3.22 sec) mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +--------------------------+ | SUM(outcome)-SUM(income) | +--------------------------+ | -191010.51 | +--------------------------+ 1 row in set (3.02 sec) mysql> explain SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main'; +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | journal | ref | account_id | account_id | 62 | const | 161638 | Using index condition | +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ 1 row in set (0.00 sec)
Comparison of results:
Both The execution plans are exactly the same, but the performance is very different. In thedatabasequery results are also very different when the database is first started, and o_direct is also very different (testThe result is omitted). I don’t quite understand why in this case, with an extra layer of operating system cache, the reading efficiency is much higher. The production environment settings must be based on the stress test results. In fact, The effect will prevail and the experience value cannot be blindly trusted.
Improvement measures:
Without changing the innodb_flush_method, this sql can actually be further optimized by adding a combinedindex (account_id,outcome,income), so that the covering index scan can greatly reduce the response time
[Related recommendations]1. 2.Detailed examples of adding new user permissions in MySQL
3.Detailed examples of changing passwords and access restrictions in MySQL
4.Details of examples of using regular expressions to replace content in the database Solution
5.Detailed explanation of examples of php storing images in mysql
The above is the detailed content of Detailed example of innodb_flush_method method in mysql. For more information, please follow other related articles on the PHP Chinese website!