axelson
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)
Where Next?
Trending in Discussions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
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
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #blog-post
- #ai
- #elixirconf-us
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming
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.
Master modern tools, write cleaner code, and tackle real-world challenges.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 10- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
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).
axelson
yeah I think it is the database itself as well
And I will be looking into fixing the more costly queries but I want to look to see if there’s a general pattern/approach that I should implement as well.
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.
axelson
This is in production running on heroku with GitHub - HashNuke/heroku-buildpack-elixir: Heroku Buildpack for Elixir with nitro boost · GitHub
drl123
OK…well that rules out my theory! I’d been seeing it on two different machines (both on 10.14.6) and the IDE dropping even on Rails projects, although Rails wasn’t throwing errors…just the DB explorer would lose the PG connection and I’d have to reconnect.
OvermindDL1
If it’s the database getting overwhelmed then not much you can do in ecto to help short of just adding delay to the inevitable, just need to optimize the queries or space them out more or so.
Woo, that sounds like major overloading?
axelson
So if I wanted to slow down my batch processing, could I control the amount of slowdown based on how overloaded ecto/the database is?
I’m thinking it would be possible to create a telemetry handler that’ll record how long the queuing time is and if it is too long, then the next batch job will be delayed to give the db some time to “cool off”
OvermindDL1
You could ask PG what it’s usage is, or maybe check the pool to see how many are checked out and halt for a period if too high? Honestly I’m more curious in what the queries are actually doing…
axelson
How would you ask PG what it’s usage is? Or check how checked out ecto’s pool is? I don’t see an API to get that info.
Yeah, I might start a separate thread about the queries themselves.
hauleth
Have you checked system catalogs? Like that.