3

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.

Improve this question
2
  • I though "youngs/s" rates could give the answer in InnoDB status, but they could also mean that the same page is used over and over. Also old pages are just a specified percent of the buffer. Pages are not aging by time, but by (un)usage.
    – user77376
    May 7, 2014 at 14:02
  • Information_schema contains INNODB_BUFFER_POOL_PAGES table for Percona, and INNODB_BUFFER_PAGE table for MariaDB. Though the former seems to lack updates. These could help to have the answer.
    – user77376
    May 7, 2014 at 16:38

1 Answer 1

Reset to default
2

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: http://www.tocker.ca/2013/05/31/estimating-mysqls-working-set-with-information_schema.html

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.

Improve this answer
2
  • That's a great solution. Though there's an access_time field, which currently I don't know how to parse, but seems to be promising.
    – user77376
    May 8, 2014 at 8:19
  • The rule of thumb would be to modify my script to make sure access_time was within the last 5 minutes. Let me know how it goes!
    – Morgan Tocker
    May 8, 2014 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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