scouten

Looking for help with poor Ecto query performance

My team at work is seeing some rather poor performance, especially on update queries, from an application we’ve built using Ecto and Postgrex. We’ve been able to reproduce this in a very small, sanitized demo application which I’ve posted here:

https://github.com/scouten/pghr

We’ve run the same operations in Benchee + Ecto and compared them to equivalent queries performed using pgbench and the results are rather disturbingly out of league with each other. In the case of update queries in particular, I am seeing a difference of nearly 75x in request throughput when testing against Postgres on my 2015-era laptop. The discrepancy is on the order of 15x for create transactions.

We are seeing a 10-20x discrepancy between pgbench and Ecto when we run against actual provisioned database instances on AWS using similar methodology.

What I’m hoping for from the community:

If there’s something wrong with our test methodology, I’d love to hear it.
If there’s something we can tune to make our Ecto configuration more in line with pgbench reported results, I’d love to hear it.
Or is there actually a performance problem with Ecto? (I’d be less happy to hear that, but at least I’d know.)

Pull requests or comments on that repo welcome.

82 comments

#ecto #postgres

7 8671 82

2019-10-17 21:55:50 UTC

Marked As Solved

scouten

Thanks, again, to this wonderful community for your help in getting us through the issues described at the top of this thread. I’ve taken a bit of time to boil this down to an easier-to-follow story about performance in hopes that it can save the next person to encounter similar issues.

Enjoy: https://medium.com/@ericscouten/elixir-ecto-postgres-a-saga-about-database-performance-488ba59128e

Post #87

Also Liked

josevalim

Creator of Elixir

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

Post #56

scouten

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.

Post #42

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.

Post #79