Detailed example of innodb_flush_method method in mysql

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

In other words, specific The value depends on the hardware configuration and workload. It is best to conduct a stress test to determine. But generally speaking, in a Linux environment with raid

controller and write-back write strategy, o_direct is a better choice; if the storage medium is SAN, then it may be better to use the default fsync or osync.

Generally speaking, it seems that most people use the value o_direct, there is a raid card on the bottom layer, and the read and write policy is set to write-back. When using sysbench to stress test the oltp type, I found that o_direct is indeed better than fsync in performance. It seems to be suitable for most scenarios. However, I recently encountered such a SQL and the customer feedback was very slow. In the case of the same memory, it The cloud host I built performed much faster. Later, I found that the main reason was the huge performance difference caused by the different setting values ​​of innodb_flush_method.

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 result

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)

Comparison of results:

Both The execution plans are exactly the same, but the performance is very different. In the

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

index (account_id,outcome,income), so that the covering index scan can greatly reduce the response time

[Related recommendations]

1.

Mysql free video tutorial

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!