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

This article mainly introduces the example analysis of innodb_flush_log_at_trx_commit parameters in mysql, which is very detailed and has certain reference value. Friends who are interested must finish it!

I. Parameter interpretation

0:log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will take place at the same time. In this mode, writing to disk is not actively triggered when the transaction commits.

1: every time a transaction commits, MySQL will write the data of log buffer to log file and flush (swipe to disk). This mode is the system default.

2: MySQL writes log buffer data to log file each time the transaction is committed, but the flush (flush to disk) operation does not occur at the same time. In this mode, MySQL performs a flush (flush to disk) operation once a second.

So from the above definition, there are two main points of innodb_flush_log_at_trx_commit.

1. Must be a transaction commit to trigger

two。 The function is to affect the way data is brushed to disk.

II. Parameter modification

Locate the mysql configuration file mysql.ini, modify it to the appropriate value, and restart mysql.

Matters needing attention

When set to 0, this mode is the fastest but less secure, and the crash of the mysqld process results in the loss of all transaction data in the previous second.

When set to 1, this mode is the safest, but also the slowest. In the case of a mysqld service crash or a server host crash, binary log can only lose at most one statement or transaction.

When set to 2, this mode is faster and safer than 0, and all transaction data can be lost in the last second only if the operating system crashes or the system is powered off.

When choosing 2, because when the transaction commits, it only says that the transaction data is brushed to the log log and not flushed to disk, so the database crash has no effect because it has been written to the file system. Only when the whole system crashes does it matter.

The above is all the contents of the article "sample Analysis of innodb_flush_log_at_trx_commit parameters in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.