0

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?

  • No customization; you have listed about all there is to know about buffer_pool management other than the "Change Buffer". – Rick James Dec 27 '18 at 0:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.