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

How to understand innodb_flush_log_at_trx_commit parameters, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

When this parameter is set to 0, the log buffer is written to the log file once a second, and the log file is refreshed to disk, but nothing is done in a transaction commit. When this value is 1 (the default), when each transaction commits, the log buffer is written to the log file and the log file is refreshed to disk.

When set to 2, at each commit, the log buffer is written to the file, but the log file is not refreshed to disk. MySQL occurs every second when the log file is refreshed. We must note that because of scheduling problems, a refresh per second is not 100% guaranteed to occur every second. You can get better performance by setting this value not to 1

But then you lose 1/2 worth of business in a crash. If you set this value to 0, the crash of any mysqld process will delete the last second transaction before the crash, if we set this value to 2, then only the operating system crash or power outage will delete the last second transaction.

However, the crash recovery of InnoDB is not affected, and this value is not taken into account because this crash recovery starts to work. Note that many operating systems and some disk hardware can spoof flushing to disk operations. Although the refresh has not taken place, you can tell mysqld that the refresh has already taken place.

Even if this value is set to 1, the persistence of the transaction is not guaranteed, and in the worst case, a power failure can even destroy the InnoDB database. In the SCSI disk controller, or in the disk itself, using a disk cache with a backup battery speeds up file refresh and makes the operation more secure. You can also try using the Unix command hdparm to disable disk write caching in the hardware cache

Or use other commands specific to the hardware provider. The default value for this option is 1.

This parameter is simply summarized as follows:

Parameter innodb_flush_log_at_trx_commit:

= 0: write os cache & flush disk per second

= 1: write os cache & flush disk every time commit

= 2: write os cache every commit, and then flush disk according to the innodb_flush_log_at_timeout parameter (default is 1s)

After reading the above, have you mastered how to understand the innodb_flush_log_at_trx_commit parameters? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.