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.
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
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.
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
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.
database downtime and memory data loss# will occur. ##. So it needs to be flushed to disk.
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.
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.
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.
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.
If the redo log is full
Try to avoid itredo 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.
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!