List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 30 2008 5:12am
Subject:Re: mysql variables
View as plain text  
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
> key_buffer_size=1G.
> 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.

	Dan Nelson
mysql variablesKrishna Chandra Prajapati30 Apr
  • Re: mysql variablesDan Nelson30 Apr
    • Re: mysql variablesKrishna Chandra Prajapati30 Apr
      • Re: mysql variablesSebastian Mendel30 Apr
      • Re: mysql variablesDan Nelson30 Apr