There seem to be several threads here where ppl have gotten the error
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2733ms. 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’m also getting it. Most of the replies seem to be about configuring DBConnection to be more lax. I have 100 connections in my pool, it’s not a very high traffic site, we should not be using all 100 connections. We’re also seeing this issue on localhost sporadically while running exunit.
I would love to just be able to peak at production and see who is using all the connections. Or turn on monitoring for a few minutes and see where they are being used.
I’ve been looking at the DBConnection code, and it’s a bit hard to understand how to get that information. Would it be possible to dig into an ETS table to get some of this information, even by polling?
I’m sure that we are doing something silly in our code, and I’m worried that just allowing our connections to be slower, and throwing more hardware at it is not the right solution.
Any ideas on how we might get to the bottom of this?
I would start using the logs and the telemetry events to see how long your queries, how long you are waiting for the queue, and that will help find out from where you queries come from.
Other than that, look for Repo.checkout and Repo.transaction. Ecto/DBConnection has no API for checking out a connection permanently, so it means they should all be in those calls which should eventually terminate.
This question requires different answers based on the scale. Assuming you’re at the small side and with a few app servers and a database server, yah, 100 connections definitely seems like a lot. If you’re mid-size, well, I have seen the necessity to have enough app servers that the database runs out of sockets. This doesn’t sound like that, otherwise we would also need to check fd’s and socket counts.
- If you have access to the database, enable slow query logging on your database. On mysql, you can also do ‘show processlist’ to see what queries are being executed. Postgres has ‘show * from pg_stat_activity’
- If you don’t have direct access to the database, you can install live_dashboard with the database options. /dashboard/ecto/stats may be helpful.
Ecto.DevLogger - an alternative logger of SQL queries in development
It really appears that you have some hanging query and hopefully this package can help you catch it.
Thanks so much. I was totally looking in the wrong place.
I’ve added a bunch of telemetry and we’ll see what I can find.