What is a mysql dirty page?

In mysql, when the contents of the memory data page and the disk data page are inconsistent, the memory page is called a dirty page. Scenarios for flushing dirty pages: 1. When the redo log is full, MySQL will suspend all update operations and synchronize the dirty pages corresponding to this part of the log to the disk; 2. When the system memory is insufficient, some data pages need to be eliminated. If eliminated If the dirty pages are dirty pages, you must first synchronize the dirty pages to the disk; 3. MySQL thinks that when the system is idle, it will synchronize the memory data to the disk when there is an opportunity. There is no performance problem in this case.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Dirty page (memory page)

• Clean page: The data in memory and disk are consistent
• Dirty page: The data in memory and disk The data is inconsistent

Why dirty pages appear

Usually fast update operations are all writing to memory and logs. It will not synchronize to the disk data page immediately. At this time, the content of the memory data page and the disk data page are inconsistent, which we call dirty page.

This involves the memory management mechanism of mysql

Brief description of the memory management mechanism

The buffer contains these three categories of lists. They are: LRUList, FreeList, FlushList.

When the database is first started, there is no data page in the LRUlist. FreeList stores free pages.

• When a page needs to be read, a free page will be obtained from FreeList. After reading the data, it will be put into LRUlist.
• If there is no free page in FreeList, Then the last page in the LRU list is eliminated according to the LRU algorithm
• When the page in the LRUlist is modified, the page becomes a dirty page, and this page will also be added to the FlushList

Note: At this time, this page is both in LRUlist and FlushList.

Summary: LRUList (manage pages that have been read) and FreeList (manage free pages) are used to manage page availability; FlushList (manage dirty pages) is used to manage the refresh of dirty pages

During the process of synchronizing dirty page data to disk, if the SQL statement is executed on the disk data page. The execution speed will slow down

Is it okay if data modification and reading only rely on the buffer?

If data modification and reading only rely on the memory buffer, then once If the database goes down, the data in the memory will be lost. Therefore, MySQL uses the redo log mentioned before to achieve data recovery after abnormal restart.

To put it simply, before updating the buffer, write the redo log first to ensure that the data in the buffer can be restored normally after an abnormal restart.

Why dirty pages must be refreshed

• It is said above that the data is only placed in the buffer, and database downtime and memory data loss# will occur. ##. So it needs to be flushed to disk.
If the redo log is infinitely large or has many files, there will be a large number of modification operations in the system. Once the system goes down, the recovery time will be very long.

So naturally, we must

need to refresh the dirty pages in the memory to the disk according to certain rules. With the refresh operation, the buffer size problem and redo The problem of log size can be solved.

The buffer does not need to be infinite, because it can be persisted to the disk.
• The redo log does not need to be infinite, because once it is persisted to the disk, the corresponding redo log will That part of the data can be released.

There are the following 4 scenarios for brushing dirty pages

• When

  redo log is full , mysql will Pause all update operations, and synchronize the dirty pages corresponding to this part of the log to the disk.

• System

  When the memory is insufficient, it is necessary to eliminate some data pages. If dirty pages are to be eliminated, First synchronize dirty pages to disk.

• MySQL thinks that when the system is

  idle, it will synchronize the memory data to the disk when there is an opportunity. There is no performance problem in this case.

• MySQL shuts down normally, MySQL will synchronize all dirty pages in memory to disk, so that the next time MySQL starts, Data can be read directly from the disk, and the startup speed will be very fast. There are no performance issues with this.

The impact it will cause

If the redo log is full

Try to avoid it

redo log is filled with . Otherwise the update of the entire system will stop. At this time, the write performance becomes 0, and you must wait for the log corresponding dirty page synchronization to be completed before it can be updated. This will cause the execution of sql statements to be very slow.

[Related recommendations:

mysql video tutorial]

The above is the detailed content of What is a mysql dirty page?. For more information, please follow other related articles on the PHP Chinese website!