An introductory

Why do I need redo logs? What are the redo log types? What is a redo buffer? What is LSN? What is checkpoint? By the end of this article, you will know the context of these concepts.

• To speed up data retrieval and modification, MySQL borrows some memory from the operating system to interact with disk data. This portion of memory is called the Buffer Pool.

• As the program runs and changes are made to pages in memory, do we immediately synchronize the changes to disk? No, that’s 1, slow and 2, inefficient. A page might only change one byte, but if you think about it, a page is 16kb. The real situation is to keep the changes in memory first and update the free, Flush, LRU list, etc. If something happens, like a power outage, the memory is gone, not updated to disk, and the changes we made are gone. To do that?

Justice from Heaven - Redo Log

• redoLogs are used to record changes in memory for easy restart after downtime.

The redo log records all changes made to the database during a transaction, which can be restored after a restart in the event of a system crash.

The redo log records all changes made to the database during a transaction, and can be restored if the system restarts after a crash.

• redoVarious types of logs

1. For example, only one offset on one page is recorded1Bytes ofredoLog type
2. For example, only one offset on one page is recorded2Bytes ofredoLog type
3. redoLogs can also be used in combination, such as deleting records in a range, using two logs, marking the beginning and end of the deletion.

Log grouping -MTR

• redoLogs need to be grouped, and the logs in each group constitute an atomic change and are an integral part of it.

The reasons for grouping redo logs:

Insert a record, for example, when a pessimistic insert time, namely the target page space is not enough, need paging, then the original part of the page data is copied to the new page, and then insert the bank records, then insert a node within the above directory records (if the directory record is not enough, need recursive insert up), then still have to update the pages in the system, For example, you can modify the statistics of various segments and extents, modify the linked list information in the Buffer Pool, and so on

The following example inserts a record with index column 10

Optimistic insert

Pessimistic insert

Following up on the topic above, if my redo log records only paging operations and does not record the insertion of a directory entry into the inner node, the B+ tree recovered from this log is an incomplete tree, which is unacceptable. So redo logs must also be atomic, which is grouping.

• How to group? In the same groupredoThe log is followed by a sentinel log to indicate that this is a complete operation.

• Do all redo logs have to be grouped, even if only one log is requiredredoLog operations? Of course not,redoLog headertypeOne byte represents the category of redo log, but the following 7 bits are actually sufficientredoLog type so can be in the first onebitIs it kind of likeASCIIThe character encoding that was later generated used the first onebitCharacter encoding to work with. The final provisions are:

If the first bit of the type field is 1, this indicates that the atomicity operation generated a single redo log. Otherwise, this indicates that the atomicity operation generated a series of redo logs.

• Mini-Transaction.MTR: indicates that an atomic access to the underlying page is called aMTR.

To summarize so far:

A transaction can contain several statements, and each statement can contain several statementsMTR, each oneMTRAnd you can have severalredoThe log

Redo log Buffer – Memory

• log bufferThe basic unit of:log buffer block, the size of512Bytes.

The reallogStored in thelog block bodythe496In bytes.Pay attention tolog blockWith four bytes in the headercheckpointInformation, that's the next point

After all, redo logs are also written to disk and files. Is there any problem with speed? So again, let’s do a cache.

• withbuffer poolIn a similar way,redo logThere is also a buffer memory calledlog buffer. amtrThe corresponding log group is first added tolog buffer, and flush to disk.
• Sequential writesredo log bufferIn, write one after another.

• In order to knowredoLog writeslog bufferThe specific location in, provides system variablesbuf_free
• Not log by log write tolog bufferMiddle, but onemtramtrIs written tolog bufferIn the.
• Different transactions are generatedmtrI can cross over

• log bufferThe timing of the flush to disk

1. log bufferWhen capacity is insufficient
2. Why do we need to flush to disk at this point when a transaction commits, if you think about it, this is itredo logThe meaning of existence.
3. There’s a thread in the background, about once a secondlog bufferIn theredoLogs are flushed to disk
4. Shut down the server properly
5. docheckinpointwhen

Redo log Log file group – Disk

