5

I have a project in Spring Boot (1.5.1.RELEASE) which is using Postgres DB (9.1-901-1).

While I am running this application in production it will create upto 100 number of Idle connection in DB.

So I override the default configuration to control creating 'N' number of idle connection. Please check below configuration:

datasource:
  driverClassName: org.postgresql.Driver
  url: jdbc:postgresql://localhost:5432/db_name
  username: root
  password: root
  tomcat:
  # default value is 100 but postgres' default is 100 as well. To prevent "PSQLException: FATAL: sorry, too many
  # clients already", we decrease the max-active value here. Which should be sufficient, by the way
    max-active: 10
    max-idle: 10
    min-idle: 5
    max-wait: 30000
    time-between-eviction-runs-millis: 5000
    min-evictable-idle-time-millis: 60000
    jmx-enabled: true

Now Its creating 5 Idle connection to DB.

I am verifying that by executing below query.

select * from pg_stat_activity;

Now My question is, Do I really need 5 Idle connection for Production environment.

What will happen if I change my configuration like below? Will this work without any problem?

 max-active: 1
 max-idle: 1
 min-idle: 0

And also would like to know how PgBouncer will help for this case? Is it necessary to have PgBouncer for Postgres?

Satz
  • 307
  • 3
  • 19
  • 2
    If you want a slow to react application that is the way to go. Creating a connection to the DB is a time consuming operation and as such you want to use a pool of connections and reuse existing connections. Those connections are idle until they are actually used. If you configure it to have no idle (or min/max connections) you basically bypass the whole point of having a connection pool. – M. Deinum Apr 19 '18 at 11:30
  • 1
    The actual values to use are based on your site and how it is used. On a site where I work, we have 75 idle connections at startup and they are used to about 90% in regular day. But it goes over that during peaks. This is tunning for performance. It it is a test, small site, then go ahead. – Nic3500 Apr 19 '18 at 11:45
  • @Nic3500 It's production site.. So I think I have to use more number of idle connections – Satz Apr 19 '18 at 12:24
  • In my opinion the best way for you to know the right number for your case is to benchmark the db usage of your application. Set max and min idle to 100 and monitor the usage. if your application uses a maximum of 10 connections (90 of them stay idle) then setting your min-idle to 10 and max-idle to 20 enough. – Akli REGUIG Apr 25 '18 at 13:37

1 Answers1

9

The configuration you have proposed is definitely not recommended. A complete DB connection cycle will go through

  1. Establish TCP connection
  2. Validate the credentials
  3. Connection is ready
  4. Execute commands
  5. Disconnect

By maintaining idle connections (connection pool) with the DB you are saving times spent steps 1-3 thus achieving better performance.

You should tune the settings on the DB based on the max instances of the microservices that will connect. for e.g. if max number of microservice instances is 5 and the service is configured to maintain 50 idle connetions then ensure that your DB is configured to cater to atleast 250 connections.

To arrive at min connection settings for the microservices you will need to do some tests based on your non functional requirements and load tests on the services.

Mohit Mutha
  • 2,921
  • 14
  • 25
  • So the answer is, I need to have good number of Idle connection based on needs and performance. Let me do some R&D with my DBA team and find out number of connection. – Satz Apr 29 '18 at 07:42