Shulou(Shulou.com)05/31 Report--

How to understand binlog and innodb_flush_log_at_trx_commit in MySQL, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, people who have this need can learn, I hope you can gain something.

innodb_flush_log_at_trx_commit and sync_binlog are key parameters that control MySQL disk write strategy and data security. This paper expounds the influence of two parameters with different values on db performance and data from the point of view of parameter meaning, performance and safety.

Meaning of a parameter

innodb_flush_log_at_trx_commit

If innodb_flush_log_at_trx_commit is set to 0, log buffer will be written to log file once per second, and flush of log file will occur simultaneously. In this mode, writes to disk are not actively triggered when a transaction commits.

If innodb_flush_log_at_trx_commit is set to 1, MySQL writes the log buffer data to log file and flushes it to disk every time a transaction commits.

If innodb_flush_log_at_trx_commit is set to 2, MySQL writes the log buffer data to log file every time a transaction commits. However, flush operations do not occur simultaneously. In this mode MySQL flushes once per second.

Note:

Due to scheduling policy issues, this "flush once per second" is not guaranteed to be 100%"per second."

sync_binlog

The default value of sync_binlog is 0. MySQL does not synchronize to disk but relies on the operating system to refresh binary log, just like the operating system does for other files.

When sync_binlog =N (N>0), MySQL synchronizes its binary log writes to disk using the fdatasyc () function every N binary log writes.

Note:

If autocommit is enabled, there is one write per statement; otherwise, there is one write per transaction.

Based on the above description, I made a picture that can be easily viewed by everyone.

second performance

The effect of the two parameters on pure writes to db at different values is as follows:

Test Scenario 1

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

Test Scenario 2

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

Test Scenario 3

innodb_flush_log_at_trx_commit=1

sync_binlog=1

Test Scenario 4

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

Test Scenario 5

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

Scenario TPS Scenario 141000 Scenario 233000 Scenario 326000 Scenario 433000

Thus, when the two parameters are set to double 1, write performance is the worst. When sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2, MySQL's write operation can achieve the highest performance (in the current mode).

third security

It is safest when innodb_flush_log_at_trx_commit and sync_binlog are both 1, binary log can only lose at most one statement or transaction in case of mysqld service crash or server host crash. But you can't have it both ways. Double 11 causes frequent io operations, so this mode is also the slowest way.

When innodb_flush_log_at_trx_commit is set to 0, a crash of the mysqld process results in the loss of all transaction data for the last second.

When innodb_flush_log_at_trx_commit is set to 2, all transaction data can only be lost in the last second if the operating system crashes or the system loses power.

Double 1 is suitable for data security requirements are very high, and disk IO write capacity is enough to support business, such as orders, transactions, top-up, payment consumption system. In double 1 mode, when disk IO can not meet business requirements, such as 11.11 active pressure. The recommended practice is innodb_flush_log_at_trx_commit=2, sync_binlog=N (N is 500 or 1000) and use a cache with battery backup to prevent abnormal system power outages.

System performance and data security are essential factors for high availability and stability of business systems. We need to find a balance point for system optimization. The right one is the best. According to different business scenario requirements, we can adjust the two parameters in combination to optimize the performance of db system.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.