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)
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Michał Herman
  • 259
  • 5
  • 16

2 Answers2

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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • I would suggest 2% (0.02) is a reasonable upper limit on the ratio. – Rick James May 01 '16 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 http://www.fromdual.com/innodb-variables-and-status-explained – wizurd Mar 01 '18 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 '23 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).

Flourid
  • 511
  • 2
  • 7
  • 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 – RolandoMySQLDBA Apr 12 '23 at 14:06