Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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;
share|improve this answer
    
Thank you :D have you an article about using informations about Innodb, query cache for calculate some statistics –  FÔx Gênki Jun 17 '13 at 21:34
    
The book <a href=amazon.com/Database-Design-Tuning-Robert-Schneider/dp/… Database and Tuning</a> has a good tuning and monitoring guide for InnoDB on pages 265-279, naming all the status variables you need to monitor. –  RolandoMySQLDBA Jun 17 '13 at 21:40
    
that's great, thanks a lot –  FÔx Gênki Jun 17 '13 at 21:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.