List:General Discussion« Previous MessageNext Message »
From:Michael Griffith Date:November 6 2001 2:15am
Subject:key cache optimization
View as plain text  
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?

If I wait long enough, it will again take 30+ seconds as if it were removed
from the cache, even though key_buffer_size=560M. It should have 30M to
spare according to Key_blocks_used. But it's not removing it from the cache
or Key_reads would increase when it reloaded.

Am I misunderstanding something or is the key cache really this inefficient?

Thanks in advance

key cache optimizationMichael Griffith6 Nov
  • Re: key cache optimizationJeremy Zawodny6 Nov
  • Re: key cache optimizationMichael Griffith6 Nov
    • Re: key cache optimizationJeremy Zawodny7 Nov
  • Re: key cache optimizationDan Nelson6 Nov