Innodb_buffer_pool_pages_free - Can it be 1 or 0?

Question

I have been researching this topic on whether the Innodb_buffer_pool_pages_free can be 0 or not and still has no impact on the site. Some DB admins are of the opinion that Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total x 100 should be 5% or more while others are saying that it can be 0 since the db will generally use all of the buffer pool size and when it becomes 0, the LRU pages will get evicted and that is just as quick.

Let's assume that the other metrics are okay such as the Buffer pool hit ratio is close to 100% and Innodb_buffer_pool_pages_flushed and Innodb_buffer_pool_pages_dirty are low.

Can Innodb_buffer_pool_pages_free be 0 or 1?

On my site, Innodb_buffer_pool_pages_free is rapidly going down by up to 0.5% per day. Currently, the ratio of Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total x 100 is 8.65% and going down. Total size of buffer pool is 96gb. Site is working okay but I am worried what will happen when Innodb_buffer_pool_pages_free eventually becomes 0 as the site is growing.

Answer 1

Sounds OK.

I surveyed many computers; 9% had Innodb_buffer_pool_pages_free = 0. A few had tiny values (including "1"); a few had millions. The machines with small values were probably busy flushing stuff to disk.

The value for innodb_buffer_pool_size is 96G? And you have 128GB of RAM? That is a good setting.

Do you have big tables and long queries against them? Maybe we can improve the indexes or something else.

As for Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total -- Most in my survey were under 1%; a few were over 99%. I recommend looking into things if it is > 15%.

Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size should be kept under, say, 70%. (I have seen it get over 100%, but that seems "impossible".) (That assumes a page size of 16KB.)

I also checked most of the changelogs and release notes for Innodb_buffer_pool_pages_free, but found nothing. I doubt if it is bothering anyone.

Answer 2

When the Innodb_buffer_pool_pages_free =0, and innodb needs to read a page not already there, it evicts the least recently used memory page and used that for the read. So this is really only a inconvenience if the evicted page isn't going to be needed very soon after, as then it will incur a disk read. So the buffer pool is a cache, it doesn't matter if its full, what matters is it is being used with a high cache hit ratio.

innodb_buffer_pool_reads (disk reads) vs innodb_buffer_pool_read_requests (memory) is a good metric of the cache miss ratio (so should be < %1 after startup most of the time) on ensuring the commonly used pages are in memory for user most of the time.

(note innodb_buffer_pool_read_requests was recently broken in 10.11.6 (and above versions released at the same time) but will be fixed next release in 10.11.7).