Search code examples
mysqlcachingbufferevict

How does MySQL evict pages from the buffer pool?


I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as explained here and how to customize it here. I am also aware that MySQL uses a LRU algorithm for page eviction as here.

However, is there a buffer pool daemon/thread that evicts read only pages (not dirty pages) that were brought to the buffer pool by say a select statement? Are there ways to customize it (for example, when I pass 95% of the bufferpool capacity, start evicting read pages or flush dirty pages). In other words, what triggers in the LRU algorithm evict pages from the buffer pool (e.g. time a page is on buffer pool, percentage of buffer pool filled, need to evict pages for new pages to load), are these adjustable?


Solution

  • Flushing and evicting are entirely different.

    https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_flush

    MySQL will flush dirty pages in accordance with innodb_max_dirty_pages_pct. The default database page size default in InnoDB is 16KB and the maximum setting for innodb_max_dirty_pages_pct is 99 so there isn't a whole lot of tolerance for the build up of dirty pages (although, as you know from the link you cite the actual flushing schedule calculation is a bit more complicated).

    After they are flushed they will then be evicted when they are the LRU.

    MySQL uses LRU with a "midpoint insertion strategy" approach. https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_midpoint_insertion_strategy