What does mysql dirty page refer to?

If the contents of the memory data page and the disk data page are different in MySQL, then this memory page will be 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.

Dirty page (memory page)

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

• Dirty pages: Data in memory and disk are inconsistent

Why dirty pages appear

Usually fast update operations , 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 placed in LRUlist

• If there are no free pages in the 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. This page will also be added to 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 to only rely on the buffer for data modification and reading?

If only the cache area is used for data modification and reading, once the database If the machine crashes, the data in the cache area will be lost. Therefore, MySQL uses the redo log introduced before to achieve data recovery in the event of an abnormal restart.

Explained in a simple way, before updating the buffer, the operation is recorded in the redo log to ensure that in the event of an abnormal restart, the data in the buffer can still be recovered.

Why dirty pages must be refreshed

• It is said above that the data is only placed in the buffer, there will be database downtime, memory data lost. 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 mustneed to refresh the dirty pages in the memory to the disk according to certain rules. With the refresh operation, the size of the buffer Both the problem and the size of the redo log can be solved.

• The buffer does not need to be infinite because it can be persisted to disk

• Because once the data in the redo log is persisted to disk , the corresponding part of the data can be released, so there is no need to set the redo log too large.

Brushing dirty pages has the following 4 scenarios

undefinedThe impact it will cause

If the redo log is full

try to avoid redo log being full. 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.

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