MySQL Forums
Forum List  »  MyISAM

Re: 5% of spare memory key buffer big enough?
Posted by: Rick James
Date: August 19, 2012 01:41PM

| Key_read_requests | 399516496499 |
| Key_reads | 223702306 |
| Key_write_requests | 4,692,155,028 |
| Key_writes | 1,711,169,289

Excellent read ratio. That is, virtually every _index_ fetch is found in the key_buffer and does not need I/o.

The write ratio is not as good -- 3 index block updates lead to 1 I/O.

> would there still likely be some benefit to increasing the size?
What is the total of the Index_length for all the MyISAM tables? Make key_buffer_size a little bigger than that -- but _not_ more than, say, 25% of RAM. (I generally agree with Aftab, but am phrasing it differently.)

> mysql would need to be restarted after changing key_buffer_size
I'm pretty sure it will change immediately when you do SET @@GLOBAL.key_buffer_size = ... However, also make the change in my.cnf (my.ini) so that it won't be lost at the next restart.

> 5% of spare memory key buffer big enough?
It's not that simple. If the 'working set' is all of all indexes, then you need room for all the index blocks. If the working set is only a small part of the indexes, then you can efficiently use a much smaller key_buffer_size.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 5% of spare memory key buffer big enough?
1814
August 19, 2012 01:41PM


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.