Innodb_flush_method (example) and innodbflushmethod

Innodb_flush_method (example) and innodbflushmethod

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.

Mysql official documentation recommends how to set the value.

How each settings affects performance depends on hardware configuration and workload. Benchmarkyour 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 foreach 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_DIRECTcan help to avoid double buffering between the InnoDB buffer pool and the operating system's filesystem cache. On some systems where InnoDB data and log files are located on a SAN, the defaultvalue or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Alwaystest this parameter with hardware and workload that reflect your production environment

That is to say, the specific value is related to hardware configuration and workload. It is best to perform a stress test. However, in linux, The raid Controller and write-back write policy are available. o_direct is a good choice. If the storage medium is SAN, it may be better to use the default fsync or osync.

Generally speaking, it seems that most people have o_direct values, the underlying layer has a RAID card, and the read/write policy is set to write-back. When using sysbench to test the oltp type, I found that o_direct is indeed better than fsync. It seems to be suitable for most scenarios, but recently I encountered such an SQL statement, and the customer feedback is very slow, in the case of the same memory, the self-built VM instance runs much faster. Later I found that the major difference is the huge performance difference caused by the different setting values of innodb_flush_method.

Test scenario 1

Innodb_flush_method is the default value, that is, fsync, cache pool 512 M, table data volume 1.2 GB, excluding the impact of cache pool and stable results

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

Change innodb_flush_method to o_direct to exclude the impact of the cache pool. Stable results

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

Result comparison:

The execution plans of the two are the same, but the performance is very different. InDatabaseThe query results at the first startup are also very different, and o_direct is also much worse (TestResult omitted ). I don't quite understand why. In this case, there is another layer.Operating SystemCache, reading efficiency is much higher, the production environment settings must be subject to the pressure test results, the actual results prevail, do not blindly trust experience.

Improvement Measures:

If innodb_flush_method is not changed, this SQL statement can be further optimized by adding a composite index (account_id, outcome, income) to overwrite the index scan, greatly reducing the response time.

The above innodb_flush_method Value Method (for example) is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's house.