Technology Sharing | The meaning of Pages flushed up to in show engine innodb status

Author: Hu Chengqing

DBA team member, good at fault analysis and performance optimization, personal blog: 1614d696f184d1 https://www.jianshu.com/u/a95ec11f67a8, welcome to discuss.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

There is a doubt that existed a long time ago:
When there is no write, why is Pages flushed up to in show engine innodb status not equal to Last checkpoint point? What does it mean?

When I was writing an article today, I wanted to pass a test to verify a statement from the changes of these LSNs. The result evoked this problem again. After some "research" (in fact, it is a Google pass, of course, it is still necessary Some test verification), finally figured out the meaning.

I mainly borrowed from an article: http://blog.itpub.net/30221425/viewspace-2154670/, because there is a lot of source code analysis, and I can’t understand the source code, it took a lot of time to understand the principle. , And verified its accuracy through tests, and then compiled it into a document that makes it easier for people like me who don’t understand the code to read.

When I was writing an article today, I wanted to pass a test to verify a statement from the changes in these LSNs. The result evoked the problem again. This time I decided to thoroughly understand what is going on.

I mainly borrowed from an article: http://blog.itpub.net/30221425/viewspace-2154670/, because there is a lot of source code analysis, and I can't understand the source code, so it took a lot of time to understand it. Principle, and verified its accuracy through tests, and then compiled a document that is more convenient for people like me who don’t understand the code to understand.

LSN

Part of the output of show engine innodb status is the status of LSN:

mysql> pager grep -A 5 LOG                                      
PAGER set to 'grep -A 5 LOG'
mysql> show engine innodb status\G
LOG
---
Log sequence number 2471197058
Log flushed up to   2471197058
Pages flushed up to 2471197058
Last checkpoint at  2471197049
1 row in set (0.00 sec)

Log sequence number: All operations that modify data will generate redo log, which is the maximum value of the current redo log sequence number (hereinafter referred to as LSN) in the system;

Log flushed up to: the serial number of the redo log that has been flushed;

Pages flushed up to: the focus of the discussion;

Last checkpoint at: The location of the last checkpoint.

What does Pages flushed up to mean? The most common saying is that dirty pages are flushed to the LSN of the disk, but Last checkpoint at also means that the previous data pages have been flushed, and usually we see Pages flushed up to always larger than Last checkpoint at, so this statement It must be wrong.

According to the source code analysis in the reference article, the value logic of Pages flushed up to is:

Pages flushed up to takes the value of the largest oldest modification lsn in the flush list of all buffer pool instances with the smallest oldest modification lsn (this may be a bit difficult to understand, we can simply understand it as taking the largest one in the flush list oldest modification lsn);

If the obtained oldest modification lsn is 0, it means that there is no dirty page, then we take the value of log_sys->lsn, which is the Log sequence number displayed by show engineinnodb status.

LSN update logic

What is the purpose of taking Pages flushed up to? Because it is the maximum LSN of the dirty page in the flush list, it can be used as an end position for this dirty page when doing a checkpoint next.

In a normal running MySQL, the system LSN increases (real-time) with the execution of SQL (SQL that modifies data), and the value of Pages flushed up to will also be updated at a certain frequency, so Pages flushed up to generally will Behind the system LSN. Then, when the InnoDB master thread does checkpoint every second and every ten seconds (there are other mechanisms to trigger checkpoint, this will not affect understanding), after flushing dirty pages, the value of Last checkpoint at will be updated to Pages flushed up To, because Pages flushed up to is constantly updated, the Last checkpoint at we observe is generally smaller than Pages flushed up to.

When no data is written, why is the Last checkpoint point not equal to Pages flushed up to?

It is because the redo log will write MLOG_CHECKPOINT when doing checkpoint, and MLOG_CHECKPOINT occupies 9 bytes, so the system LSN will be increased by 9, and because dirty pages are flushed, the flush list is empty, and it will be directly when you get Pages flushed up to Take the system LSN value, so it will also be 9 greater than the Last checkpoint point.

Getting Pages flushed up to and checkpoint is not an atomic operation, it is obtained before the checkpoint, which refers to the position where the next checkpoint ends.

Test verification

We manually start a transaction to write a row of data (without committing), and we will find the following figure:

• The Log sequence number has increased because the system LSN is generated in real time;
• Log flushed up to remains unchanged, indicating that the redo log has not been flushed (it may also be observed that this value increases and is consistent with the Log sequence number, because the InnoDB background thread flushes the redo log every second, and the redo log of uncommitted transactions may be flushed ahead of time. );
• Pages flushed up to has not changed, because this value is not generated in real time, as long as our hand speed is fast enough, we can see that this value has not changed by checking the LSN immediately after executing the insert;
• The Last checkpoint point has not changed. Similarly, as long as the hand speed is fast enough, the value viewed before the checkpoint will not change.

We check the LSN after a few seconds, and we will find that all the values have changed, and the first 3 values are the same, and the Last checkpoint point is 9 less than their value. This is because InnoDB Master Thread will checkpoint every second and every ten seconds. After all dirty pages are flushed, the flush list is empty, which conforms to the principle we mentioned earlier:

In the case of continuous writing, it can be observed that the Last checkpoint point is equal to Pages flushed up to (new Pages flushed up to has not been obtained after the checkpoint):