1

We are running a PostgreSQL database in the Google Cloud. On top of this we have an app. In the app we can configure runtime connection pooling settings for the database.

Our Google SQL server has 30GB ram so the default max_connections is 500 as I read in the Google docs. In our app we have set the following connection pooling (Apache commons pooling) settings:

  • MaxActive: 200
  • MaxIdle: 200
  • MinIdle: 50

We are experiencing issues with these settings. First of all, we often run into the MaxActive limit. I can see a flatline in the connections graph at 200 connections a couple times a day. At those moments our logs are flooded with SQL connection errors.

The server is using around 28GB ram on peak moments (with 200 active connections). So we are close to the RAM limit as well.

Instead of blindly increasing the RAM and MaxActive I was hoping to get some insights on what would be a best practice in our situation. I see 2 solutions to our problem:

  1. Increase RAM, increase MaxActive and increase MaxIdle (not very cost efficient)
  2. Increase MaxActive, keep MaxIdle the same (or even lower) and keep MinIdle the same (or even lower)

Option 1 would be more cost expensive so I am wondering about option 2. Because I lower the Idle connections I would take up less RAM. However, will this have a noticeable impact on performance? I was thought to keep MaxIdle as close to MaxActive as possible, to ensure least overhead in creating new connections.

I have done quite some research, but I came to the conclusion that tuning these settings are very situation specific and there is not really a general best practice on these settings. I could not find a definitive answer to the performance impact of option 1 vs option 2.

Ps. we are also experiencing some slow queries in our app, so of course we can optimize things or change the design of our app to decrease the amount of concurrent connections.

I really hope someone can give some helpful insights / advice / best practices. Thanks a lot in advance!

Marcel
  • 105
  • 6
  • You need to consider 2 others factors (at least): 1) how many concurrent users you have and 2) PG configuration. For 1, a properly configured app can handle much more user than it has connection. For 2) if you have small queries, you can lower the work_mem and then increase the number of connections – JGH Jan 28 '20 at 13:12
  • Thanks for your help! The amount of concurrent users varies a lot, but I would say 400-500 at peak times concurrent. We have quite a few heavy queries (lots of joins, big tables). But it's an app with over 2000 forms so it varies a lot. 70% of the queries are simple and small. Apart from that, am I correct about the 2 options (so option 2 would be as I expect)? – Marcel Jan 28 '20 at 16:41
  • 2
    400 users exhausting 200 connections may indicate an improper connection handling in the app (i.e. release the connection - from an app point of view - right after it has been used. Do not keep it open longer than required. The pool will is then responsible to close/keep it open to the DB). Don't worry about idle connections if issues arise at peak time (the connections are not idle, they are used in the app). If the server is already at 28/30GB, you must adjust the per connection setting before allowing more connections. – JGH Jan 28 '20 at 18:43

0 Answers0