On Fri, 28 Nov 2003 23:14:14 +0000 (UTC), "David Brown"
<dc**@dcb7.dyndns.org> wrote:
"David Brown" <dc**@dcb7.dyndns.org> wrote in message
news:bp**********@hercules.btinternet.com... With MySQL is it possible to have indexes or tables held in RAM? If so how
do I ensure that updates are copied to disk?
Given the amount of response I'll take that as a No.
Indexes are automatically cached in memory.
To control the size of the index cache, you set the "key_buffer_size"
in the my.cnf file. In mine, where the system has 1 gig of ram, I've
got:
set-variable = key_buffer_size=128M
The index cache is shared among all mysql threads.
To see how efficiently the indexes are being cached, do
show status like 'key%'
Key_reads is the number of times mysql has had to read index data from
disk. Key_read_requests is the number of times it needed index data.
Divide key_reads by key_read_requests to get the cache miss rate.
An example: On my server, I've got 157603570 key read requests, and
86731 key reads. That's a miss rate of 0.055%, So, 99.945% of the
time, MySQL is getting the index data from memory.
Tables can also be cached, but generally their large size makes this
not very useful. Also, each "thread" of mysql has it's own table
cache, so you need to keep the table cache small, or the system will
run out of memory, and start swapping memory to disk, completely
killing system performance.
You can create an in-memory (heap) table, but generally it's not
useful except for small data sets. And you need to handle storing it
to disk manually in your application.
If you've got a small table and want it to be an in-memory table, then
you could change the type to "heap". But, heap tables don't support
some features of other table types, so you need to read up on heap
tables. And before stopping mysql, you'd need to be sure to convert
the heap table back to a normal stored type, or you'd lose any
changes.
Chuck Gadd
http://www.csd.net/~cgadd/aqua 
