A Schrödinger's database adapter: Ecto&PostgreSQL perform faster when observed with Observer

Can you tell us:

1. The CPU architecture
2. Erlang/OTP version

There is another thread where another performance issue was hunted down and those parameters mattered. From what I recalled it had to do with an Apple M2 putting processes on slower cores. Having the GUI monitor open could prevent such thing to happen (???)

Observing this topic will resolve the issue faster.

BartOtten, thanks for your response.

CPU: 11th Gen Intel Core i7-1165G7 2.80 GHz, 4 Cores, 8 Logical Processors
Erlang: Erlang/OTP 25 [erts-13.0.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit:ns]
Elixir: IEx 1.13.4 (compiled with Erlang/OTP 25)
OS: Windows 11 Pro 22H2 OS Build 22621.1702, WSL2 Ubuntu 20.04

Having the GUI monitor open could prevent such thing to happen

Which GUI monitor do you mean?

Observing this topic will resolve the issue faster.

I am not sure I understand this, could you please elaborate?

The GUI monitor = Observer. As observer is always busy observing, it might keep a process in “performance mode”.

The other was a joke about your database being faster when observed (Schrödinger). I applied this effect to this topic Rest assured: observing this topic 24/7 won’t make any difference in solving speed. Feel free to take a nap.

This might be related to

1. PostgreSQL planner
2. Virtual machine (WSL)
3. Kernel scheduler
4. Kernel network stack dispatch
5. General memory layout

Anyway, I’d suggest using Task.await_many and publishing your code, since it will definitely speak more precisely than your description

I will look into Task.away_many, meanwhile, I am simplifying the question:

Why a function that runs many database queries with a separate Task.async call for each query, runs faster when launched from iex?

I just ran it from iex without Observer, it still runs fast (faster than mix run --no-halt).

You can share the code, and I will test it in my local setup. Otherwise, this can be related to anything

That only depends on how many tasks will you run vs. the size of your DB pool.

Example: if your DB pool is at 10 but you spawn 30 tasks then 20 tasks will wait until the first 10 finish first. That can give you the idea that it’s “slower” when in fact it isn’t.

@dimitarvp thank you for your response.

I think I understand how a DB pool works, but I guess this issue is not about the pool.

I run exactly the same code with exactly the same pool configuration and I get the following results

Run with mix run --no-halt: 84 seconds.
Run from iex -S mix: 25 seconds.

In both cases exactly the same pool is used.

I see. Well, as a start using Enum.map + Task.async + Task.await is a tad inefficient – though I wouldn’t think that amounts to more than a rounding error for the performance difference between executing the code in mix run --no-halt and iex -S mix but… let’s address one problem at a time.

I also don’t see a reason to use recursion in your scenario, by the way. That’s probably the main culprit because you end up processing batches serially.

Still, try this:

@maximum_parallel_db_connections 15

@doc "Process one batch serially within a single DB transaction"
def process_batch(items) do
  Repo.transaction(fn ->
    Enum.each(items, fn -> item
      do_stuff_with(item) # 👈 your processing code here
    end)
  end)
end

@doc "Process all items in parallel"
def parallel_process_items(items) do
  items
  |> Stream.chunk_every(100) # 👈 your desired batch size here
  |> Task_async_stream(&parallel_process_batch/1, max_concurrency: @maximum_parallel_db_connections)
end

So, IMO first change your code to something close to the above, post it, and also post if there are still differences between the both ways you are executing your code. That will put everyone in a better position to try and help you further.

How do you measure time?
What’s in your code?
How do you call these functions exactly in both variants?
How can I test the same?

Without answers to these questions, this discussion won’t bring any answer, it will be just dice rolling with extremely small chance to get a useful answer.