Btw, thanks for putting the benchmarks up, they led to a batch of improvements on the Enum.random implementation in Elixir master!

As @josevalim patiently noted in the GitHub issue, Enum.random turns out to be the culprit here. I thought I had controlled for that in a previous experiment, but apparently I had not. Uff. Been a rough week. My apologies.

So, I dug into this, and when I did so I realized there’s a huge difference between the settings being used between pg bench and Ecto. The number of threads used for pgbench is NOT the same thing as the parallelism used in parallel bench.

Threads

Notably, the following section is BEFORE introducing pg_notify

From the pgbench docs:

Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. Default is 1.

This is more akin to the number of BEAM schedulers. It’s the -c Client Count that is the true “parallelism” (which really ought to be called concurrency). Here’s why this matters: The settings for pghr that I downloaded had a pool size of 40, but only a parallelism of 10. This means there are 30 connections just sitting idle at any given time since there are only 10 processes doing work.

Improving the iterations_in_checkout value up to 1000 also makes a big difference. On my computer ecto with these changes is now 87% as fast as pg bench, which is pretty good in my books given that we haven’t really started micro-optimizing the loops or the mechanism we use to check for time.

Randomness

Here you’re using randomness just to make sure that a unique value is entered every time, it doesn’t need to be random in a secure sense. I wanted to make sure that differences in randomness didn’t slow Elixir down, while also keeping things fair. The first thing I did was change the Elixir random value to :erlang.unique_integer([:positive]). This actually had a decent impact, getting me to within ~90% of pgbench. At this point though I wanted to make sure that pgbench wasn’t being slowed down by randomness, so I added a create_item_fast_seq which used a sequence to generate incrementally higher values. This actually slowed pgbench down a fair bit, which makes sense given the additional disk IO.

Pg_notify

Here’s why all of this preliminary stuff was important: If you have the same number of parallelism and client values between ecto and pgbench, the performance penalty of pg_notify is the same. Both drop by a factor of ~3+. I’ve got a PR up with the changes I made so you can confirm.

BUT SURPRISE: it isn’t entirely pg_notify’s fault. If you change PERFORM pg_notify to EXECUTE 'SELECT 1' you still lose 40% throughput just from running the trigger.

Hi there - benchee creator here,

to be clear this is absolutely intended behaviour on the side of benchee.

The parallel option is there to exercise more load on the system or gather more data samples in the same time. Benchee is concerned with measuring and providing data about the time it takes individual function calls to execute. The function takes 10 seconds to execute hence an average of 1 and an ips of one is the correct behaviour.

I’ll edit the parallel benchmarking wiki page to highlight this property even more.

Cheers,
Tobi

PS: I haven’t read the original thread (yet), I will and I have something of note to say I’ll add a new post

Update (probably last one until Monday or Tuesday as I’ll be traveling in places with limited Internet access):

I’ve worked in all of @benwilson512’s feedback and I’m really happy with where we’ve landed. Please see the new README in the pghr repo (https://github.com/scouten/pghr#results). tl;dr:

• Ecto performance is between 87% and 107% of pg_bench write throughput on apples-to-apples comparison of the two systems.
• pg_notify clearly adversely impacts perf. My team has decided on an approach that doesn’t rely on pg_notify and we have a good idea of the perf we can expect once that work is complete. And that we will be satisfied with that result.

Thank you, everyone, who has participated in this effort. It’s one of the things that makes Elixir a great platform to build upon.