List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:March 21 2001 3:52pm
Subject:Re:How to load a database in memory
View as plain text  
Pascal,

>Hi all,
>I use Mysql to store session's information.
>My problematic is to be very performant...
>So, I'm thinking about solutions to make the accesses faster.
>I would like to try to cache my database ("load it in memory"). 

>I found some interesting information in the documentation :
> - HEAP table allows to cache data in memory
> - INNOBASE seams to permit to cache data too. 

if you have enough memory in your computer and specify 
innobase_buffer_pool_size big enough to hold your data and indexes,
then your data will stay in memory. Innobase uses the 'Least Recently Used'
replacement algorithm to decide which database pages should stay in the
buffer pool.

But, currently you cannot 'fix' an Innobase table in memory,
'fix' meaning that the table would stay in memory even if you
are accessing other, big, tables and the LRU algorithm would normally
push your table out of the buffer pool. Fixing a table in memory
is useful in some real-time applications where you have to be able
to give an upper bound for the time used by some specific database
operation. But, in normal database applications the LRU algorithm
is the best: you do not win anything by fixing a table in memory.

>I read that "HEAP tables use a hashed index and are stored in memory. This
>makes them very fast, but if MySQL crashes you will lose all data stored in
>them. HEAP is very useful for temporary tables!".
>INNOBASE table seems to be safer. 

Yes, Innobase writes to the log all your updates and inserts to the table
when you commit your transaction. After that your updates and inserts cannot
be lost.

If your table fits in the buffer pool, Innobase will internally build
hash indexes to your data based on the (B-tree) indexes you have defined
for your table. Queries will be very fast then.

>Does this table type permit to load data in memory ?
>What type should i better use ?
>Is it recommanded to compile MySQL with the two table types (in ordrer to be
>very performant ?)Could someone give me a little more details ? Thanks a lot. 

Regards,

Heikki Tuuri

>Regards,
>-- Pascal Thivent

Thread
How to load a database in memoryPascal THIVENT19 Mar
Re:How to load a database in memoryHeikki Tuuri21 Mar