InnoDB memory usage buffer pool status

InnoDB memory usage buffer pool status

Making sense of INNODB buffer pool stats

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)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 1758417
Buffer pool size   393215
Free buffers       853
Database pages     360515
Old database pages 133060
Modified db pages  300
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7365790, not young 23099457
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1094342, created 185628, written 543182148
0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 360515, unzip_LRU len: 0
I/O sum[2571]:cur[0], unzip sum[0]:cur[0]

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.

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

This is the number of pages with data inside the Buffer Pool:

Database pages     360515

To see the amount of data in the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the percentage of the Buffer Pool in use, run this:

SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

This is the number of pages in the Buffer Pool that have to be written back to the database. They are also referred to as dirty pages:

Modified db pages  300

To see the Space Taken Up by Dirty Pages, run this:

SELECT FORMAT(DirtyPages*PageSize/POWER(1024,3),2) BufferPoolDirtyGB FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the Percentage of Dirty Pages, run this:

SELECT CONCAT(FORMAT(DirtyPages*100.0/TotalPages,2),' %') BufferPoolDirtyPercentage FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

As for the other things in the display, run this:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

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.

Reference:

http://dba.stackexchange.com/questions/56494/making-sense-of-innodb-buffer-pool-stats

http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html