Force an entire MySQL database to be in memory

Question

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

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.
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.

Diego Torres Milano · Accepted Answer · 2011-02-04 05:36:02Z

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 Feb 4, 2011 at 5:36

Diego Torres Milano

69.9k9 gold badges116 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fragsworth Over a year ago

This sounds good, but is there a similar solution for CentOS?

Diego Torres Milano Over a year ago

Check if you have it: $ mount | grep shm. Otherwise, you can create a filesystem on one of /dev/ram* and mount it somewhere.

MarkR Over a year ago

Centos 5.0+ always has /dev/shm mounted. However, you can mount a tmpfs anywhere you like. tmpfs has been available for some time.

Cmag Over a year ago

guys, dont know if its OK, just tried : mv /usr/local/mysql/data/blah /dev/shm.... ln -s /dev/shm/blah -> data/blah.... restarted mysql... its flying

tylerl · Accepted Answer · 2011-02-04 06:41:06Z

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

yuxhuang · Accepted Answer · 2011-02-04 05:32:10Z

1

SET storage_engine=MEMORY;

This is going to set the default storage engine for the current session.

answered Feb 4, 2011 at 5:32

yuxhuang

5,3891 gold badge20 silver badges13 bronze badges

3 Comments

The Scrum Meister Over a year ago

There is a limit how large a MEMORY table can be. it is doubtful (but possible) that all the tables in a db will be able to be MEMORY tables

MarkR Over a year ago

Memory tables still have their metadata stored on disc, and they don't behave the same as the other engines.

Denilson Sá Maia Over a year ago

Further expanding @MarkR comment: MyISAM and Innodb are already very different and can lead to different behavior, leading to bugs that only happen to one engine. Thus, I recommend NOT changing your database engine to MEMORY, as it will very likely introduce engine-specific bugs.

fsinisi90 · Accepted Answer · 2016-02-11 17:19:28Z

1

If you are using Windows, in your database creation script you can create the tables adding the MEMORY parameter like this:

CREATE TABLE IF NOT EXISTS `user` (
  `id` varchar(23) NOT NULL,
  `username` varchar(250) NOT NULL,
  ...
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

answered Feb 11, 2016 at 17:19

fsinisi90

1,1681 gold badge17 silver badges51 bronze badges

Collectives™ on Stack Overflow

Force an entire MySQL database to be in memory

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related