InnoDB Two-stage log submission mechanism

A few days ago, see the innodb_flush_log_at_trx_commit的官网解释时产生了一些疑问，关于 detailed explanation of the innodb_flush_log_at_trx_commit parameters in the official website:

Https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

one of the following is written: with a value of 2, the contents InnoDB of the log buffer is written to the log file after each transaction commit and the log file is flushed to disk Approximatel Y once per second. This means: if the value of Innodb_flush_log_at_trx_commit is set to 2, then the contents of log buffer will be written to log file at each commit. Then logfile will be flush to disk. since InnoDB's log file is ib_logfile on the hard disk, I'm wondering if the log file here is flush to disk, is there a layer between the log buffer and disk that can cache the log file structure? after consulting a large number of Chinese and English materials, finally have a preliminary understanding, tentatively summarized here. first, the noun explanationIn the InnoDB storage engine, there is a unique log file, the redo log file, so there are two Logfile:redo logs and binlog for the InnoDB storage engine. Redo log: IB_LOGFILE0 in the Data directory,Ib_logfile1 (the number is controlled by Innodb_log_files_in_group ), the InnoDB storage engine is unique and has a corresponding redo log buffer in memory. so the 3-layer structure when writing redo is: Redo Log Buffer---> file system cache Redo logfile >disk log file on the redo--- binlog: By default in the data directory, you can also specify the path directly through the Log_bin parameter, the file name is the default so the 2-layer structure when writing Binlog is: Binlog in the file system cache---binlog on >disk two or two-stage log write process Original from:https://jin-yang.github.io/post/mysql-group-commit.html when Binlog is turned on, if the session makes a commit request, then before committed, a series of processes are: 1.prepare Stage:writes the transaction change and transaction commit information for log buffer to the redo log file in the filesystem cache, noting the log buffer and undo buffer (also called undo Page) is generated on-the-fly during the execution of the transaction (undo defaults to the system tablespace, and you can specify a separate tablespace after 5.6), and the redo log in the file system cache is flush to disk, depending on the innodb_flush_log_at_ The Trx_commit parameter. innodb_flush_log_at_trx_commit:

• this A value of 0 indicates that the contents of the redo log buffer are written to the file system cache redo log every second and are flush (cured) to the Redo log file on disk.
  
• This value is 1: redo the contents of the log buffer are written to the file system cache redo log when the transaction commits, It is also flush (cured) to the Redo log file on disk.
  
• This value is 2: redo log The contents of buffer are written to the redo log of the file system cache while the transaction commits, while

2. Write Binlog stage:This phase calls two methods write () and Fsync (), which is responsible for writing Binlog in the file system cache, which is responsible for writing Binlog in the file system cache to bin log on disk, which is bound to be called at this stage, which is called by sync_ Binlog parameter control. about the sync_binlog parameter:

• Sync_binlog=0: The call to Fsync () is completely given to the operating system, that is, if the Binlog in the file system cache is flushed to disk completely controlled by the operating system.
  
• Sync_binlog=1: Indicates that binlog must be cured to disk when the transaction commits
  
• Sync_binlog=n (n>1): When a database crashes, N-1 transactions may be lost, as detailed in https://jin-yang.github.io/post/mysql-group-commit.html
  

3. Final Commit phase:This phase mainly includes: server tells the storage engine, both Binlog and redo log are written (at least at the filesystem cache level), submits the data according to the normal mechanism, and then returns the acknowledgment submission information to the session committed.   third, fault recovery analysis1. If it crashes after a phase, the data is obviously not committed due to binlog, which is a failed transaction and does not need to be rolled forward or rolled back. (The situation is more complex for Oracle databases, and some large transactions may have hardened data even if they are not committed, then rollback is required.) It's unclear if MySQL's big business has a mechanism to write uncommitted data to disk in advance. 2. If the crash occurs after phase two, there is only one situation where the data is guaranteed to be completely non-lost, namely:Innodb_flush_log_at_trx_commit and Sync_binlog are set to 1, at which point redo Both log and binlog are cured to disk, which guarantees that data not written after commit is rolled forward at recovery time. If either of these is not 1, then the Binlog and redo log inconsistencies can occur, and transactions are likely to be lost at this time. therefore, the Innodb_flush_log_at_trx_commit and Sync_binlog of the main library must be set to 1 to ensure that the master-slave is fully consistent.  The 3-stage group submission mechanism that occurs after 5.6 to resolve concurrent transaction commit exceptions remains to be studied.

InnoDB Two-stage log submission mechanism