Postgres connection count always increasing, never drops

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

%Postgrex.Error{connection_id: nil, message: nil, postgres: %{code: :too_many_connections, file: "postinit.c", line: "803", message: "remaining connection slots are reserved for non-replication superuser connections", pg_code: "53300", routine: "InitPostgres", severity: "FATAL", unknown: "FATAL"}, query: nil}

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.

More information

The pool size is set to 10 on the prod.secret.exs.
We can reproduce this by uploading and processing a CSV file on the application.

Questions

  • Why are the connections always increasing?
  • Is there a way of closing the unused connections or restricting it to a maximum?

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:

  1. Some other app(s) is/are connecting to your DB and using connections
  2. Your pool isn’t really 10 connections (but its unlikely to be 80!
  3. 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 …

1 Like

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

  use Mix.Config
  config :my_app, MyApp.Repo,
    adapter: Ecto.Adapters.Postgres,
    username: "myapp",
    password: "pass",
    database: "myappdb",
    hostname: "myapphost",
    pool_size: 10
  • 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

is ecto and especially postgrex latest versions?

do you have ec2 auto-scaling enabled? (this would fit the issue/symptoms exactly)…

1 Like

The version is 0.14.3

Auto scaling is not setup for this application

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

great!

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…

4 Likes

You can use the terminate/2 callback in your LiveView to unsubscribe from notifications when the user navigates away from the page.

1 Like

Thank you will give this a try