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

This article mainly introduces how to interpret the working principle of MySQL InnoDB engine log, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

When you update the data using UPDATE, INSERT, and DELETE statements, you change the data in two places: log buffer and data buffers. A Buffers is a fixed-length block of memory, usually 512 bytes.

LOG BUFFER DATA BUFFER

= =

= Log Record # 1 = = Page Header =

= Log Record # 2 = = Data Row =

= Log Record # 3 = = Data Row =

= Log Record # 4 = = Data Row =

= = for example: after the INSERT INTO JOBS VALUES statement is executed, log buffer will add a new log record called Log Record # 5, which contains a rowid and the contents of the new record. At the same time, data buffer will add a new line, but it will also be identified in the header: the latest log record on the page is Log Record # 5. In this example # 5 is Log Sequence Number (LSN), which is critical to the timing of the next operation.

[@ more@]

Here are some details of data-change:

1. An INSERT log record contains only one new data, which is sufficient for redoing operations on the page, so it is called a redo entry.

2. LSN is not a field recorded by log, it is the relative offset of an absolute address in the file.

After InnoDB changes log buffer and data buffer, the next step is to write the disk. This is the complication. There are multiple threads monitoring buffer activity, and there are three situations-overflow, checkpoint, and commit---that can lead to write operations.

What happened in the case of Overflows?

Overflow is a rare occurrence because InnoDB uses pro-active measures to prevent buffers from being filled. But let's take a look at the following two situations:

1. If the log buffer is full, InnoDBInnoDB writes log at the end of the buffer. Then the situation is the same as the following figure (log buffer has only four records, now insert the fifth record):

LOG FILE (S) BEFORE WRITING LOG RECORD # 5

=

= Log Record # 1 =

= Log Record # 2 =

= Log Record # 3 =

= Log Record # 4 =

=

LOG FILE (S) AFTER WRITING LOG RECORD # 5

=

= Log Record # 5 =

= Log Record # 2 =

= Log Record # 3 =

= Log Record # 4 =

= logs cannot grow forever. Even if InnoDB uses some compression algorithms, the log file is still too large to be placed on any disk drive. So InnoDB takes the approach of circular writing, that is, it will overwrite the previous log record.

two。 If the data buffer is full, InnoDB writes the recently used buffer to the database, but not fast enough. In this case, the LSN in the header works. First, InnoDB checks to see if its LSN is larger than the LSN recorded by the most recent log in the log file, and writes the data to disk only if log catches up with data. In other words, the data page will not write the disk until the corresponding log record needs to be written to the disk. This is the first logging strategy.

What happened during the CheckPoints?

As mentioned earlier, InnoDB has taken some pro-active measures to ensure that overflows does not occur, the most important of which is checkpointing. There is a separate thread, or a thread separated from a set of threads that modify the buffers. At certain intervals, checkpointer will wake up, check for buffer changes, and make sure that the write operation has taken place.

At this time, most DBMS will write all the buffer to disk, which ensures that all buffer that has changed but not written disk will write disk. That is to say, DBMS will flush all "dirty" buffers through "Sharp Checkpoint". But InnoDB only guarantees that: (a) log and data buffers will not exceed a certain limit point; (b) log always writes the disk before data; and (c) no data buffer has a header LSN equal to the overwritten log record. In other words, InnoDB is Fuzzy Checkpoint.

During COMMIT, InnoDB does not write dirty data page to disk. This is emphasized because it is easy to think that to submit a change is to write everything on a lasting medium. In fact, only log records need to be written. Writing dirty data page can only happen at overflow or checkpoint moments because their content is superfluous.

Recovery

It is necessary to see log in recovery: when an exception occurs in the database, the data can be recovered.

For exceptions that are not damaged disk drives, recovery occurs automatically. InnoDB reads the latest checkpoint log records to check that dirty pages was written to disk before the exception occurred, and if not, reads the log records that affect the page and applies them. This is called "rolling forward". Because there is LSN, InnoDB only needs to compare this number to synchronize.

Thank you for reading this article carefully. I hope the article "how to interpret the working principle of MySQL's InnoDB engine log" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.