InnoDB buffer pool active pages

Posted on

Question :

I’d like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW INNODB ENGINE STATUS), so one can’t find out how many distinct pages are actually active in the buffer pool.

I have Googled around and read various “how to set/find out optimal innodb buffer size” type Q&As, but they don’t involve that question.

Answer :

You can kind of do this from MySQL 5.6 and onwards using information_schema. I have an example of finding the ideal buffer pool size on my blog here:

The caveat is that you may need to either restart or lower the buffer pool size first. Inactive pages will just stay in memory if there is no need to make free space – which could skew your result on a server that’s been running for a while, yet has plenty of memory.

Leave a Reply

Your email address will not be published. Required fields are marked *