Looking for help with poor Ecto query performance

I think that the important think is that Ecto example is doing way more things than only inserting data:

• Encoding data
• Decoding data (as you are using numeric IDs it need to read the entry after it was inserted to check the inserted ID)
• Logging and Telemetry
• Generating random integer (you cannot be sure that the PRNG engine has the same performance, so this can greatly impact the IOPS)
• You do run-time protocol choosing
• Success checking (I am not sure whether pgbench do not just ignore failed inserts)

Other than that, as I see that you are running bench and the DB on the same machine - what are your guarantees that these two do not interfere in each other? Maybe system scheduler is an important thing there as well.

So in the end it isn’t fair apples to apples comparison.

Hmmm. I’m having a hard time accepting this answer. I could explain away a 10-20% performance impact based on this, but not an order of magnitude or more.

As I understand it, pgbench is just another TCP-based client just like psql or Postgrex, so at least the encoding is present in both tasks.

I did just now re-run the tests with the Mac Activity Monitor application watching CPU consumption during the create-records test.

In the pgbench test, it was pgbench at 75% CPU with the 10 postgresql processes running at ~50% each. (This is an 8-core machine.)

In the Ecto + Benchee test, it was beam.smp at ~500% with the 10 postgresql processes each running at 4-5% each.

FWIW I took the random number generation out of the create-items benchmark (replaced with a hard-coded constant). Both benchmarks improved somewhat, but there’s still a 10x discrepancy to be explained.

As a rhetorical question: Should a database abstraction layer impose an order-of-magnitude performance penalty? Had I known that it was on that order, let’s just say I’d have made some different technology recommendations.

One problem seem to be how benchee’s parallel option is used and how the result is reported. I think 1 ips = all parallel task being run. See Parallel Benchmarking · bencheeorg/benchee Wiki · GitHub for more information

For example, if you set benchee parallel to 1 and pgbench clients and thread to 1 the result was that pgbench was only 5% faster. (If I changed the pgbench sql to run in a transaction they had the same throughput)

This means, the time for serialization and type checking and random generation has nothing to do with the speed as in the simple case they are almost the same.

I don’t know how to properly measure this with benchee. A simplistic way would be to multiply the ips by the parallel number. But I don’t know if that is the right approach either. In your case it changes the results from pgbench not being so much faster (60% to 10% faster depending on settings).

Look into how pgbench does connection pooling. Perhaps it opens up a connection and then just holds that and runs the transactions sequentially on that? Ecto will have to check out a connection from the pool, execute the query, and return the connection. A pool is there to make sure the database doesn’t get overloaded in terms of the number of connections, so there is always going to be overhead. Especially if the number of concurrent requests is higher than the number of connections in the database.

I wonder if you’re hitting ecto queue limits. You can check this by getting the telemetry from ecto. It’ll give you a breakdown of where you’re spending the most time (per query). We always have to crank the db connections for ecto quite a bit. A quick test would be to measure the run queue by calling :erlang.statistics(:run_queue). If you see it increasing through the course of your test you know you have a bottleneck somewhere.

Yeah I think you’re on to something here. Here’s another issue with how parallel works, from the benchee hexdocs:

This is terrible if you’re relying on benchee’s parallelism to maximize throughput, because instead of trying to always have 4 processes constantly inserting, a group of four will always be as slow as the slowest insert. This is to say, if you have 3 that take 0.5ms, and one that takes 1ms, there will be a full 0.5ms where only one process is inserting.

Also exactly correct. @scouten see the benchee wiki on parallel: Parallel Benchmarking · bencheeorg/benchee Wiki · GitHub.

Notice how the IPs degrade as parallelism increases. What benchee parallelism about is generating load, and then measuring how a single function call performs in the context of that load. It is not measuring throughput.

I think the wording on the Benchee wiki could use some improvement, but I can show that the interpretation you’ve arrived at is incorrect.

Let’s benchmark Process.sleep/1:

delays = [1000, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10]

Benchee.run(
  %{
    "sleep" => fn ->
      delays
      |> Enum.random()
      |> Process.sleep()
    end
  },
  parallel: 10,
  time: 10
)

If Benchee were running tranches of iterations as you suggest, iterations per second would be very close to 1.0 because the one long delay I’ve included would prevent the other “parallel” iterations from continuing. That does not appear to be the case:

$ mix run bench/sleep.exs
Operating System: macOS
CPU Information: Intel(R) Core(TM) i7-4980HQ CPU @ 2.80GHz
Number of Available Cores: 8
Available memory: 16 GB
Elixir 1.9.1
Erlang 22.0.7

Benchmark suite executing with the following configuration:
warmup: 2 s
time: 10 s
memory time: 0 ns
parallel: 10
inputs: none specified
Estimated total run time: 12 s

Benchmarking sleep...

Name            ips        average  deviation         median         99th %
sleep          8.31      120.36 ms   ±257.98%       11.00 ms     1001.22 ms

We’re running multiple transactions on a single connection in both cases. Is checking out a pool from a connection that expensive? If so, why?

Postgres opens one postgres OS process per active connection and terminates that process when the connection is closed by the client.

As mentioned earlier in the thread, I’ve observed the Mac Activity Manager while running both the Ecto + Benchee and pgbench. In both cases, I see exactly the requested number of connection processes open (corresponding to the pool size I’ve specified) and CPU load is evenly distributed among those processes.

That would seem to suggest a similar connection-pooling mechanism being used in both cases. Yes, it’s possible pgbench doesn’t have to do any pool check-out, but I’m having a really hard time explaining orders-of-magnitude away for that reason. Having an even harder time explaining away the markedly worse behavior on updates that way.

The updates are easy to explain. With pgbench you’re just doing an update action. With ecto you’re doing a find and then an update.

Have a look at Rewrite update query without hand-rolled SQL. by scouten · Pull Request #5 · scouten-archive/pghr · GitHub.

I also ran the update benchmark with hand-rolled SQL to match the pgbench approach. No meaningful difference.

Ok so you’re clearly hitting some other bottleneck. Did you look at the run queue or telemetry data as I described above?