Hello,
We are working on a payments systems which uses postgreSql 9.6 as backend DB and blockchain technology. The database is setup in HA in master-standby mode using pacemaker on Linux 7.6.
We are new to postgres and need help in deciding how to set value for max_connections on DB.
1. How can we decide on optimal value for max_connections for a given setup/server? I checked many posts saying that even 1000 is considered as a very high value but we are hitting the error too_many_connections due to Max_connections value limit.
I’d say 2x the number you expect to have connect to the database as you can not change the value without bouncing the service and downtime is usually hard to come by. Just set the monitoring solution at 50%. That’s what I currently do; using a max connection of 2000 with an average connection rate of around 800. That sounds like a lot but with 96 cores that’s only around 9 connections per core.
2. We usee hikari pool on the client side but that even has limitations. Implementing pg_bouncer is another suggestion in the posts. In a HA setup, do we have to set it up on both primary standby?
Take a look at pg_bouncer. The problem with a high number of connections is that the idle connections will wake up from poll() and spin on the cpu. I’m in the process of testing pg_bouncer to reduce the number PostgreSQL connections to resolve the high server load due to idle sessions. pg_bouncer has proven to be effective as it doesn’t suffer from the idle poll() issue and it has reduced the server load due to idle sessions.
I have been wondering if PostgreSQL used kqueues instead of poll if it would allow the server to scale better without having to introduce pg_bouncer.