MySQL storage on RamFS or TmpFS partition

Mount tmpfs to a folder:

# mkdir /var/ramfs

# mount -t ramfs -o size=1G ramfs /var/ramfs/

Here we mounted ramfs to /var/ramfs. I am using ramfs in oppose to tmpfs mainly because:

    ramfs grows dynamically(tmpfs doens’t)

    ramfs doesn’t use swap(while tmpfs does)

RAM-backed file system is mounted, so now I need to populate it with MySQL files for processing.
To do that I will need to stop mysql, copy it’s database files over to ramfs, adjust AppArmor and MySQL settings and start mysql server again. Here is the chain of commands to do that:

Copying files:

# /etc/init.d/mysql stop

# cp -R /var/lib/mysql /var/ramfs/

# chown -R mysql:mysql /var/ramfs/mysql

Tweaking MySQL config:

# cp /etc/mysql/my.cnf /etc/mysql/original-my.cnf

# vi /etc/mysql/my.cnf

Find line with ‘datadir‘ definition(it will look something like datadir = /var/lib/mysql) and change it to

datadir = /var/ramfs/mysql

Looks like we’re done with settings, let’s see if it will work:

# /etc/init.d/mysql start

If mysql daemon starts(double check /var/log/mysql.err for any errors) and you can connect to it, mostlikely now we’re running fully off of a RAM device. To double check it, run this from mysql client:

mysql> show variables where Variable_name = 'datadir' \G
*************************** 1. row ***************************
Variable_name: datadir
Value: /var/ramfs/mysql/
1 row in set (0.00 sec)

The real reason you don't see anyone wanting to run MyISAM from a tmpfs is because MySQL has the EMORY engine for that purpose.  If you want to keep a database in memory, that's the correct way to do it.  Someone actually wanting to do a memory database would start with converting the handful of TEXT and BLOB fields in the schema over to VARCHARs, since the MEMORY engine doesn't support either.

Disadvantages of Ramfs and Tmpfs:

Since both ramfs and tmpfs is writing to the system RAM, it would get deleted once the system gets rebooted, or crashed. So, you should write a process to pick up the data from ramfs/tmpfs to disk in periodic intervals. You can also write a process to write down the data from ramfs/tmpfs to disk while the system is shutting down. But, this will not help you in the time of system crash.