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
