Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

Load balance Three Postgresql database (Primary, Secondery1 and Secondery2) Servers

Hi Experts,

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 !
0
Shakthi777
Asked:
Shakthi777
  • 2
  • 2
3 Solutions
 
lcohanDatabase AnalystCommented:
"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. "

In my opinion it would be better to direct the traffic from the application to the desired instance and not from the backend database servers. In other words the app should have 2/3 connection strings (and app pools) to direct the queries against the desired "node" for the desired purpose.
0
 
Daniel WilsonCommented:
Another option is pl/Proxy.
https://wiki.postgresql.org/wiki/PL/Proxy

That is the solution Hannu Krosing developed to scale Postgres for Skype.

I haven't yet implemented a cluster in PostgreSQL, but PL/Proxy is the tool I expect to use ... later this year.
0
 
Shakthi777Author Commented:
I really wanted to thank for all your prompt responses,

#lcohan
This applications has implemented with a single connections string, any good approach on separating those ?

#Daniel Wilson
Partitioning is another option it's seems ?
0
 
Daniel WilsonCommented:
PL/Proxy involves a partitioning / sharding scheme.  The application knows nothing of the sharding.  It simply talks to the proxy.  The proxy knows which shard to contact .
0
 
Shakthi777Author Commented:
Thanks for the valuable comments !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now