From: Dan Nelson
Date: November 6 2001 6:19am
Subject: Re: key cache optimization
List-Archive: http://lists.mysql.com/mysql/90172
Message-Id: <20011106061918.GA69543@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Nov 05), Michael Griffith said:
> Either the key cache is inefficient or I misunderstand it. Can anyone
> explain this?
> 
> Before query:
> | Key_blocks_used          | 530300     |
> | Key_read_requests        | 56072940   |
> | Key_reads                | 527026     |
> Query executed, takes 30+ seconds
> 
> After Query
> | Key_blocks_used          | 530300     |
> | Key_read_requests        | 56092143   |
> | Key_reads                | 527026     |
> No increase in key_reads, so lookup came from cache
> Same Query run again takes 0.11 seconds (272 times faster)
> 
> How can a query that uses only cached indexes and read only from RAM
> cache both times run faster the second time, when NOTHING WAS READ
> FROM disk?

How do you know it only read from the RAM cache?  You only pasted the
index stats; show us the handler_read counts as well, and also let us
see the query and EXPLAIN plan, and maybe iostat output during each
run.  If any rows in the explain plan do not have "Using index" in the
Extra column, mysql has to read record data, and at that point it's up
to your OS to cache it.  MyISAM table data is not cached by mysql at
all.  Not sure about BDB or InnoDB table types.

-- 
	Dan Nelson
	dnelson@stripped