Gitlab CI Mysql service slow

The test suite for our app takes around 40 seconds on our local machines. But on our CI build, we are seeing the test build taking up to 10 minutes to run all tests. Most of the test suite involves tests that hit the database. Our business logic heavily depends on the database and needs rather complex schemas and relations to be correct, so we test on that. Think about nested structures that are manipulated in methods, saved to the db and then retrieved, where we check the outcome.

Anyway, we have the sandbox pool setup and as much async: true as possible, and on the local machine everything is fairly fast.

But we see tests that run around 150 to 300ms locally takings 4 to 5 seconds (!) in CI. The ci setup is:

  • Gitlab ci pipeline on, on the hosted version of Gitlab, with our own runner executing on a dedicated machine in the office (which is powerful enough)
  • the elixir/phoenix app runs in docker
  • the database is a Gitlab MySQL service runner

The machine running the Gitlab runner has ample CPU power, when running the tests CPU usage almost never traverses the 30% mark, only during the compilation of the dependencies we can see it going higher.

Is there anyone with similar experience using this kind of setup? We already set the overlayer2 Docker driver in the CI which significantly speed up the various db migrations, but queries are slow as ever…

Thank you for any insights!

First thing I’d check would be if the latency between your Elixir container and the MySQL host isn’t prohibitively high. One thing that helped me in a job ~3 years ago was to just set up a VPS with Elixir + the DB on one machine, and our CI/CD ran literally 3x - 5x faster.

Did you set up mysql on your local as gitlab ci does? For example, use the same image and same config.

If the machine is busy - mysql performance is impacted a lot. I’ve hit this issue with large Rails app. You may try using memory filesystem (or tmpfs!) if disk io is the issue.

Really hard to know what’s going on without a better understanding of your configuration and system. I’ve done a fair amount of Elixir in Gitlab CI but no one on here will be able to have the same insights as you.

For databases and CI another significant bottleneck you didn’t mention that you need to check is IO. Laptops and dev environments often have fast NVMe or SSD disks. If you have a hard drive, slower disk or even just a lot of IO happening on the Gitlab runner you can see performance drop significantly.

@chulkilee alluded to this in his answer but just as a sanity check is the runner you are using hosting the database or is another host/service? If not then you need to set up mysql with the Gitlab CI services style. And if you did and you do confirm that you have a slow disk the temp file system he alludes to will make it in memory and faster.

For now we are using the mysql service inside the Gitlab CI runner, so we assume it is running in it’s own docker container but on the same machine as where the app is running in test. We are also using the overlay2 driver which significantly increased the migrations speed.

The machine where the Gitlab runner is running on does have an NVMe disk, so disk IO should be equal or better then my local SSD disk.

We are going to try out putting the MySQL db in the same docker image as the Phoenix app in test, and see what it gives.

Thanks for your input @agundy and @chulkilee!

Small update. We ran some tests with a new Docker container that has both the Elixir/Phoenix app running, with the MySQL server running inside the same container. Overal speed was the same, thus very slow.

Also tried with a lot of tests running async: true (as we do on our local machine), as well as none of them running async, but no difference.

We will try some other things in the days to come.

Not a long ago I was bitten by slow database tests. Turns out it was my bcrypt config as pointed here TestCase to setup logged user

Thank you for the tip, but we did that already a while ago, which made a big difference at the time. Now we use t_cost and m_cost with the test values as suggested in the docs.

1 Like

Have you checked this issue?

1 Like