|
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 have one set at 1000 but I usually top out around 500 right now. As long as you have the CPU and memory to handle whatever you’ve set I would think it’s ok. Hopefully others with more experience with big hardware will chime in. 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?
Client side pooling is a different proposition than server side. Each server maintaining its own pool VS the server maintaining a common pool, the server side pooling would lower your connection count. The value of pooling also depends
on your app. If it’s maintaining persistent connections for hours at a time then any kind of pooling is going to be of limited value depending on how the pooler is operating, IE connection pooling VS transactional pooling. If your transactions are simple
you could use transactional pooling which could greatly reduce the connection count.
For an HA setup you would need to set it up on both PG nodes. If you’re using the standby for read-only queries then it would have to be running on both nodes at all times and pacemaker would start/stop it as part of the failover process.
If you’re only using the active node then pacemaker would have to start the pooler on the active node and stop it on the passive node, and again control it on both nodes for failover. If it’s that large of a setup you may want to make the pooler its own cluster
with pacemaker to relieve the PG cluster of the additional load. Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you. |
