What are mysql dirty pages?

Dirty pages (memory pages)

Clean page: the data in memory and on disk are consistent Dirty page: the data in memory and on disk are inconsistent

Why do dirty pages appear?

Normally, fast update operations are all writing to memory and logs.
It will not be synchronized to the disk data page immediately. At this time, the contents of the memory data page are inconsistent with the disk data page, which we call a dirty page.
This involves the memory management mechanism of MySQL

Brief description of memory management mechanism

The buffer contains lists of these three categories. They are: LRUList, FreeList, and FlushList.
When the database is just started, there are no data pages in the LRUlist. FreeList stores free pages.

• When a page needs to be read, a free page is obtained from the FreeList, and after reading the data, it is placed in the LRUlist.
• If there are no free pages in the FreeList, the last page in the LRU list is eliminated according to the LRU algorithm.
• When a page in the LRUlist is modified, the page becomes a dirty page and is added to the FlushList.

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

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

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

Is it possible to modify and read data only by relying on the buffer?

If data modification and reading rely only on the memory buffer, once the database crashes, all data in the memory will be lost. Therefore, MySQL uses the redo log mentioned earlier to implement data recovery after abnormal restart. For an introduction to redo log, see this article: MySQL-redo log and binlog

In simple terms, redo log is written before updating the buffer to ensure that the data in the buffer can be restored normally after an abnormal restart.

Why dirty pages must be refreshed

• As mentioned above, if the data is only stored in the buffer, the database will crash and the memory data will be lost. So it needs to be flushed to disk.
• If the redo log is infinitely large or has many files, there are a large number of modification operations in the system. Once a crash occurs, the recovery time will be very long.

So naturally, we must refresh the dirty pages in the memory to the disk according to some rules. With the refresh operation, the buffer size problem and the redo log size problem can be solved.

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

There are four scenarios for flushing dirty pages:

• 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.
• When the system memory is insufficient, some data pages need to be eliminated. If dirty pages are eliminated, they must be synchronized to the disk first.
• When MySQL thinks the system is idle, it will synchronize memory data to disk when it has the opportunity, which does not cause performance problems.
• When MySQL is shut down normally, MySQL will synchronize all dirty pages in memory to disk, so that the next time MySQL is started, it can read data directly from disk, and the startup speed will be very fast. This has no performance issues.

The impact

1 If the redo log is full, try to avoid it. Otherwise the update of the entire system will stop. At this time, the write performance becomes 0, and the update can only be done after the synchronization of the corresponding dirty page of the log is completed. This will cause the SQL statement to execute very slowly.

This is the end of this article about what MySQL dirty pages are. For more relevant MySQL dirty pages, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!