“This is the 27th day of my participation in the Gwen Challenge in November. See details of the event: The Last Gwen Challenge in 2021”
An SQL statement that normally executes very fast, but sometimes, without knowing what’s going on, becomes very slow, and this scenario is very difficult to repeat, not only is it random, but it also lasts very short.
Why are SQL statements “slow”?
InnoDB handles the update statement with only one disk operation, logging. This log is called the redo log. After the redo log is written in memory, it is returned to the client. The update succeeded.
The database always finds time to update the data (the data in memory is written to disk, the term is flush). Before this flush operation is performed, the data in memory is inconsistent with the data on disk. When the contents of the memory data page and disk data page are inconsistent, we call the memory page “dirty page”. After memory data is written to disk, the contents of memory and data pages on disk are the same, called “clean pages.” Dirty pages and clean pages are in memory.
Quick updates are written to memory and logs, and the occasional “shake” in MySQL may be a dirty page flush.
So what triggers the database flush process?
-
InnoDB redo log full. At this point, the system stops all updates, pushes the checkpoint forward, and leaves room for redo log writing.
-
The system memory is insufficient. When new pages are needed and memory is insufficient, some data pages are weeded out to free up memory for other data pages. If dirty pages are eliminated, the dirty pages are written to disk first.
-
When MySQL considers the system “idle”. MySQL takes the time to swipe dirty pages whenever possible.
-
MySQL is shut down normally. In this case, MySQL flush all dirty pages from memory to disk, so that the next time MySQL starts up, it can read data directly from disk, which is faster.
Analyze the performance impact of the above four scenarios
The third case is when MySQL is idle, when the system is not under pressure, and the fourth case is when the database is about to shut down. In both cases, you don’t pay much attention to the “performance” issue.
The first is “Redo log full, flush dirty pages”, which InnoDB is trying to avoid. Because when that happens, the entire system can no longer accept updates, and all updates must be blocked. If you look at the monitor, the update count will drop to 0.
The second is “out of memory, write the dirty pages to disk first”, which is actually the normal situation. InnoDB uses a buffer pool to manage memory. The pages in the buffer pool have three states:
- The first is, not yet used;
- The second is, use and are clean pages;
- The third is that the pages are used and dirty.
InnoDB’s policy is to use as much memory as possible, so there are very few unused pages for a long running library.
When the data page to be read is not in memory, you must request a data page from the buffer pool. At this point, only the most unused data pages can be eliminated from memory: if a clean page is to be eliminated, it can be released for reuse; But if it is a dirty page, you must first brush the dirty page to disk, become a clean page before reuse.
Therefore, although it is normal to brush dirty pages, the following two situations will significantly affect performance:
- If a query has too many dirty pages to eliminate, the response time of the query will be significantly longer.
- The log is full, updates are blocked, and write performance drops to 0. This situation is unacceptable for sensitive services.
So InnoDB needs a mechanism to control the percentage of dirty pages in order to avoid both of these situations.
The end of this article, I hope to help you!