quatermain
Is it possible to debug which processes and code places are using the DB pool when using Ecto?
Hi,
We have a regular Phoenix project using Ecto and Postgresql. But recently we are seeing a lot of connection timeouts or idle/closed connections. All looks like problem that DB pool is exhausted. Traffic has been pretty low, queries look ok, no big queries running all the time, no big Oban jobs. But I’m pretty sure we’re missing something.
So is it possible to debug which processes and code places are using the DB pool? Thanks to telemetry, we’re able to run code when these errors occur, but the question is whether it’s possible to get details about pool usage.
Telemetry handler:
def handle_event(
[:enaia, :repo, :query],
measurements,
%{result: {:error, %DBConnection.ConnectionError{} = error}} = metadata,
_config
) do
# check and log details about Pool usage
end
I can get repo’s child, but I’m not sure how to proceed.
repo_pid = Process.whereis(MyApp.Repo)
children = Supervisor.which_children(repo_pid)
Most Liked
josevalim
First step is to see your data for queue times, query times, and idle times, as those will show how busy the pool is. Also, what do you mean by connection timeouts?
The idle/closed connections may happen if the database or a proxy are closing them and are not necessarily an indicator of a problem (although we do ping the connection every second).
josevalim
Other things that could be helpful:
-
Investigate if this relates to deployments somehow
-
Chart p90% and p99% not averages, especially for queue and query times, but you should be able to track queries that take too long. Even if by using custom telemetry handlers. If anything takes more than a second, make sure to log that
quatermain
We already have had this issue with crashed primary machine multiple times this week, we changed machine with more resources maybe a week ago. so it’s new machine. In case this is the issue, something has to broke that machine even with new ones
pdgonzalez872
We already have had this issue
Hmm, interesting.
Please share
Absolutely, here are the steps (if it doesn’t help you, maybe it will help someone else):
# Steps to destroy a bad machine (and volume) and clone a good one - fly postgres
# list the machines, get the id for the bad one
$ fly machine list --app <your_db_app>
# list the volumes, get the volume attached to the bad one
$ fly volumes list --app <your_db_app>
# had to use --force, cli suggested, worked
$ fly machine destroy <bad_machine_id> --force --app <your_db_app>
$ fly volumes destroy <bad_volume_id> --app <your_db_app>
$ fly volumes list --app <your_db_app>
$ fly machine clone <good_machine_id> --region <target_region> --app <your_db_app>
# confirm
$ fly volumes list --app <your_db_app>
$ fly machine list --app <your_db_app>
One of the things support said was to make sure the machine you cloned was a good one. Could it be you cloned a bad one?
I feel like I’m pulling you into a rabbit hole, sorry about that, but I’ve had to do this twice now (that’s why I had notes 
)
Have you reached out to Fly’s support?
quatermain
I added these values to telemetry and look on this dashboard. 
are our crashes, dots on timeline are releases.
All looks ok but:
- idle time is too big, not sure why or if it’s ok
- total time - it’s too low, I expected it and sum of all other metrics
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Catch errors, track performance, monitor hosts and more.
Categories:
Sub Categories:
Forums
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.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"