Menu
When I issue the SHOW GLOBAL STATUS; MySQL command, one of the lines I get is the following:
Innodb_buffer_pool_pages_dirty 28
When I look up the documentation, all I see is:
The number of pages currently dirty. Added in MySQL 5.0.2.
How can I fix this (I'm assuming having dirty pages isn't something good) ?
560
InnoDB performs certain tasks in the background, including flushing of dirty pages from the buffer pool. Dirty pages are those that have been modified but are not yet written to the data files on disk. In MySQL 8.0, buffer pool flushing is performed by page cleaner threads.
InnoDB aggressively flushes buffer pool pages if the percentage of dirty pages in the buffer pool reaches the innodb_max_dirty_pages_pct threshold. When configuring innodb_max_dirty_pages_pct_lwm , the value should always be lower than the innodb_max_dirty_pages_pct value.
The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
O_DIRECT is a flag passed when a file is opened. It instructs to bypass page cache and perform any IO operations directly against storage. So, the buffers in the application space are flushed directly to disk, without copying the data into page cache first and waiting for the kernel to schedule write-back operations.
Having dirty pages is normal when there are changes (UPDATE/INSERT/DELETE) to the database.
When you change a row, MySQL updates it in the buffer pool, marking the page as “dirty”. The change is written in the binary log as well, so in case of crash, MySQL will replay the log and data won't be lost. Writting to the binary log is a fast append-only (sequential) operation, while the actual page update uses random writes which are slower. (this is done in the background)
MySQL flushes dirty pages to disk when it needs to load new data in the buffer pool. So, having dirty pages in InnoDB is something normal - it's how it works and it's done to improve the overall performance.
But if you really want to get rid of them, set innodb_max_dirty_pages_pct to 0
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
