List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 10 1999 9:19pm
Subject:Storing tables in memory (Was : do the fields not in an index
slow queries ?)
View as plain text  
>>>>> "chas" == chas  <panda@stripped> writes:

>> 
>> Generally the smaller you can make your tables, the faster the queries
>> go. The trick to table design is to fit the entire table or a large
>> portion of it into RAM. RAM somewhere in the order of 1000 faster to
>> access than disk, so the difference between having your data entirely in
>> RAM and only partially, the other part being on the disk is someting the
>> difference between going to a store 5 minutes away to buy milk and
>> driving to New York from Calfornia to accomplish the same.

chas> Thank you Sasha,

chas> This raises a topic that I've been very interested in - keeping entire
chas> tables in memory as well as caching queries. This seems to be common practice
chas> by the big search engines (with GB of RAM).   I've scoured section 10.1
chas> of the MySQL reference manual several times, looking for the exact syntax 
chas> for this but I can't seem to find it. I had suspected that it was the
chas> KEY_BUFFER
chas> settings but in a previous post, Monty said :

chas> [snip]

chas> Everything depends on the queries. The record buffer is only used when
chas> MySQL is forced to read sequentially through the hole table.

chas> You will normally not get any more performance using a record buffer of
chas> more than 1M or a sort_buffer of more then 8M.

chas> If the queries are slow, it normally means that MySQL can't find a
chas> suitable index for your queries.

chas> [/snip]
 

chas> Basically, how does one say to MySQL "always store table X in memory"

chas> and how can we check that it is actually doing this ?
chas> in fact, is there any way to check what MySQL is actually doing (holding
chas> in memory) at any one instant in time ?

chas> cheers,

chas> chas

Hi!

MySQL lets the OS cache the rows while MySQL only caches the key usage.
This makes it impossible to determinate what is cached and what isn't.

MySQL 3.23 supports HEAP (in memory) tables, which will are very fast,
but as these are not saved on disk they are mostly suitable as
temporary tables.

Regards,
Monty
Thread
MySQL installationR-II3 May
  • do the fields not in an index slow queries ?chas9 May
    • Re: do the fields not in an index slow queries ?Sasha Pachev9 May
      • Storing tables in memory (Was : do the fields not in an indexslow queries ?)chas9 May
        • Storing tables in memory (Was : do the fields not in an indexslow queries ?)Michael Widenius11 May
    • PHP and last inserted row: Never MindVan9 May
  • MySQL installationJani Tolonen11 May