Transcript How to Calculate MySQL Hit Rates

HOW TO CALCULATE
MYSQL HIT RATES
By Will Mayall
27-April-2015
1
7/21/2015

mysql> use information_schema;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'KEY_READ_REQUESTS' into @KEY_READ_REQUESTS;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'KEY_READS'
into @KEY_READS;

select 1 - (@KEY_READS/@KEY_READ_REQUESTS) * 1 AS KEY_CACHE_HIT_RATE;

+--------------------+

| KEY_CACHE_HIT_RATE |

+--------------------+

| 0.9975138662972965 |

+--------------------+

1 row in set (0.00 sec)

mysql>
CALCULATE KEY CACHE HIT RATE
2
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Key_write_requests' into @Key_write_requests;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Key_writes'
into @Key_writes;

select 1 - (@Key_writes/@Key_write_requests) * 1 AS KEY_CACHE_WRITES;

+-------------------+

| KEY_CACHE_WRITES

+-------------------+

| 0.978793901714135 |

+-------------------+

1 row in set (0.00 sec)

mysql>
|
CACULATE KEY CACHE WRITES
3
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'QCACHE_HITS' into
@VALUE1;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'QCACHE_INSERTS' into
@VALUE2;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'COM_SELECT' into
@VALUE3;

select @VALUE1/(@VALUE1 + @VALUE2) as QCACHE_HITS;

+--------------------+

| QCACHE_HITS

+--------------------+

| 0.3814596043317718 |

+--------------------+

1 row in set (0.00 sec)

mysql>
|
CALCULATE QUERY CACHE HIT RATE
4
7/21/2015

mysql> select @VALUE1/(@VALUE1 + @VALUE3) as OVERALL_QCACHE_HITS;

+---------------------+

| OVERALL_QCACHE_HITS |

+---------------------+

| 0.37696441588220664 |

+---------------------+

1 row in set (0.00 sec)

mysql>
CONTINUED…
5
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Select_full_join' into
@Select_full_join;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Select_range_check' into
@Select_range_check;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Select_scan' into @Select_scan;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Com_select' into @Com_select;

select 1 - (@Select_full_join+@Select_range_check+@Select_scan)/(@Com_select) * 1 AS HEAVY_JOIN_RATE;

+--------------------+

| HEAVY_JOIN_RATE

+--------------------+

| 0.9969589922564667 |

+--------------------+

1 row in set (0.00 sec)

mysql>
|
CALCULATE HEAVY JOINS
6
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Created_tmp_disk_tables' into @Created_tmp_disk_tables;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Created_tmp_tables' into @Created_tmp_tables;

select 1 - (@Created_tmp_disk_tables/@Created_tmp_tables) * 1 AS
TMP_TABLE_HIT_RATE;

+--------------------+

| TMP_TABLE_HIT_RATE |

+--------------------+

| 0.6969795037756203 |

+--------------------+

1 row in set (0.00 sec)

mysql>
CALCULATE TMP TABLE HIT RATE
7
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Threads_created' into @Threads_created;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Connections'
into @Connections;

select 1- (@Threads_created/@Connections) * 1 AS THREAD_CACHE_HIT_RATE;

+-----------------------+

| THREAD_CACHE_HIT_RATE |

+-----------------------+

|

+-----------------------+

1 row in set (0.00 sec)

mysql>
0.9999125421801777 |
CALCULATE THREAD CACHE HIT RATE
8
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Handler_read_rnd'
into @Handler_read_rnd;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Handler_read_rnd_next' into @Handler_read_rnd_next;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Handler_read_last'
into @Handler_read_last;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Handler_read_next'
into @Handler_read_next;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Handler_read_prev'
into @Handler_read_prev;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like 'Handler_read_rnd'
into @Handler_read_rnd;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'Handler_read_rnd_next' into @Handler_read_rnd_next;

select 1 (@Handler_read_rnd+@Handler_read_rnd_next)/(@Handler_read_rnd+@Handler_read_rnd_next+@H
andler_read_last+@Handler_read_next+@Handler_read_prev+@Handler_read_rnd+@Handler_read_
rnd_next) * 1 AS INDEX_HIT_RATE;
CALCULATE INDEX HIT RATE
9
7/21/2015

+--------------------+

| INDEX_HIT_RATE

+--------------------+

| 0.9369443667114482 |

+--------------------+

1 row in set (0.00 sec)

mysql>
|
CONTINUED…
10
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_reads' into @VALUE10;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_read_requests' into @innodb_buffer_pool_read_requests;

select 1 - (@VALUE10/@innodb_buffer_pool_read_requests) * 1 AS
INNODB_BUFFER_POOL_HIT_RATE;

+-----------------------------+

| INNODB_BUFFER_POOL_HIT_RATE |

+-----------------------------+

|

+-----------------------------+

1 row in set (0.00 sec)

mysql>
0.9999993741148261 |
CALCULATE INNODB BUFFER POOL
HIT RATE
11
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_pages_data' into @VALUE11;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_pages_total' into @VALUE12;

select 1 - (@VALUE11/@VALUE12) * 1 AS INNODB_BUFFER_POOL_USAGE;

+--------------------------+

| INNODB_BUFFER_POOL_USAGE |

+--------------------------+

|

+--------------------------+

1 row in set (0.00 sec)

mysql>
0.9084971309974362 |
CALCULATE BUFFER POOL USAGE
12
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_wait_free' into @VALUE13;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_buffer_pool_write_requests' into @VALUE14;

select 1 - (@VALUE13/@VALUE14) * 1 AS INNODB_CACHE_WRITE_HIT_RATE;

+-----------------------------+

| INNODB_CACHE_WRITE_HIT_RATE |

+-----------------------------+

|

+-----------------------------+

1 row in set (0.00 sec)

mysql>
1 |
CALCULATE INNODB CACHE WRITE
HIT RATE
13
7/21/2015

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_log_writes' into @VALUE15;

select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME like
'innodb_log_write_requests' into @VALUE16;

select 1 - (@VALUE15/@VALUE16) * 1 AS INNODB_LOG_CACHE_HIT_RATE;

+---------------------------+

| INNODB_LOG_CACHE_HIT_RATE |

+---------------------------+

|

+---------------------------+

1 row in set (0.00 sec)

mysql>
0.8735020135258925 |
CALCULATE LOG CACHE HIT RATE
14
7/21/2015

SELECT
innodb_os_log_written_per_minute*60

AS estimated_innodb_os_log_written_per_hour,

CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024/2, 1), 'MB')

AS a_good_value_for_innodb_log_size_value


FROM
(SELECT SUM(value) AS innodb_os_log_written_per_minute FROM (

SELECT -VARIABLE_VALUE AS value


FROM INFORMATION_SCHEMA.GLOBAL_STATUS

WHERE VARIABLE_NAME = 'innodb_os_log_written'

UNION ALL

SELECT SLEEP(60)
FROM DUAL


UNION ALL

SELECT VARIABLE_VALUE

FROM INFORMATION_SCHEMA.GLOBAL_STATUS

WHERE VARIABLE_NAME = 'innodb_os_log_written'
) s1


) s2

;
CALCULATE THE BINARY LOG SIZE
15
7/21/2015

+------------------------------------------+----------------------------------------+

| estimated_innodb_os_log_written_per_hour | a_good_value_for_innodb_log_size_value |

+------------------------------------------+----------------------------------------+

|

+------------------------------------------+----------------------------------------+

1 row in set (1 min 0.01 sec)

mysql>
11550720 | 5.5MB
|
CONTINUED…
16
7/21/2015