List:General Discussion« Previous MessageNext Message »
From:Yi Wang Date:September 8 2008 6:03am
Subject:Re: How many key buffer size should be allocate?
View as plain text  
On Mon, Sep 8, 2008 at 12:47 PM, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Sep 08), Yi Wang said:
>> According to the mysql manual, I changed key_buffer_size from 8M to
>> 512M. But In mysql administrator, key buffer hitrate seems stay
>> unchanged. And key buffer usage always below 4M.
>>
>> The total db size is about 200M. And the system's load is slight. key
>> buffer size is 8M before changing. And the hitrate is about 100%
>> intermittently.
>
> If your database is only 200M, then your indexes are probably less than
> half that (if you total up all of your .MYI files, that's the total
> size of all your indexes).  Setting key_buffer_size larger than your
> total index size doesn't do any good.
>
>> Some details:
>>
>> mysql> SHOW status like 'uptime';
>> +---------------+-------+
>> | Variable_name | Value |
>> +---------------+-------+
>> | Uptime        | 6663  |
>> +---------------+-------+
>> 1 row in set (0.00 sec)
>>
>> mysql> SHOW variables like 'key_%';
>> +--------------------------+-----------+
>> | Variable_name            | Value     |
>> +--------------------------+-----------+
>> | key_buffer_size          | 419430400 |
>> | key_cache_age_threshold  | 300       |
>> | key_cache_block_size     | 1024      |
>> | key_cache_division_limit | 100       |
>> +--------------------------+-----------+
>> 4 rows in set (0.00 sec)
>>
>> mysql> SHOW status like 'key_%';
>> +------------------------+---------+
>> | Variable_name          | Value   |
>> +------------------------+---------+
>> | Key_blocks_not_flushed | 0       |
>> | Key_blocks_unused      | 357751  |
>> | Key_blocks_used        | 3820    |
>> | Key_read_requests      | 2170473 |
>> | Key_reads              | 11343   |
>> | Key_write_requests     | 6339    |
>> | Key_writes             | 1693    |
>> +------------------------+---------+
>
> You definitely have a high enough read hit rate to indicate that all
> (or almost all) of your index blocks are cached.  You could probably
> bring key_buffer_size down to 32M, which leaves room for growth.
>
> --
>        Dan Nelson
>        dnelson@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

emm, I should follow key_read and key_read_requests.

Thanks very much!

-- 
Regards,
Wang Yi
Thread
How many key buffer size should be allocate?Yi Wang8 Sep
  • Re: How many key buffer size should be allocate?Dan Nelson8 Sep
    • Re: How many key buffer size should be allocate?Yi Wang8 Sep
    • Re: How many key buffer size should be allocate?David Ashley8 Sep