MyISAM key cache stats with information_schema.KEY_CACHES

Following on from a post last week on INNODB_BUFFER_PAGE queries I thought I’d look at the equivalent for the MyISAM key cache. The information_schema.KEY_CACHES is MariaDB only at the moment

SELECT KEY_CACHE_NAME,
	  FULL_SIZE / 1024 / 1024 AS key_buffer_mb,
	  USED_BLOCKS * BLOCK_SIZE / 1024 / 1024 AS  used_mb,
	  UNUSED_BLOCKS * BLOCK_SIZE / 1024 / 1024 AS  unused_mb,
	  100 - ((`READS` / READ_REQUESTS) * 100) AS read_cache_hit_rate,
	  DIRTY_BLOCKS / (USED_BLOCKS + UNUSED_BLOCKS) AS dirty_percentage,
	  READ_REQUESTS / (`READ_REQUESTS` + WRITE_REQUESTS) AS read_write_ratio
FROM information_schema.`KEY_CACHES`;

Most of the data returned should be self explanatory. The read_write_ratio indicates the number of reads versus writes, for example 1.0 indicates 100% reads whole 0.75 would indicate 75% read and 25% writes.


Leave a Reply