search
HomeDatabaseMysql TutorialDetailed 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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How to Grant Permissions to New MySQL UsersHow to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideHow to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

MySQL BLOB : are there any limits?MySQL BLOB : are there any limits?May 08, 2025 am 12:22 AM

MySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst

MySQL : What are the best tools to automate users creation?MySQL : What are the best tools to automate users creation?May 08, 2025 am 12:22 AM

The best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.

MySQL: Can I search inside a blob?MySQL: Can I search inside a blob?May 08, 2025 am 12:20 AM

Yes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment