This is useful not only as a personal productivity improvement but can also improve your team workflow. If tests are easier to run then developers are more likely to detect build failures themselves. This saves some back-and-forth between them, reviewers and the continuous integration server and should help you merge changes faster.

A Bit of Theory

In order to maintain ACID guarantees databases need to ensure data was written to disk before completing a transaction. It’s essential in production but becomes a burden during development. If we could reduce the number of disk writes we might be able to speed up tests considerably.

Some databases provide proprietary in-memory capabilities but there’s a generic method applicable to any database system. The idea is to configure the database to store the data directory on an an in-memory file system (tmpfs on Linux) to store the data. It would also be nice to persist the data accross reboots. Otherwise recreting it over and over again would be enough of a burden to erase any productivity benefits.

This is a two-step process: set up the file system and point the database to it. Let’s take a look how to do that on Linux and MySQL.

Practice

I assume we’re using MySQL on Linux with systemd. I’ll cover macOS and PostgreSQL in a later article and won’t cover Windows at all (sorry!).

Before we start messing up with the database let’s stop it with sudo systemctl stop mysqld.

Step 0: Preparations

On my Arch Linux, MySQL stores data in /var/lib/mysql. You can look for datadir in my.cnf to find the path on your machine. Before making any changes please back up that directory.

We’ve already shut down MySQL so we can rename /var/lib/mysql to /var/lib/mysql-persistent (you can pick any other name as long as you use it in the subsequent commands). This directory will persist changes across reboots. The original directory is now gone so let’s reconfigure it as a mount point for tmpfs.

Step 1: Configuring the File System

Let’s create a file named /lib/systemd/system/var-lib-mysql.mount with the content below. The file name must correspond to the full path name of the mount point with slashes replaced with dashes. If your data directory is located somewhere else remember to reflect that in the name of the unit file.

Code speaks louder than words so let me just show you the file with some explanatory comments:

[Unit]
Description=The temporary file system for MySQL data directory.

[Install]
# Ensure the file system is mounted before starting MySQL.
WantedBy=mysqld.service

[Mount]
# This is a memory file system so there's no need to specify a device.
What=none

# The path to datadir. Remember to reflect that path in the name of the unit.
Where=/var/lib/mysql

Type=tmpfs

# We not only need to provide the size but also uid and gid. Otherwise MySQL
# will complain that the data directory is owned by someone else (e.g. root).
Options=size=1G,uid=mysql,gid=mysql

We now need to enable the unit, reload systemd configuration and mount it:

sudo systemctl enable var-lib-mysql.mount
sudo systemctl daemon-reload
sudo systemctl start var-lib-mysql.mount

We should now see the new mount point in the output of mount.

Step 2: Configuring MySQL

The last step is modifying the MySQL service unit to make it restore and dump data after starting and before stopping respectively. Before that ensure you have rsync installed as we’ll use it to copy files

We need to add two lines to the Service section of mysqld.service (lines broken for readability):

ExecStartPre=/usr/bin/rsync --archive --recursive --delete
                            /var/lib/mysql-original/ /var/lib/mysql/
ExecStopPost=/usr/bin/rsync --archive --recursive --delete
                            /var/lib/mysql/ /var/lib/mysql-original/

The only difference between the commands is the directories are passed in reversed order.

After making these changes we need to reload the configuration one more time and, lastly, start MySQL:

sudo systemctl daemon-reload
sudo systemctl start mysqld

Results — The Good and The Bad

When I implemented this optimization for the first time I hoped to speed up a Cucumber test suite. It used to take over 12 minutes to finish on my machine. To my surprise it reduced that time down to 2 minutes 30 seconds (an almost five-fold improvement!). That’s the good news. The bad news is RSpec performance is unaffected. It might be less than I expected but it’s still a great productivity boost especially if applied company-wide.

What’s Next?

There are some natural follow up steps and questions:

• Apply the technique to PostgreSQL.
• Port to macOS.
• Check whether Capybara (not only Cucumber) test performance improves as well.
• Why is RSpec performance unaffected?

I’ll address some of these points in future articles. For now, I encourage you to give this a try on your development machine and enjoy a faster test suite. If you have any questions, suggestions or ideas feel free to drop me a line at.