MySQL Forums
Forum List  »  MyISAM

5% of spare memory key buffer big enough?
Posted by: Josh Irish
Date: August 15, 2012 03:53AM

Hi All,

I'm looking into optimizing my companies mysql server, and one of the first places I've looked at is the key buffer size. At the moment it's set to 400MB, and according to the server there is generally about 10GB of memory free, so that's less than 5%, and from what I read it's generally a good idea to have it between 25 and 50%

It's a pretty large database, 200GB+, and some of the indexes on single tables are over 1GB, so I expected the low key buffer size to adversely affect performance, but I checked the stats for them and they don't seem that bad:

| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 484 |
| Key_blocks_used | 319666 |
| Key_read_requests | 399516496499 |
| Key_reads | 223702306 |
| Key_write_requests | 4692155028 |
| Key_writes | 1711169289

(I'm not overly familiar with these stats so perhaps I'm interpreting them incorrectly)

I realise the ideal size is largely dependant on the data and usage of the database, which is extremely varied, but would there still likely be some benefit to increasing the size?

Also my our system administrator says mysql would need to be restarted after changing key_buffer_size, but I've not seen anything that states that, is it the case? (Server version is 5.5.1)


Options: ReplyQuote

Written By
5% of spare memory key buffer big enough?
August 15, 2012 03:53AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.