OK, got a few spare moments so I was able to post it tonight.
scouten
aseigo
Curious on this one, I gave it a spin here as well.
With pgbench without the pg_notify trigger, I get a bit over 80k TPS. With the trigger, pgbench manages “only” 50k TPS, which is only 62.5% the performance relative to without.This is quite reliable here.
Using ecto, I get 44k TPS without the pg_notify trigger, and 33k with the trigger which is 75% the performance of without.
The drop in performance is less (relatively) in ecto than pgbench, which makes sense since there is overhead in the Elixir code (inc ecto, of course) which gets paid on every transaction, which masks the performance hit due only to the trigger. But the performance drop is there, and significant in both pgbench and the ecto benchmark.
Simplifying the trigger itself by removing the string coercion and concats results in a small improvement in performance, which also makes sense since that code is actually being run in the db. Keeping the trigger but removing the pg_notify eliminates the performance penalty entirely.
Moving to just one thread (vs 10), the penalty is reduced to 83% as fast with vs without the pg_notify, so it certainly costs more as the number of parallel actions occur …
So, seems that the issue is pg_notify itself, and is indeed likely the 0(n^2) behavior showing up when there are multiple messages being broadcast on the same notification channel.
There are patches on the postgresql mailing list that have been rotting for a year or more (depending on the patch). This seems to be an issue that is not going to see easy resolution. If this is indeed the source of your performance problems, it may be time to look at a solution other than pg_notify. 
If your application(s) are entirely Elixir and/or database mutations all go through your Elixir code, you may want to look at phoenix_pubsub. It certainly scales decently, and works lovely as a message bus. If you need horizontal scalability and can not do BEAM clustering, then perhaps move to a full external message queue bus solution or if you have to (or just want to) keep it all in-app perhaps look at something like lasp-pg.
Good luck!
(p.s. Also tried with the straight NOTIFY call instead of a PERFORM pg_notify in case there was some gotcha there, but they are indeed equivalent.)
3
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
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.
8
dimitarvp
If you are willing, could you please make a PR and link it here? Really curious to see your changes.
0
scouten
Ben did post a PR last night (more correct concurrency by benwilson512 · Pull Request #17 · scouten-archive/pghr · GitHub). I’m reading through it now. There were a few distinct changes he’s proposing and I’m thinking about splitting them apart and layering them in separately so we can assess each one independently.
1
kip
ex_cldr Core Team
Really enjoying this thread (and I suspect I’m one of many lurkers). Thank to you all for digging into this.
6
scouten
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 (GitHub - scouten-archive/pghr: Q&D demo of PG perf issues · GitHub). tl;dr:
- Ecto performance is between 87% and 107%
of pg_benchwrite throughput on apples-to-apples comparison of the two systems. pg_notifyclearly adversely impacts perf. My team has decided on an approach that doesn’t rely onpg_notifyand 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.
7
scouten
FYI everything that I’ve been working with is now merged into the pghr (short for Postgres hot rod 
) repo so you can check methods and results.
This includes all of Ben’s suggestions, except for the use of sequence in the pg_bench create test, which did not appear to help performance. (See PR #22 in that repo, which was not merged.)
2
dimitarvp
dimitarvp
3rd round of tests. Notes:
- At GIT commit
1b77536cd2214e23b3a045529e9cc4038332bc0c. - Added my Manjaro i3-3217U spare laptop that I turn on like five times a year.
- Changed Erlang to
22.1.1. Elixir is unchanged and at1.9.1-otp-22. - All tests are at 30s now so no point in the “duration” column.
- All machines use SSDs, no HDDs involved.
Results:
| CPU (cores/threads) | Test | IPS |
|---|---|---|
| Xeon-2150B, 10/20 | Elixir create | 13780 |
| Xeon-2150B, 10/20 | pgbench create | 14768 |
| Xeon-2150B, 10/20 | Elixir update | 14360 |
| Xeon-2150B, 10/20 | pgbench update | 14563 |
| i7-4870HQ, 4/8 | Elixir create | 17746 |
| i7-4870HQ, 4/8 | pgbench create | 26338 |
| i7-4870HQ, 4/8 | Elixir update | 22624 |
| i7-4870HQ, 4/8 | pgbench update | 30569 |
| i3-3217U, 2/4 | Elixir create | 194 |
| i3-3217U, 2/4 | pgbench create | 191 |
| i3-3217U, 2/4 | Elixir update | 198 |
| i3-3217U, 2/4 | pgbench update | 190 |
These results are surprising to say the least. I can’t explain why the iMac Pro did so bad. I have various system monitors installed and Postgres couldn’t even go beyond ~127MB/s write throughput (and I know it has peaked at 1.6GB/s write speed when doing pg_restore). So something is severely under-utilised under this test.
Additionally: 6 out of the 10 cores were very moderately used: about 35-40%, another 2 were at about 15%, and the final 2 weren’t used at all.
3