How can I be sure the whole MySQL DB is loaded in memory?

Question

I am running a mysql server. I would like to somehow make sure that the whole DB is loaded into the ram as I heard it would be alot faster.

Is this true? and how do I vertify it?

Answer 1

Well, one option is to use a ramdrive.. though it's not permanent.

http://www.linuxforums.org/forum/misc/670-how-create-ram-drive-linux.html

Answer 2

If you are using InnoDB, then you can set innodb_buffer_pool_size to be at least as large as your data and indexes. Then as you access data, it will be cached into memory and all subsequent accesses will occur via memory.

If you wanted to pre-cache the data, you could do some of your common queries, or full table/index scans to force it to load all of the data into memory.

There is an init-file option for mysqld which can have the server automatically execute some commands on startup, where you could include the pre-caching queries to execute.

Keep in mind you don't want to run out of memory, so be careful with setting innodb_buffer_pool_size too large.

Answer 3

(Just for completeness,) You could use HEAP as table engine. But keep in mind that whatever data you put in a HEAP table is gone when you reboot the server.

Answer 4

Tables in RAM are very fast.

You can change your existing tables by altering their storage engine. But make a backup if you want to use the data after a server restart.

ALTER TABLE test ENGINE = MEMORY;

Keep in mind, that MEMORY tables cannot contain BLOB or TEXT columns and data and index size is limited to available memory.