axelson
How can you avoid overwhelming ecto?
I’m getting the following error in my logs fairly often:
connection not available and request was dropped from queue after 476ms. You can configure how long requests wait in the queue using :queue_target and :queue_interval. See DBConnection.start_link/2 for more information
I understand that I can tweak the queue_target and queue_interval, however I’d like to better understand what (if anything) I should be trying to do before that. I have operations coming in from multiple locations that hit the database:
- Web requests directly from clients (via phoenix/http)
- Batch jobs from another server (via phoenix/http)
- Batch jobs via oban
Should I be trying to provide backpressure somehow? Should I try to prioritize the client requests over the batch requests? Should I be checking the telemetry for the ecto queue time in my code?
Basically I’m trying to understand the best practices and how I should approach this problem and I’m curious to hear any thoughts (including possibly telling me that I’m thinking about this all wrong)
First Post!
OvermindDL1
Is it really just Ecto being overwhelmed, or is it the database itself? If the database can’t keep up then doing more in Ecto won’t really help, you’d need to fix the queries. If the database is still not being fully loaded then adding more connections would probably help then (in addition to fixing up some of the more costly queries).
Most Liked
hauleth
josevalim
Correct. You should avoid tweaking queue_target and queue_interval because increasing them mostly means your users have to wait longer.
You should look at the logs and generally track your database times. Ecto logs the following data:
-
query_time / db_time - how long your queries are taking. if you have slow queries, they will get all connections busy, leading to the errors above. Your priority is to reduce those (adding indexes, denormalizing the data, etc)
-
queue_time - how long you are waiting for the connection. If query times are already low but queue time is high, it is like that you have a lot of traffic and you can’t serve it all in time. A good solution here is to increase the pool size or create separate pools based on usage as mentioned by @hkrutzer
drl123
Are you running MacOS? I noticed these starting to pop up on my machine too, mostly when in iex and it’s sitting idle. It may have something to do with the latest update of MacOS (I’m on Mojave 10.14.6). The db connector in my IDE also has started dropping the PG connection about the same time as I started to see the errors in iex and that was shortly after the OS had an update.
Curious if anyone else is seeing this and if it is only MacOS or Linux too.
Last Post!
axelson
Ah, that is a good idea for the bulk requests, although it might be a little difficult passing the repo to use through the rest of the system.
Thanks for the tips! I’ll take a look at those values (looks like they’re exported via telemetry as well) and then choose the approach from there.
Popular in Discussions
Other popular topics
Chat & Discussions>Discussions
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"