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]
