Is it possible to load a database in the RAM?

Question

I want to load a MYSQL-database into the RAM of my computer, is there a way to do this? I am running this database under Linux. Also, if this is possible, is there a good way to make backups, because if the computer is unexpectedly shut down, I would lose all my data.

Answer 1

absolutely, for example under linux you can mount your database in a tmpfs

Answer 2

Yes, you can use the MEMORY engine. As far as backups, it's your call. You never said, e.g. how often you want to store to disk. But you can use traditional MySQL replication or your own solution.

Answer 3

If you buffer pool is big enough, you data is -- effectively -- an in-memory database with a disk backup copy. Don't fool around with RAM databases, simply make the buffer pool size as large as you can make it.

Read this:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

Answer 4

If you're using innodb tables then I recommend adjusting the buffer pool size like S.Lott suggested above. Make it 110% or so of your database size if you have the ram.

If your database is > 50mb you'll also want to look at increasing the innodb_log_file_size. See http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_log_file_size Perhaps to around 25 - 50% of your buffer pool size, but 1gb max.

The innodb_log_file_size is a bit tricky to adjust. You need to shut the db down, move the current log files into a backup location, and let mysql recreate them when it's restarted (i.e. after you've changed the values in my.cnf). Google it and you'll find some answers.