High Number of Postgres Connections with Play + Slick

In this post, we will go through one of the recent interesting scenarios which we faced while debugging high number of postgres connections.

Recently i started looking into pg_stat_activity to have a better understanding about the queries which were taking up a lot of time on postgres. For people thinking what is pg_stat_activity, it is just a table which contains the information about the current set of queries being served by postgres. For more details , please go through this link

Interestingly when i looked into pg_stat_activity, i noticed something weird about the connection usage for my application.

Total Number of Active Connections from my application to postgres is ~40

export_postgres

Defined Number of Connections from my application to postgres is 20

slick.dbs.default.db.maxConnections=20
slick.dbs.default.db.driver=something
slick.dbs.default.db.url=something
slick.dbs.default.db.username=something
slick.dbs.default.db.password=something

We can clearly see that actual number of active connections in my application are around 2 times the number of connections defined in my application. So this needed to be solved because we needed to increase the number of connections and were hitting the max_connection_limit on postgres.

Before going deep into the root cause of this , let me explain you our application stack. From the config properties you might have guessed we use

  • Slick for interacting with the Postgres database and
  • Play as a web framework

Now as with any other web application, we have migrations which get applied at application startup. Play already provides a default module i.e. EvolutionsModule which applies these migrations sequentially and makes sure that DB state is consistent with the code.

evolutions_moduile

This EvolutionsModule uses underlying slick library to communicate with the database. For running these SQL migrations, we need a connection pool and this connection pool is in turn provided by Slick. Slick uses the same configuration that we have provided earlier to configure this connection pool

slick.dbs.default.db.maxConnections=20
slick.dbs.default.db.driver=something
slick.dbs.default.db.url=something
slick.dbs.default.db.username=something
slick.dbs.default.db.password=something

So essentially in our application, we were creating two connection pools

  • For the Play Migrations
  • For the DB queries which are run during the application runtime

And as the for both of the connection pools same configuration was being used , so we were seeing 2 times the actual number of connections which were getting used.

To solve this , we just needed to add these properties to our slick configuration

slick.dbs.default.db.minConnections=1
slick.dbs.default.db.idleTimeout=60000

In these above properties, we defined that for a connection pool

  • We can have connections as low as 1 ( which was 20 by default previously )
  • If a connection is not used for 60s, then the connection would be purged given we have atleast 1 connection in the pool

Hence after applying this configuration, we were good and we were not seeing 2 times the number of configured connections.

after_fiing

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.