• Introduced,redo logStorage format in memory —block, then the diskredo logWhat does it look like? This is theredo logLog file group.

SHOW VARIABLES LIKE 'datadir';
Copy the code

The actual write is a circular write mechanism, write outib_logfile0, just writeib_logfile1

• redo logLog file format

willlog bufferIn theredoThe essence of flushing logs to disk is to bringblockIs written to the log file. So it’s likebuffer poolThe pages are the same size as those on disk. inredoIn a log file group, each file is the same size and format.

LSN – log sequence number

Log the total amount of redo logs that have been written. Instead of starting at zero, we start at 8704. How does the value of the LSN change

• Initialize the

Because ablockThe head of the footprint12byte

• Insert a200Bytes ofmtr

• Insert a1000Bytes ofmtr

Summary: EachmtrIt’s all there at the beginninglsnValues, such asmtr1The corresponding is8716.mtr2The corresponding is8916, and so on.lsnThe smaller the value is, the earlier the data is writtenredoIn the log.

Flushed_to_disk_lsn – Flushes the logs in the buffer to disk

The redo buffer already has a global variable buf_free that records where the redo log should be next inserted into the buffer. You actually need a global variable to record which redo logs stored in the buffer have been flushed to the disk log file group. This global variable is buF_next_to_write.

• It’s like two addressesbuf_next_to_writewithbuf_freeThe same,

Flushed_to_disk_lsn and LSN are the corresponding variables in flushed_to_disk_lsn and LSN. The initial values are 8704 and redo logs are generated in flushed_to_disk_lsn. So with buf_free flushed_to_disk_lsn gradually and LSN the gap.

Everything is ready except the east wind

Flush list

• flushThe dirty pages in the linked list are arranged in the order in which the first modification occurred, that is, according tooldest_modificationOn behalf of thelsnValue to sort. thislsnIs the first time to modify the corresponding pagemtrAt the beginning oflsn
• Pages that are updated more than once do not occurflushMultiple orders of linked list nodes simply update each node — that is, each control blocknewest_modificationthelsnValue. thislsnThe value is the last modification of this pagemtrAt the end of thelsnValue.

Come out:

The size of the log file group is limited, so write in a loop. This raises the question: Does a newly written redo log retrace the original redo log? When will it be covered?

• Judge someredoWhether the disk space occupied by logs can be overwritten.Is whether its corresponding dirty page has been flushed to disk.

For example,So here’s the situationmtr_1/mtr_2 redoThe log group has been written to disk, but the corresponding dirty pages are still in memory. Why do you know? becauseflushThe linked list still existsmtr_1/mtr_2The two corresponding control blocks.Page A has been flushed to disk, so the MTR_1 log group is useless and can be overwritten.

checkpoint_lsn

Checkpoint_lsn specifies the number of logs that can be overwritten by the current system, and its initial value is 8704.

How to checkpoint?

Essentially adding the checkpoint_lSN operation

1. Calculate the maximum coverage that the current system can havelsnValue.

Where can I find this?flushFind in the linked list!flushAt the end of the listoldest_modificationOn behalf of thelsnThe value is the maximum number of logs that can be overwritten by the current systemlsnValue. The dirty page itself has been flushed to disk because of changes made to the dirty page recorded in the redo log. Redo logs are useless. 2. Write the information to the log management file.Record thecheckpointAfter the information,redoLog file groupslsnThe relationship of values is as follows.

conclusion

• 1, in MySQL, if every increment, deletion, and change is dropped disk, it must be very affected performance. But how do redo logs maintain reliability after a transaction commits? Yes, that’s where redo logs come in. Redo logs are written in order as soon as possible.

• There is also a redo log buffer between redo logs

• In fact, the checkpoint_lsn and the flushed_to_disk_lSN are all from the same starting point — 8704. Flushed_to_disk_lsn runs the fastest at flushed_to_disk_lsn. In fact, the flushed_to_disk_lsn flushes the flushed_to_disk_lsn at flushed_to_disk_lsn at flushed_to_disk_lsn. Leave enough space for future redo logs.

Reference source

How MySQL works

MySQL Official manual