During a spike, my connections go from 30 to over 140, as you can see below:
I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy:
1 Load Balancer (nginx)
2 web servers (www01 and www02)
5 DB servers (master01, slave01 (read-only), slave02 (hot_standby),
slave03 (hot_standby), slave04 (hot_standby)Just ONE Database. Size: 2TB
max_connections: 200
I'm using PostgreSQL 9.2
Question:
Can pgbouncer handle with my master and slave01, as I'm using slave01 as read-only server? Also.. I've got 2 www servers… Do I have to have pgbouncer in both of them?
Best Answer
Yes you can define in pgbouncer your 2 servers (or more) masetr and slave and connect to each of them. There is different kind of topology you can install pgbouncer on each www, as is the connection will be the fastest (www process can use unix socket to connect), on install a pgbouncer on each server (master and slave) or install it on a new host to be single. PErsonnaly I recommend another topology, install pgbouncer everywhere, on each www (to increase the connection), and on each db server, it will help you when you'll want to do migrations for example. PgBouncer support PAUSE/RESUME on connection, as is you can reconfigure your conenction on move traffic to master to a new host when you'll upgrade verison for example.