“This is the 9th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Overall mechanism of a Buffer Pool

A redo log must be written to log changes to the database. This log also ensures that dirty pages in the Buffer Pool are not flushed to disk after the transaction commits. If the MySQL database is down, redo the redo log, restore the cached pages, and flush the cached pages to disk. In this way, the redo log ensures that the data is not lost after the transaction is committed. The Buffer Pool query flow has been written in the following figure:

Here we have a question: why not directly write data back to disk during the update, wait until the disk data update is complete, the transaction is truly successful? Redo logs are also written to disks. MySQL does not update disk files directly, which is mainly related to the performance of updating disk files. Because cache pages are updated to disk files, it is random disk read and write, and the performance is poor. Moreover, each cache page is 16KB, and the amount of disk write is also large, so the database performance and concurrency are very weak. This is why we introduced the redo log mechanism, where a transaction is considered successful only after the redo log is written to the redo log file. Because the redo log is written to the file, the speed of disk writing is similar to that of memory writing. In this way, skills ensure that data is not lost, but also to ensure the processing capacity of the database.

Redo log Log format

The redo log records the change of several bytes to an offset on a page of a tablespace. The redo log records the change of several bytes to an offset on a page of a tablespace. ** Table space + data page number + offset + change a few bytes of value + specific value ** There are different types of redo log based on change a few values in the data page:

  • MLOG_1BYTE type: the value is changed by 1 byte
  • MLOG_2BYTE type: the value is modified by two bytes
  • And so on, there are log types that change the value of 4 bytes, log types that change the value of 8 bytes
  • If you change a large number of values at once, the type is MLOG_WRITE_STRING. For this log type, you need to know exactly how many bytes of data were changed, so there is an extra length of modified data

In this case, the log format is:

Log type (similar to MLOG_1BYTE) Tablespace ID Data page Number Offset in the data page Modified Data length Modified dataCopy the code

redo log block

Redo logs are not written to disk files. There is a redo log block that stores multiple single-line logs. A redo log block is 512 bytes and is divided into 3 parts: a 12-byte header body, a 496 byte body, and a 4-byte trailer tail

The 12-byte header is divided into four parts:

  • 4 bytes Block no: indicates the unique block number
  • 2 bytes Data Length: Indicates the number of bytes of data written to the block
  • First record group: each transaction has multiple redo logs. It is a redo log group. So the offsets of the first redo log in this block are stored in these two bytes;
  • 4 bytes checkpoint on

For redo logs, it is true that redo logs are continuously appended to redo log files, but each redo log is written to a redo log block, which contains a maximum of 496 bytes of redo log.

redo log buffer

When MySQL started, it allocated contiguous memory to the operating system. There were N empty redo log blocks, as shown in the following figure:

Mysql innodb_log_buffer_size specifies the size of the redo log buffer. The default value is 16MB. This value is relatively large because a redo log block is only 512KB, and each redo log can be anywhere from a few to dozens of bytes. Redo logs are written to the redo log block data structure in memory, and then the redo log block is written to the disk file. When a redo log is written, a redo log block is written, a redo log block is written, and so on, until all redo log blocks are full.

Redo log buffer Indicates the flush time of cache logs

When a transaction is committed, redo logs are written to the redo log buffer. When a transaction is committed, redo log blocks are written to the redo log buffer. Select blocks that have free space to write to, and then flush to disk when the block is full. Blocks can be flushed to disk files in the following ways:

  1. If the redo log buffer has been written to half of the redo log buffer (default: 8MB), it is flushed to disk
  2. When a transaction commits, redo log blocks must be flushed to disk so that the data changes are not lost after the transaction commits
  3. The redo log buffer is refreshed periodically. One redo log block is flushed to disk every second
  4. When MySQL is shut down, the redo log block is flushed to disk

Do you guarantee that when a transaction is executed, the redo log goes to the redo log buffert file, and the redo log must be flushed to disk before the transaction is committed successfully or not? Disk files also have an OS cache. There are three redo log flush policies: 0,1, and 2

Redo log files

Log files are constantly being written to the disk as redo logs are constantly being added, deleted, or changed during the runtime. The log files consume more and more disk space. This directory can be set with the innodb_log_group_home_dir parameter. You can limit the number of redo log files. Innodb_log_fifile_size specifies the size of each redo log file. The default size is 48MB. Innodb_log_files_in_group specifies the number of log files. The default is 2. By default, two log files ib_logfile0 and ib_logFILe1 are written in a loop