MyISAM Key Buffer in MySQL – Configuration and Optimization

myisamMySQL

Good evening,

I want to know how can I use informations about MyISAM to calculate :

  • size of buffer,
  • size of buffer used,
  • Write ratio,
  • Read ratio,
  • and MyISAM key cache hit rate

and thank you.

Best Answer

Key Buffer Size

SELECT variable_value INTO @KBS
FROM information_schema.global_variables WHERE variable_name='key_buffer_size';
SELECT @KBS KeyBufferSize;

This should be the same number as

SHOW VARIABLES LIKE 'key_buffer_size';

Key Buffer in Use

SELECT variable_value INTO @KBUSED
FROM information_schema.global_status WHERE variable_name='Key_blocks_used';
SELECT variable_value INTO @KCBSIZ
FROM information_schema.global_variables WHERE variable_name='key_cache_block_size';
SET @KBINUSE = @KBUSED * @KCBSIZ;
SELECT @KBUSED KeyBlocksUsed,@KCBSIZ KeyCacheBlockSize,@KBINUSE KeyBlocksInUse;

Key Read and Write Ratios, Query Cache HitRate (based on @SECONDS_TO_TEST)

SET @SECONDS_TO_TEST = 30;
SELECT variable_value INTO @KRDS1
FROM information_schema.global_status WHERE variable_name='Key_reads';
SELECT variable_value INTO @KRQS1
FROM information_schema.global_status WHERE variable_name='Key_read_requests';
SELECT variable_value INTO @KWRT1
FROM information_schema.global_status WHERE variable_name='Key_writes';
SELECT variable_value INTO @KWRQ1
FROM information_schema.global_status WHERE variable_name='Key_write_requests';
SELECT variable_value INTO @QCHITS1
FROM information_schema.global_status WHERE variable_name='Qcache_hits';
SELECT variable_value INTO @QCNSRT1
FROM information_schema.global_status WHERE variable_name='Qcache_inserts';
SELECT variable_value INTO @QCNC1
FROM information_schema.global_status WHERE variable_name='Qcache_not_cached';
SELECT SLEEP(@SECONDS_TO_TEST);
SELECT variable_value INTO @KRDS2
FROM information_schema.global_status WHERE variable_name='Key_reads';
SELECT variable_value INTO @KRQS2
FROM information_schema.global_status WHERE variable_name='Key_read_requests';
SELECT variable_value INTO @KWRT2
FROM information_schema.global_status WHERE variable_name='Key_writes';
SELECT variable_value INTO @KWRQ2
FROM information_schema.global_status WHERE variable_name='Key_write_requests';
SELECT variable_value INTO @QCHITS2
FROM information_schema.global_status WHERE variable_name='Qcache_hits';
SELECT variable_value INTO @QCNSRT2
FROM information_schema.global_status WHERE variable_name='Qcache_inserts';
SELECT variable_value INTO @QCNC2
FROM information_schema.global_status WHERE variable_name='Qcache_not_cached';
SET @KRQS = @KRQS2 - @KRQS1; SET @KRDS = @KRDS2 - @KRDS1;
SET @KRR = 100 - ((100 * @KRDS) / @KRQS);
SET @KWRT = @KWRT2 - @KWRT1; SET @KWRQ = @KWRQ2 - @KWRQ1;
SET @KWR = 100 - ((100 * @KWRT) / @KWRQ);
SET @QCHITS = @QCHITS2 - @QCHITS1; SET @QCNSRT = @QCNSRT2 - @QCNSRT1;
SET @QCNC = @QCNC2 - @QCNC1; SET @QCHR = 100 * @QCHITS / (@QCHITS + @QCNSRT + @QCNC);
SELECT @KRDS KeyReads,@KRQS KeyReadRequests,IFNULL(@KRR,100) KeyReadRatio,
    @KWRT KeyWrites,@KWRQ KeyWriteRequests,IFNULL(@KWR,100) KeyWriteRatio,
    @QCHITS QueryCacheHits,@QCNSRT QueryCacheInserts,@QCNC QueryCacheNotCached,
    IFNULL(@QCHR,0) QueryCacheHitRate;
Related Question