2

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)

2 Answers 2

2

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 !!!

3
  • I would suggest 2% (0.02) is a reasonable upper limit on the ratio.
    – Rick James
    May 1, 2016 at 20:54
  • Isn't Method #1 telling the Miss Rate? I thought Hit Rate was: Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100 fromdual.com/innodb-variables-and-status-explained
    – wizurd
    Mar 1, 2018 at 21:14
  • Since Innodb_buffer_pool_read_requests are all logical reads and Innodb_buffer_pool_reads are logical reads that could not be satisfied by the buffer pool and read from disk, hit rate should be (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100. I'll post a seperate answer for the SQLs.
    – Flourid
    Apr 12, 2023 at 13:16
1

The SQLs from RolandoMySQLDBA calculates the buffer pool miss rate. To calculate the hit rate (in percentage) you need to use the following formula

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100

(Innodb_buffer_pool_read_requests are all logical reads and Innodb_buffer_pool_reads are logical reads that could not be satisfied by the buffer pool and read from disk as explained in the documentation)

The SQLs are therefore for 5.7:

SELECT round ((P1.variable_value - P2.variable_value) / P1.variable_value  * 100,4) 'Hit Rate in %', 
P2.variable_value 'Physical BP Reads', P1.variable_value 'All BP Reads'
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'; 

and for 8.0:

SELECT round ((P1.variable_value - P2.variable_value) / P1.variable_value * 100,4) 'Hit Rate in %', 
P2.variable_value 'Physical BP Reads', P1.variable_value 'All BP Reads'
FROM performance_schema.global_status P1,
performance_schema.global_status P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads'; 

Also, FLUSH STATUS does no longer reset those two counters (tested in MySQL 8.0).

1
  • It must be pointed out that the original question asks for Hit Ratio not Hit Rate (which is a percentage). My mistake in my answer was not using the word Ratio, Note how the comment from Rick James coincides with Ratio. A ratio of innodb_buffer_pool_reads / innodb_buffer_pool_read_requests being 0.02 means 1 read from disk for every 50 reads requests. Your answer is expressing 98% (49 reads requests out of 50 not needing to read from disk). Nothing wrong with your answer from that perspective so +1 for you. So a ratio above 0.02 would be bad. This is also the Miss Rate as you mentioned Apr 12, 2023 at 14:06

Your Answer

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

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