Re: What happens if the size of an index file exceeds physical RAM?
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.
Subject
Views
Written By
Posted
3708
July 22, 2010 12:01PM
1795
July 23, 2010 03:19AM
Re: What happens if the size of an index file exceeds physical RAM?
2142
July 28, 2010 12:25AM
1475
September 06, 2010 03:15PM
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.