In the last episode (Apr 30), Krishna Chandra Prajapati said:
> Key_blocks_unused 952405
> Key_blocks_used 395539
> Key_blocks_used is increasing day by day and Key_blocks_unused is
> decreasing day by day. Ater a month Key_blocks_unused will reach to
> 0. Does it mean that i need to increase the key_buffer_size. Already
> While going through forums and lists , i found that when
> key_blocks_unused is less. then select query will become slow. So
> whats the right solution.
In a perfect world, you would set key_buffer_size to the total size of
all your .MYI files. Depending on the size of your tables, that may
simply not be possible. Luckily, it's usually not necessary, either.
What is more important is the hit rate. Run "SHOW STATUS LIKE 'key%'",
and compare Key_read_requests (how many times mysql asked for a key)
against Key_reads (how many times mysql actually had to go to disk to
fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate.
If it's 0.99 or greater, that means that 99% of your key reads are
already coming from the key buffer, and adding more key buffers is
unlikely to give you a measurable performance increase.
Don't worry too much about your write hit rate (
1-(Key_writes/Key_write_requests) ); it's always going to be lower
because mysql tries to keep the on-disk copy of the index up to date.