Sunday, October 8, 2017

After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. Here's the output from show engine innodb status\G (we're running v5.5)

I wanted to know how well we're utilizing the buffer cache. After initially glancing at the output, it appeared that we are indeed using it, based off of the Pages made young and not young have numbers in them and Buffer pool hit rate is 1000 / 10000 (which I saw elsewhere on the web that this means it's being used pretty heavily. True?)

What's throwing me through a loop is why the young-making rate and not are both at 0/1000 and the young/s and non-young/s accesses are both at 0. Those would all indicate that it's not being used at all, right?

Can anyone help make sense of this?

This is in pages not bytes:

The Buffer pool size 393215

To see the Buffer Pool size in GB run this:

Note:As of MySQL 5.7.6 the "information_schema" is merged into performance_schema. So just change "information_schema" to "performance_schema" in the query to make it work.

You'll see all the status variables for the Buffer Pool. ou can apply the same queries against whatever you need to examine.

The buffer pool is divided into two part, a young list and a not-young list. The making rate shows how many pages in the buffer pools are being shuffled between the two lists.

Pages made young are not-young pages being made (i.e. being read out of the cache. Pages made not-young are pages moved from the young list because either they are too old, or because the young list is full.

The rate at pages are moved between the two depends upon how much of the buffer pool is currently being used vs the size of the young pool. Set at zero means your active set (the pages you are using) is smaller than the young pool.