PolicyStat's Dev Blog

Run MySQL From a RAM Disk in Ubuntu Linux

Here at PolicyStat, we heavily rely on unit tests, selenium tests and continuous integration to keep our quality up so that we can practice continuous delivery. That means we write a lot of tests and we run a lot of tests. A major source of frustration is the painfully bad MySQL performance during tests because of table creation overhead. One option we considered was to use the Memory storage engine in MySQL, but you lose some capabilities (blob and text columns) and you’re no-longer testing your actual database.

Yesterday, I finally bit the bullet and figured out how to configure MySQL to run on a ramdisk.

Results

RAMdisks are fast, but we really only saw big performance improvements when compared to an ext4 filesystem on a conventional hdd. Compared to solid state disks with XFS or ext3, the RAMdisk only gave marginal performance improvements

I ran our full nosedjango-based test suite of 1340 tests on our ec2-based Hudson slaves. One of the slaves had the RAMdisk fix and the other was just using standard ec2 ephemeral storage with an ext3 filesystem.

  • ext3- 4117 seconds
  • RAMdisk- 3650 seconds

The RAMdisk gave us an 11% performance improvement. Our test suite relies very heavily on fixtures, so this tells me that our test suite is basically CPU-bound. Results on ext4 would be more dramatic and so would results on I/O-bound test suites.

Caveats

These instructions worked on two different Ubuntu 10.04 machines, but I haven’t tried it on other distros/versions. If someone can try this on another version and let me know the results, I would love to update the instructions. Also, you should understand what a RAMdisk actually is before proceeding. The main points are that your data will be lost on restart and if you don’t actually have free RAM, you won’t get much of a benefit.

Instructions

After a lot of unfruitful googling, I found this stackoverflow answer with some simple instructions. They got me most of the way there, but didn’t quite work on my system.

The following instructions got a working MySQL instance running on a RAMdisk.

1 Stop MySQL

We’re going to be copying the raw mysql data files, and we need them in a consistent state.

$ sudo service mysql stop

2 Copy your MySQL data directory to the RAMdisk

By default, all of MySQL’s data is stored in /var/lib/mysql and that’s the folder that needs to be fast. Ubuntu has a RAMdisk located at /dev/shm by default, so we’re going to use that. We also want to preserve the permissions on the files so that MySQL can access them.

$ sudo cp -pRL /var/lib/mysql /dev/shm/mysql

3 Update your mysqld configuration

Now we need to tell mysqld to actually use our new data directory. This setting is “datadir” located in /etc/mysql/my.conf under the [mysqld] section. Change yours to:

# datadir = /var/lib/mysql
# Using a RAMdisk
datadir = /dev/shm/mysql

4 Update your apparmor profile

AppArmor is great for keeping programs isolated for security purposes, but it also means that seemingly-small changes can cause AppArmor to break your program. By default, the mysql-server install comes with an AppArmor profile that locks mysqld to a specific set of files. /dev/shm/mysql isn’t in the default profile (obviously), so we need to add it.

First, open /etc/apparmor.d/usr.sbin.mysqld with your favorite text editor:

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

Then add the following lines inside the “/usr/sbin/mysqld” section (between the braces):

/dev/shm/mysql/ r,
/dev/shm/mysql/** rwk,

5 Restart apparmor and MySQL

And if everything has gone well, we just need to restart our services and get on to our much-faster testing.

$ sudo service apparmor restart
$ sudo service mysql start

Results

If you’re using ext4 for your hard drive, you should see a *huge* performance improvement with a ramdisk. Christian’s single selenium testcase run went from several painful minutes to 17 seconds. ext4 is a bit more paranoid about ensuring that changes are actually flushed to disc versus ext3, so you see a very large performance hit doing database and table creation. That means the gains from a RAMdisk are more dramatic.

TODO: I have plans to create an upstart script to manage the process of copying data to the RAMdisk on every boot, but for now you’ll need to do that manually every reboot.

Troubleshooting

Not enough RAM (ibdata1 file is too big)

By default when using innodb, the /var/lib/mysql/ibdata1 file grows and grows, even after you delete all of your database. In our case, Christian’s ibdata1 file was >300MB for no particular reason. This stackoverflow question explains how to shrink your ibdata1 file

Comments