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
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
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.
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.
