Re: What happens if the size of an index file exceeds physical RAM?
Good question. When we talk about key cache performance we usually look at these two status variables.
1. Key_read_requests
The number of requests to read a key block from the cache.
2. Key_reads
The number of physical reads of a key block from disk.
There is a valid reason to examine Key_reads, because we know that disks are very slow relative to RAM but the Key_reads aren't always physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read, you will be lucky if it is cached, then it's just a system call. Having said that it is always good to minimize key_reads which may cause randon disk I/O.
The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1.
Finally I'll like to show you something partially useful you can do with Key_reads:
[xxxxxx ~]$ mysqladmin -uroot -p ext -ri10 | grep Key_reads
Enter password:
| Key_reads | 44605148 |
| Key_reads | 4 |
| Key_reads | 4 |
| Key_reads | 13 |
| Key_reads | 9 |
| Key_reads | 6 |
| Key_reads | 20 |
| Key_reads | 6 |
| Key_reads | 11 |
This server is doing approximately 7 Key_reads every ten seconds but it is hard to say that how many of them are random I/O and how many are just system calls (i.e. read from operrating system's cache)
Edited 1 time(s). Last edit at 07/23/2010 03:22AM by Aftab Khan.
Subject
Views
Written By
Posted
3726
July 22, 2010 12:01PM
Re: What happens if the size of an index file exceeds physical RAM?
1803
July 23, 2010 03:19AM
2149
July 28, 2010 12:25AM
1485
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.