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

This article mainly explains "the complete process analysis of a transaction in the mysql innodb storage engine". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the complete process analysis of a transaction in the mysql innodb storage engine".

First, let's talk about innodb's transaction log concept:

The ib_logfile file is the transaction log of innodb, which can be understood as the REDO log of INNODB. When the database is shut down abnormally, mysql under the innodb storage engine uses the transaction log to complete instance recovery, that is, roll forward and rollback to ensure database consistency.

Different from binlog log, which is also called binary log file, it records all Query that modify database data in mysql into log file in binary form, such as create,insert,drop,update, etc. (for select operations will not be recorded in binlog, because it does not modify the data in the database), binlog is mainly used to ensure data integrity, such as master-slave backup, read the operation Query from the binlog file to do the same on the salt machine, to ensure master-slave synchronization, but also can be used as a tool to restore data.

Innodb also has another log Undo log, but the Undo log is stored in a shared tablespace (ibdata* file, which stores the check point log serial number).

InnoDB log buffer (InnoDB Log Buffer): this is the buffer used by the transaction logs of the InnoDB storage engine. Similar to when Binlog Buffer,InnoDB writes transaction logs, in order to improve performance, the information is first written to Innofb Log Buffer, and the log will not be written to a file (or synchronized to disk) until the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full). You can set the maximum memory space it can use through the innodb_log_buffer_size parameter

The following focuses on the innodb_flush_log_trx_commit parameter: the following figure clearly shows the different processes of log refresh when the parameter is set to different values.

For this diagram, the first arrow represents where the transaction log arrives each time commit, and then the second arrow represents the process of flushing to disk permanent storage. The following fsync every commit, fsync every second, and fsync every second describe the conditions of the second arrow refresh, respectively.

Then there is another thing to note: macroscopically, writing to logfile is to write to disk. But microscopically, writing logfile is first written into os cahce, then refreshed to raid cache (if raid is done) and finally to disk.

Specific analysis of the significance of innodb_flush_log_at_trx_commit=N:

Innodb_flush_log_at_trx_commit=0, every time you commit, the transaction log is written to innodb log buffer, and then every second Log Thread flushes the transaction log from innodb log buffer to ib_ogfile (that is, to disk). When innodb_flush_log_at_trx_commit is set to 0memmysqld process, the crash will result in the loss of all transaction data in the previous second. This is because every time commit, the transaction log is only written into innodb log buffer, and then the transaction log in innodb log buffer is flushed to disk for permanent storage every second. So when the mysqld process crashes, innodb log buffer may not refresh the log for one second, but in this case, MySQL performance is the best.

Innodb_flush_log_at_trx_commit=2, every time commit, the transaction log is written to innodb log buffer and then to os cache, that is, every time commit, the transaction log is written to os cache, and then flushed from os cache to ib_logfile (that is, flushed to disk) every second. When innodb_flush_log_at_trx_commit is set to 2, only if the operating system crashes or the system is powered off, all transaction data can be lost in the last second. Because every time commit, the transaction log has already entered the os cache, so mysqld crashes and the transaction log will not be lost.

Innodb_flush_log_at_trx_commit is set to 1, which is the safest setting. At the same time, due to frequent io operations, the efficiency is the lowest. At this time, no matter it is mysqld or the operating system crashes, the data will not be lost. This is because every time commit, the transaction log is flushed to disk for permanent storage.

Principles of selection:

For some applications that do not require high data consistency and integrity, a configuration of 2 is sufficient; for maximum performance, it can be set to 0. Some applications, such as payment services, require high consistency and integrity, so even the slowest, it is best set to 1.

Then introduce the parameter sync_binlog:

Sync_binlog = N: the control is to refresh the binlog from binlog buffer to the underlying binlog file (that is, to the underlying disk)

N > 0 every time N SQL or N transactions are written to the binary log file, the data of the binary log file is flushed to disk

Number0 does not actively refresh the data of binary log files to disk, but is determined by the operating system.

Recommended configuration combination:

1) innodb_flush_log_at_trx_commit=1 and sync_binlog = 1

This is the so-called double one setting: this configuration is suitable for very high data security requirements, and the disk IO write capacity is sufficient to support business, such as recharge consumption system, banking business.

2) innodb_flush_log_at_trx_commit=1 and sync_binlog = 0

This setting ensures that the transaction log is complete, which ensures that the instance can be restored, that is, roll forward and rollback. It is suitable for high data security requirements, and the disk IO writing capacity is not very redundant.

3) innodb_flush_log_at_trx_commit=2 or 0 and sync_binlog = 2 or m (0)

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.