List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 8 2008 4:47am
Subject:Re: How many key buffer size should be allocate?
View as plain text  
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
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