Boost MySQL speed of Magento 2 integration tests

TL;DR

Simply run 2 docker commands to run MySQL entirely in tmpfs. See the commands below. And then modify your Magento configuration to point to this database. Done.

Setting up integration tests

This blog assumes that you have already setup Magento for running integration tests: You have modified the phpunit.xml file, added your database settings to the install-config-mysql.php file, etcetera. There are already excellent blogs out there, helping you with this setup.

What these blogs mention a lot of times is that you can speed up the running of tests by disabling the flag TESTS_CLEANUP in the phpunit.xml file. However, this was not good enough for me. Still, running the tests was taken too much time. So I went ahead into tuning MySQL to the max.

Tuning MySQL is not a huge benefit

Tuning MySQL is simple: The more memory you assign to it, the faster MySQL runs. However, tuning InnoDB or the query_cache (though it is deprecated, it is still really powerful in development environments) are assuming that there needs to be a fine balance between performance and database integrity.

However, with integration tests, this is a different story: The more performant the database, the more production you become. And we don't care about database integrity: Once the tests are run, the database could be thrown away. It is just a temporary resource, only needed while testing.

This leads to the conclusion that if all of MySQL is run in memory, performance is at a maximum. This can be done by either moving the MySQL folders (in my case, /var/lib/mysql) into a RAM disk or into tmpfs.

Using a docker image for tmpfs

Moving MySQL to tmpfs is possible. However, on the same system where I'm running integration tests, I'm also running my development environments, with databases that should stick. I quickly decided that I wanted to use Docker for this.

As a developer, you always try out things yourself. So I went ahead with creating a plain Dockerfile, adding MySQL to it, swapping it out with a tmpfs-based filesystem, disable AppArmor because it was preventing things from working.

However, after doing all of this, I found out that the official MySQL images for Docker already support this behaviour out of the box. Bummer. So here are the instructions to get going with the official MySQL Docker.

Creating a network - for a static IP

While creating the actual container only requires 1 single command, I quickly found out that the default assignments of IP were rather annoying: Every time a new instance of Docker is fired, a new DHCP address is given and the Magento configuration needed to be started.

Instead, I wanted to have a static IP, so that I only needed to modify my Magento configuration once. To do this, you need to define your own custom network:

docker network create --driver bridge magento --subnet 172.18.0.0/16

With this command, you'll create a magento network that is used in the second command below.

Creating the docker container

Now that a custom network is created, you can run the actual Docker image. No need to prepare anything because Docker will download the right image (mysql with version 5.7 in this case) automatically for you:

docker run \
--rm \
-p 3306 \
-e MYSQL_ROOT_PASSWORD=root \
-e MYSQL_DATABASE=magento2 \
-e MYSQL_SQL_TO_RUN='GRANT ALL ON *.* TO "root"@"%";' \
--tmpfs=/var/lib/mysql/:rw,noexec,nosuid,size=600m \
--tmpfs=/tmp/:rw,noexec,nosuid,size=50m \
--net=magento \
--ip=172.18.0.100 \
mysql:5.7

Port 3306 is exposed to the outside world (your host). The database credentials are something you can customize if you want. The MYSQL_DATABASE constant also allows for an empty database magento2 to be created when the Docker container comes alive.

The main trick here is the usage of a tmpfs folder for all of the databases that MySQL uses (var/lib/mysql). In my case, 600Mb was large enough for the database. But you might need to increment this.

Once the command runs, it should show a process of the MySQL server listening to a port.

Testing if it works

You can now open up a MySQL client that connects to this server:

mysql --host=172.18.0.100 --port=3306 --user=root --password=root

If this works, you can start playing with it. If you are done and you want to shut down the container, first locate its container ID and then use that stop the container:

docker ps
docker stop <container_id>

Modify the Magento configuration

Last but not least, we can now put the optimized MySQL image to use. Open up the file install-config-mysql.php again and modify the database settings:

return [
    'db-host' => '172.18.0.100',
    'db-user' => 'root',
    'db-password' => 'root',
    'db-name' => 'magento2',
    ...

And it is fast

The end result is cool: It takes - in my environment - less than 2 seconds to run a basic set of integration tests of one of my own Magento extensions. And this is such an improvement that I've moved the 2 Docker commands to a script, which is included in my rc.local which is run at every boot.

I hope you can benefit from this little trick as well!