Load balance Three Postgresql database (Primary, Secondery1 and Secondery2) Servers
Posted on 2014-07-24
I have somewhat large Postgresql database to manage and it's about 10GB after I made a dump and I believe it will take about 100GB space after it's populated on a hard disk. Currently I have implemented this on a Standby hot replication cluster. Two basic modules are running in this application and some reporting modules are directly connecting to the Secondary node(read only) and still there some SELECT queries coming in to Primary node(read/write) too.
Pgbouncer has implemented on both of these two database servers and currently operates on Transaction mode.
I get too high I/O rate and Processing rates ( %I/O wait is around 15 ~ 20, Load leverage is around 8 ~ 10) in the peak times. (these two databases are running on SSD with 8 cores of 2.5 Ghz, two Blades, Ubuntu 12.04 server 64bit )
So I'm planning to add third read only server and implement a mechanism to balance the load across these three DB servers. Example, filter all the read requests on secondery1, secondery2 and write requests on the Primary.
Did any one tried this with pgpool-II, or what is your valuable recommendation on such a situation ?
Thanks a lot for your time !