Shulou(Shulou.com)06/01 Report--

Configuration item description

The document is clearly written:

0

If the value of innodb_flush_log_at_trx_commit is 0, log buffer will be written to disk every second, and nothing will be done when the transaction is committed.

one

When set to the default value of 1, log buffer is brushed to the log each time the transaction is committed.

two

If set to 2, each commit transaction will be logged, but no brush operation will be performed. The log file is brushed at the same time every second. It is important to note that there is no guarantee that 100% will be flushed to disk every second, depending on the scheduling of the process.

The default value of 1 is to ensure a complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but you will lose 1 second of data in the event of a system crash. If set to 0, the last second transaction will be lost when the mysqld process crashes. Set to 2, the last second of data will be lost only in the event of an operating system crash or power outage. InnoDB ignores this value when doing a restore.

The concept of brushing and writing

Brushing is actually two operations, flush and write, and it is important to distinguish between these two concepts (two system calls). In most operating systems, the log buffer (memory) of Innodb is written to the log (calling the system call write), simply moving the data to the operating system cache, which also refers to memory. There is no actual persistence data.

So, usually when set to 0 and 2, the last second of data will be lost in the event of a crash or power outage, because at this time the data only exists in the operating system cache. The reason for saying "usually" is that more than 1 second of data may be lost, such as blocking when performing a flush operation.

Summary

A setting of 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If you do not have high requirements for data consistency and integrity, you can set it to 2, and if you only want the most performance, such as a log server with high concurrent writes, set to 0 to get higher performance.

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.