Mysql – How does MySQL evict pages from the buffer pool

buffer-poolcacheMySQL

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?

Best Answer

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.

The documentation states, "When room is needed to add a new page to the buffer pool". So I would imagine it waits until it is pretty close to full. Regarding the characteristics of LRU in general, you can read up on that elsewhere as it is not MySQL specific. MySQL Does however use the "Midpoint insertion strategy" which makes things not a true LRU in order to deprioritize superfluous pages.

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

Related Question