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

This article mainly introduces how to use sync_binlog and innodb_flush_log_at_trx_commit in mysql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Sync_binlog:

The value defaults to 0 and the range is 0,4294967295 (mysql 5.6)

When 0 is 0, mysqld does not control disk synchronization, but waits for the operating system's fdatasync to flush from memory to disk. (synchronize with the operating system). In the replication structure, the binlog thread is transmitted during the flush phase.

The safest setting of 0 is 1. If autocommit is enabled, if mysqld or os crash is dropped at this time, at most one transaction will be lost. (it is also the slowest setting). In the replication structure, the binlog thread is transmitted during the sync phase.

0 when the value is other values in the range, such as 100, this means that mysql brushes the cache to disk when it writes binlog 100 times. In the replication structure, the binlog thread is transmitted during the flush phase.

Setting this value to a slightly higher value can increase tps, and you need to use battery-backed cache to prevent abnormal power outages.

Innodb_flush_log_at_trx_commit:

(control REDO log flushing strategy)

The default value is 1, and the other available values are 0 and 2.

When 0 is 1, each transaction commits, the contents of log buffer will be written to log file and will be flush to disk at the same time, which is also the safest way to do so. Can fully comply with ACID. (the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.)

When 0 is 0, each transaction commits, writes to mysqld's own log buffer, writes the contents of log buffer to log file every 1 second, and then flush to disk.

When 0 is 2, each transaction commits will be written to log file (OS cache), but log file will only be flush to disk every 1 second, when the operating system crash or power outage, the transaction will be lost at most 1 second.

The difference between 0 and 2 is

When 0, mysqld or operating system crash will cause transactions within 1 second to be lost.

When 2, when the operating system crash or power outage will cause transactions within 1 second to be lost.

In terms of performance (from fast to slow):

0 > 2 > 1

In terms of security (from good to bad):

1 > 2 > 0

Thank you for reading this article carefully. I hope the article "how to use sync_binlog and innodb_flush_log_at_trx_commit in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.