Speeding up MySQL for testing or development

Four years ago I gave a talk about how we made a 15+ minute test suite run in just over a couple of minutes. It was mainly about Perl, although there were bits involving the MySQL side of things.

Since that time, my team has added many more tests to the suite, but we have actually continued optimizing, bringing the runtime down more. We also did a hardware change recently, moving from our colocated servers to bigger and faster GCP spot instances (t2d are the fastest as my testing has shown), with which we finally approached the half minute runtime:

                                  Yath Result Summary
----------------------------------------------------------------------------------------
     File Count: 146
Assertion Count: 51338
      Wall Time: 33.97 seconds
       CPU Time: 322.32 seconds (usr: 7.79s | sys: 0.19s | cusr: 262.65s | csys: 51.69s)
      CPU Usage: 948%
    -->  Result: PASSED  <--

It was so close to the 30 second mark, that, as any normal person would (not), I immediately started work on trying to break it.

The profiler was quite clear: after years of optimizations, there were no more obvious bottlenecks except the actual database calls, especially for the tests where we had to create temporary DBs. As mysql was local and fine-tuned already, the only thing I could think of was speeding up the actual disk I/O. We were already using a fast SSD type of course, but why not go drastically faster? What if I just mounted the entire data directory to RAM? This was a testing server, the db is reset on every spot instance we launch anyway. Admittedly, I hadn't tried using a ramdrive since the hard disk era, but it was worth at least a try.

So, there are a few ways you can go about mounting your mysql data on a ramdrive. Let's say you want to bake it into the actual OS image, then one way is to install mysql, initialize the desired DBs, stop mysql and move the data directory to a backup location:

mv /var/lib/mysql /var/lib/mysql.bak

You can then install a systemd service that creates a ramdrive on boot and copies that backup to it, and set it as a requirement for the mysql service. Example config:

# /etc/systemd/system/mysql_ramdisk.service:
[Service]
Type=oneshot
User=root
Group=root
ExecStart=/bin/mount --types tmpfs --options rw,noatime,mode=1777,size=6g,nr_inodes=5K --source tmpfs --target /var/lib/mysql
ExecStart=/bin/cp -rp /var/lib/mysql.bak/. /var/lib/mysql/

[Install]
RequiredBy=mysql.service

In my case, as I wanted more immediate control/tweaking, I decided to instead create the ramdrive on the test suite runner script. The relevant part goes a bit like this:

# Create the backup the first time the script is run on a server
unless (-e $mysql_data_backup) {
    system "sudo systemctl stop mysql";
    system "sudo mv /var/lib/mysql /var/lib/mysql.bak";
}

# Check for mountpoint, create ramdrive if required
my $check = `grep '/var/lib/mysql ' /proc/mounts`;
unless ($check =~ m#/var/lib/mysql#) {
    system "sudo systemctl stop mysql";
    system "sudo mkdir /var/lib/mysql" unless -e '/var/lib/mysql';
    system "sudo chown mysql:mysql /var/lib/mysql";
    system "sudo mount -t tmpfs -o size=6g tmpfs /var/lib/mysql";
    system "sudo cp -rp /var/lib/mysql.bak/. /var/lib/mysql/";
    system "sudo systemctl start mysql";
}

So, the first time a server gets to run our test suite, it takes a few extra seconds to create the ramdrive and copy the ~5GB db to it. On subsequent runs it does nothing.

After all this, I tried running the same test suite:

                                  Yath Result Summary
----------------------------------------------------------------------------------------
     File Count: 146
Assertion Count: 51465
      Wall Time: 25.95 seconds
       CPU Time: 331.70 seconds (usr: 8.38s | sys: 0.17s | cusr: 268.70s | csys: 54.45s)
      CPU Usage: 1278%
    -->  Result: PASSED  <--

We went from 34 seconds to 26 seconds runtime. That's almost a 25% speed improvement on an otherwise very highly optimized test-suite, with just one simple trick!

SSDs are fast, but they still can't compete with RAM. If you have an I/O bottleneck on a dev or a testing system where you don't care about persistence and can spare the RAM, give the ramdrive a try.