Force an entire MySQL database to be in memory
Create the database in /dev/shm
(ubuntu|debian) and it will be in RAM. It can grow up to 0.5 of available memory.
As dtmilano said, you can put it on a tmpfs mounted filesystem. It doesn't have to be /dev/shm, but that is one place where tmpfs is usually mounted.
You can create a new one anywhere, though:
mount none -t tmpfs /path/to/dir
If it fills all your available RAM, it will use swap as a backup.
Put it in /etc/fstab to re-mount on boot. Just remember, it's a ram disk, so it starts out empty every time you reboot. See: http://www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs
Alternately, as suggested by yuxhuang you can create a table of type MEMORY
. It also empties on restart, though the table definition remains. The MEMORY
table type has a few restrictions, though. It uses fixed-size rows, for example, so text
and blob
columns are not allowed, and varchar
isn't variable length. See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
SET storage_engine=MEMORY;
This is going to set the default storage engine for the current session.