On Mon, Sep 8, 2008 at 12:47 PM, Dan Nelson <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
emm, I should follow key_read and key_read_requests. Thanks very much! -- Regards, Wang Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
