How to move MySQL storage to RamFS or TmpFS partition

Whether moving all MySQL storage to a tmpfs helps with speeding it up or not is questionable but I needed to do for some testing purposes, so this is a short overview of how I did that hopefully will be useful:

First mount tmpfs to a folder:

sudo mkdir /var/ramfs
sudo mount -t ramfs -o size=1G ramfs /var/ramfs/

Here I 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:

sudo /etc/init.d/mysql stop
sudo cp -R /var/lib/mysql /var/ramfs/
sudo chown -R mysql:mysql /var/ramfs/mysql

Tweaking MySQL config:

sudo cp /etc/mysql/my.cnf /etc/mysql/original-my.cnf
sudo vim /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

Next step is to tune apparmor settings:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the following few lines just before the closing curly braces:


/var/ramfs/mysql/ r,
/var/ramfs/mysql/*.pid rw,
/var/ramfs/mysql/** rwk,

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


sudo /etc/init.d/apparmor restart
sudo /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)

That’s pretty much it :)

8 thoughts on “How to move MySQL storage to RamFS or TmpFS partition

  1. Pingback: How to move MySQL storage to RamFS or TmpFS partitionAntarctic … | mysql

  2. Pingback: Move your mysql to RAM, PoorMan SSD | bloody error

  3. Thanks for this tutorial. It worked flawlessly. Now my database-involving integration tests are 100 times faster :)

  4. That was a superb article. Before that writing a local dump into my mysql database (on SSD) took 50s. With the mysql on the RAMFS it only takes 20s. Additionally my database is more responsive and development is smoother.

    But I took this article to the next level.

    As a RAMFS is not persistent it is gone after restarting the PC (obviously). But I need the database right after the start and at best in the state I left it the day before. So I wrote two additional scripts. One for backing up the RAMFS to my SSD on computer shutdown or reboot. And the second script on startup for writing the backup back to the RAMFS.
    That way I can start working right away.

  5. After the procedure, when I want to run mysql, it throws an error “Job failed to start”.
    Do you have any recommendation to fix it?

Leave a Reply to Andrey Shestakov Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>