Mysql – InnoDB Buffer Pool Hit Rate

buffer-poolinnodbMySQL

As given at http://cherry.world.edoors.com/COBFKUqnUdBY one can obtain buffer hit ratio by following sql query:

SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

QUESTIONS

From what period are buffer hit ratio given by that query? From start of database engine till now?
Is there possibility to obtain buffer hit ratio from given period of time? (for example: last 10 minutes)

Best Answer

That's the Hit Rate since Uptime (Last MySQL Startup)

There are two things you can do to get the Last 10 Minutes

METHOD #1

Flush all Status Values, Sleep 10 min, Run Query

FLUSH STATUS;
SELECT SLEEP(600) INTO @x;
SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

METHOD #2

Capture innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, Sleep 10 minutes, Run Query with Differences in innodb_buffer_pool_read_requests and innodb_buffer_pool_reads

SELECT
    P1.variable_value,P2.variable_value
INTO
    @rqs,@rds
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads'; 
SELECT SLEEP(600) INTO @x;
SELECT round (((P2.variable_value - @rds) / (P1.variable_value - @rqs)),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';

Give it a Try !!!

Related Solutions

MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)

The InnoDB buffer pool stores copies of anything that is stored in database pages on disk. That includes:

Data (which is really the clustered primary index for each table)
Secondary indexes
Blobs/Text/Varchar that won't fit on data pages
Undo pages
Change buffer pages
etc.

There's no way you can exclude one of these types of pages. They all occupy the buffer pool from time to time, based on demand.

So strictly speaking, the only answer to your question of how to exclude InnoDB Blob data from the buffer pool is to use a different storage engine.

You could also exclude Blob/Text/Varchar columns from your queries. Just don't reference these columns in your query (avoid using SELECT *). InnoDB is smart enough to skip loading the extra pages of Blob/Text/Varchar data it doesn't have to.

But you should take the recommendation of tools like mysqltuner with a grain of salt. You don't necessarily need as much buffer pool as the total amount of data. In fact, it's more typical that you have an 80/20 rule going on: 80% of your queries are served by 20% of your data.

Your mileage may vary -- the types of queries you run determine what is the best compromise on size of buffer pool. You could give it a best-guess allocation, and then monitor the ratio of logical page reads vs. physical page I/O:

mysql> show global status like 'innodb_buffer_pool_read%s';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 151062817 |
| Innodb_buffer_pool_reads         | 3585      |
+----------------------------------+-----------+

Or the page hit rate:

mysql> show engine innodb status\G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
...
Buffer pool hit rate 999 / 1000, young-making rate 36 / 1000 not 0 / 1000
...

If you don't like these ratios, then allocate more RAM to the buffer pool.

Mysql – optimal size of innodb buffer pool

If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.

Where does the idea of having additional 10% extra space for the Buffer Pool come from ???

Please note the following diagram of the InnoDB Architecture

InnoDB Architecture

Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).

From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,