Devoting RAM to specific tables in a MySQL database

Question

Is there any way that I can force MySQL to store specific tables in the RAM (other than using the memory storage engine)?

My database contains +200 GB in InnoDB tables (which I don't want to be stored in the RAM) and 10 tables (~100 MB) which uses the MyISAM engine.

My goal is to obtain the fastest possible acces to the MyISAM tables (if necessary I can change the storage engine of the MyISAM tables or move them to another db on the same server).

My db-server is running Linux and has 10 GB RAM.

Answer 1

Assuming the following:

• datadir is /var/lib/mysql
• your database is called mydb
• you have MyISAM tables t1, t2, and t3 in mydb
• you want to create a memory database called memorydb

You could the following:

• Step 01) service mysql stop
• Step 02) Create a RAM Disk
• Step 03) mkdir /var/lib/mysql/memorydb
• Step 04) chown mysql:mysql /var/lib/mysql/memorydb
• Step 05) mount the RAM disk onto /var/lib/mysql/memorydb
• Step 06) service mysql start
• Step 07) mysql> ALTER TABLE mydb.t1 RENAME memorydb.t1;
• Step 08) mysql> ALTER TABLE mydb.t2 RENAME memorydb.t2;
• Step 09) mysql> ALTER TABLE mydb.t3 RENAME memorydb.t3;

After step 6, memorydb should be registered in information_schema.schemata

If you want to keep copies of the table back in mydb, do the following after step 6

• Step 07) mysql> CREATE TABLE memorydb.t1 LIKE mydb.t1;
• Step 08) mysql> INSERT INTO memorydb.t1 SELECT * FROM mydb.t1;
• Step 09) mysql> CREATE TABLE memorydb.t2 LIKE mydb.t2;
• Step 10) mysql> INSERT INTO memorydb.t2 SELECT * FROM mydb.t2;
• Step 11) mysql> CREATE TABLE memorydb.t3 LIKE mydb.t3;
• Step 12) mysql> INSERT INTO memorydb.t3 SELECT * FROM mydb.t3;

Just make sure you have enough RAM to hold your MyISAM tables.

Give it a Try !!!