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
  • 185,223
  • 33
  • 326
  • 536
Michał Herman
  • 249
  • 6
  • 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
  • 185,223
  • 33
  • 326
  • 536
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
  • 531
  • 2
  • 8