Alex Rivera | Logout

Force an entire MySQL database to be in memory

Asked 2011-02-04T05:26:39.600
10

For the purposes of running a large number of tests that interact with the database, I want to do two things:

  1. I would like to copy the schema of a database without copying its data. I can do this with a script that grabs the CREATE TABLE statements from each table in the database.

  2. Upon creating this database, I would like to force it to be 100% in memory.

I'm stuck on how to do part 2 - Is there an easier way to do this other than specifying each table's engine? Somehow that seems like a poor way of doing it.

mysql database linux unit-testing
Edit
Report

2 Answers

11

Create the database in /dev/shm (ubuntu|debian) and it will be in RAM. It can grow up to 0.5 of available memory.

answered 2011-02-04T05:36:02.423
2

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

answered 2011-02-04T06:41:06.200

Your Answer