We have an application running on EC2 which connects to a database on RDS.
Over the past couple of weeks, we noticed the connections to the database were always filling up after which
we were not able to make new requests to the database with the following error
The connections always go up in count and rarely drops down to a lower number. On rebooting the server the connection count is reset then it starts to increment again never dropping.
I’m sure more knowledgeable souls will leap in. But what your graph is showing that your RDS instance has run out of connections. If your Ecto Repo pool is 10 connections and you are running out of connections on the DB side at 80 then that suggests:
Some other app(s) is/are connecting to your DB and using connections
Your pool isn’t really 10 connections (but its unlikely to be 80!
Something is causing the Repo to crash and restart but the old connections are still being held open on the DB side (this seems unlikely since you would see log messages). And it would have to crash and restart 8 times!
Maybe that at least gives you some angles to consider …
There is another elixir application connected to the instance which has the same setting for the pool_size, there is also a database client connected to the client but it uses only 2 pools.
Killing just this one app takes us back to 40 new connections which then increase to 80 again
Your pool isn’t really 10 connections (but its unlikely to be 80!
The pool size is set to 10 on the production config file
Something is causing the Repo to crash and restart but the old connections are still being held open on the DB side (this seems unlikely since you would see log messages). And it would have to crash and restart 8 times!
Checking on Sentry there are no crushes reported in relation to the database
I suspect I found the problem, on the CSV page, we are subscribing to postgres events which we never close when the user navigates away from the page or the process crashes
def mount(_session, socket) do
Phoenix.PubSub.subscribe(MyApp.PubSub, "repayment:upload_csv")
Phoenix.PubSub.subscribe(MyApp.PubSub, "repayment:repayment")
Phoenix.PubSub.subscribe(MyApp.PubSub, "repayment:paid")
{:ok , socket}
end
I suspect this is the problem because on the postgrex docs it states that Postgrex.Notifications starts a separate (notifications) connection.
I am using phoenix live view which means if I find a way of unsubscribing to these then the connections will be reduced
what is the current postgrex code? sounds like you are opening up a db listener per client connection - think you are better off with one long-lived listener that then emits pubsub events…
I’ve used Boltun in the past which gives a nice API - https://github.com/bitgamma/boltun - but better/more recent solutions might be around…