MySQL Forums
Forum List  »  MyISAM

Re: What happens if the size of an index file exceeds physical RAM?
Posted by: Rick James
Date: July 28, 2010 12:25AM

In MyISAM, _index_ blocks are cached in the "key_buffer". _Data_ blocks are cached by the OS elsewhere. So, those two caches need to share RAM. Recommend 20% of RAM for key_buffer_size, regardless of the size of the index.

Since the key_buffer is a "cache", it loads and purges blocks as needed.

Example 1: You have a huge index, but it is on a DATETIME, TIMESTAMP, or AUTO_INCREMENT, and you are only looking at 'recent' items. In that case, only blocks near the end of the index are needed. So, it does not matter how big or small your key_buffer is relative to the total index size. Key_reads/Key_read_requested will be close to 0.

Example 2: You have a huge table, with an index on a GUID or MD5. Or you are otherwise jumping around a lot. In these cases, if the index does not fit in the key_buffer, then generally every reference to that index (for INSERT/UPDATE/DELETE/SELECT) will involve a fetch from disk. This will be slow. Key_reads/Key_read_requested will be close to 1.

Aftab points out a good way to see whether your situation is closer to Example 1 or Example 2.

All indexes for all MyISAM tables share the key_buffer. So the real situation is more complicated. But the ratio boils it down to a single number.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: What happens if the size of an index file exceeds physical RAM?
2142
July 28, 2010 12:25AM


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.