0

I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to distribute the load. I get "server closed the connection unexpectedly" intermittently when connecting through HA Proxy. If I connect directly to one of the PG Bouncer instances I do not receive these errors. Has anyone setup something similar to this?

1 Answer 1

0

I fail to see why you are loadbalancing between pgbouncers on the same server, I assume you have a single DB server instance, if they run on the same node you're not really balancing anything.

Than being said, this sounds like timeout issues, since pgsql connections tend to live longer than the standard haproxy timeouts I suggest your raise them: (for example in defaults section add : )

timeout queue           1m
timeout connect         1s
timeout client          3600s
timeout server          3600s
timeout check           2s

This will keep any connection that the client hasn't closed open for an hour. queries that take longer to run will be cut.

see https://github.com/gplv2/haproxy-postgresql/blob/master/haproxy-example.cfg for more inspiration from a working haproxy config.

2
  • It is my understanding that PGBouncer is single threaded and when you reach the limits of what PGBouncer can process, it becomes a bottleneck in front of your database. The only way to increase throughput is to increase the number of PGBouncers, therefore running multiple pgbouncers and load balancing them. Jan 4, 2021 at 13:28
  • We run single pgbouncer installs with more than 10K connections using a single pgbouncer, unless the number of connections is truly huge, I don't see an added benefit in running multiple ones. We can balancer 400 databases using 50 clusters with a single pgbouncer where the load is under 1 (2 cpu machine). You seem to assume that pgbouncer is CPU bound, it's not, it's just a pass through, you'll hit network limits way earlier before you hit CPU/memory limits.
    – Glenn Plas
    Jun 25, 2023 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.