2

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?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Hadi
  • 945
  • 3
  • 10
  • 31
  • "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?" Not sure what you are asking here.. InnoDB fills the buffer pool only based on select queries and works with some [algoritms](https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html) and updates that buffer pool when updating or deleting.. – Raymond Nijland Dec 26 '18 at 19:09
  • Thanks for the clarification, I updated the question to reflect what I'm looking for. – Hadi Dec 26 '18 at 19:13
  • I think you would better ask this question in the dba sister site of SO because this question is not about software development. You want to understand how a specific feature of a specific rdbms product works and how you can configure its behaviour. Developers typically would not really care about such details, but DBAs would. Although, you stand even more chance at mysql or percona forums with this question. – Shadow Dec 26 '18 at 19:26

1 Answers1

2

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

MarCPlusPlus
  • 366
  • 1
  • 5
  • Thanks, is there any documentation on when the LRU algorithm (e.g. based on time, hit ratio, etc.) the pages are evicted? Or like I need to go over mysql source code? – Hadi Dec 27 '18 at 03:11
  • 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. – MarCPlusPlus Dec 27 '18 at 09:52
  • I see, so it only evicts when a new page is required to be added. – Hadi Dec 27 '18 at 19:09