Experimenting with running mysql tests in parallel

There’s a similar approach baked into mix test, but it’s partitioned at the level of OS processes instead of per-connection. The default configuration generated by Phoenix in config/test.exs appends the partition to the database name:

https://github.com/phoenixframework/phoenix/blob/5408971b36debf7e1b7c88ad7be370726d519a90/installer/lib/phx_new/generator.ex#L340-L347

However, this process-level strategy seems like it only makes sense for splitting tests in a CI-like environment (where each MIX_TEST_PARTITION is a separate node that runs migrations etc) versus running them locally.

Or are you guys following a totally different approach altogether to run MySQL tests in parallel?

In another programming language, I created a new Postgres database for every single test. It made test parallelization very easy. While I’m sure there was some overhead, the speedup from parallelization clearly made up for it. I used Postgres’ “template database” feature; maybe something similar exists in MySQL.

This doesn’t help with your Ecto question though

We looked into MIX_TEST_PARTITION. But as you mentioned, it depends on user invoking multiple instances. So it is hard to make use of it for local development. And It is more heavy weight comparatively – running 10 beam-vm vs running one beam-vm with 10 databases. We do use process-level strategy, but bit differently, without using MIX_TEST_PARTITION. We split all of our apps into 2 groups and run app tests for these 2 set of groups in-parallel by making use of separate CI job.

I used Postgres’ “template database” feature; maybe something similar exists in MySQL.

We looked into this as well, but unfortunately there is no equivalent in MySQL space. The only way to do this is to dump and restore mysql structure. And DDL commands in mysql are slow. There are some interesting way to make it faster but as it is, it is hard to get this benefit without Postgrs template secret sauce I think.

Out of curiosity, do you know why is that the case? What is the characteristic of mysql transactions that make them unsuitable for async tests?

Even though we are using postgres at work, we are limited by ecto sandbox as well.

Is there any chance your current solutions/wrappers/scripts might be released as some hex package?

If you don’t want to rely on undocumented behaviour (I personally wouldn’t for anything serious) you could create multiple repos with different values for :database.

I think there are multiple scenarios which can cause deadlock. I don’t remember all details, one such case was where you are trying to insert multiple rows using a column which has unique-index. In innodb inserts in a transaction takes a gap-lock on index value range if the column as uniqueness index.

    Task.async_stream(1..2, fn _ ->
      Repo.transaction(fn ->
        for num <- 9..1 do
          Repo.insert!(%Post{title: "foo_#{num}"}) # :title has unique-index
        end

        Repo.rollback(:ok)
      end)
    end)
    |> Stream.run()

@NobbZ I’m would be happy to share it. But I’m not sure how to package it (perhaps Ecto is the right place for something like this?), and the question about pool_index still remains. I’ll just create draft in GH, let’s see from there.

@joey_the_snake can you expand on this? You mean there will be multiple Ecto.Repo instances connecting to different database with same structure? If so then the code under test dynamically need to choose one of these repo right?

Btw, we only need to do this for test env. So the worst case is it breaks pipeline, and we switch back to old approach.

Yeah you got it. It would be similar to the replica setup here: Replicas and dynamic repositories — Ecto v3.14.0.

TBH I don’t understand your current method too well because I’m not sure how configure/1 or the :pool_index works. But this is one way to have multiple databases ready to be queried.

IMO you already handle it well enough.

The only thing I’d change if I had a day to burn on this would likely be to make my own docker-compose.yml file that spins up 20 - 50 separate Elixir containers, each with its own DB, and control them with environment variables (or indeed with MIX_TEST_PARTITION), and see if that accelerates things even further. I doubt it but it could be fun to try because I/O bounded code usually can parallelize super well on something like 5x your CPU threads.

And I wouldn’t worry about using undocumented mechanisms here. Just use that until it’s there and if one version breaks it you can then make a call to either stick to the older version for a while or move to a Docker [Compose] based testing.

If I was in your team I’d simply tell you “good job, you did really well”.