pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Iain <iain@mst.co.jp>
To: Adile Abbadi <adile@minitdrugs.com>
To: pgsql-admin@postgresql.org
Subject: Re: Load Balancing/Multiple Postgres Machines
Date: Tue, 30 Nov 2004 11:21:49 +0900
Message-ID: <007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb> (raw)
References: <NDEDLIMJNEEMMAFHLEEGAEBHCJAA.adile@minitdrugs.com>

Hi,

I have been considering this in my spare time for a little while too.

Joshua already mentioned slony and pgpool, and you may want to look at 
heartbeat too, though I don't know if it is strictly neccessary in a 
slony/pgpoos installation. My focus is more on replication for high 
availability and failover than performance.

Replication will involve some overhead so to offset that your application 
must be set up to take good advantage of the load balancing possibilities. 
There is no guarantee that your application will perform faster even if you 
do implement replication.

As such, I don't know of any viable true _synchronous_ replication system 
for postgres, the options listed above seem best suited for async 
replication (using slony) and load balancing of SELECTs (not UPDATE INSERT 
DELETE) using pg pool. If your application has a very heavy SELECT component 
from browsing users (perhaps from a web application) then you may be able to 
have a large portion of your SELECT SQL diverted to the slave database, 
freeing the master to handle all updating of data.

Whether this will help you or not, I don't know. Analyse the options and 
your requirements and test it. If you come up with any good information, I'd 
be very intertested to hear it.

I posted a summary of options as I understood them in the admin section. If 
your search on availabilty and  failover you should find it.

Regards
Iain

----- Original Message ----- 
From: Adile Abbadi
To: pgsql-admin@postgresql.org
Sent: Tuesday, November 30, 2004 3:34 AM
Subject: [ADMIN] Load Balancing/Multiple Postgres Machines


Hi all,

Not sure if this has been asked before - it probably has been - but I figure 
its probably just easier if I ask so my question is this:

How easy is it - or rather is it possible to create multiple instances of 
the same db on multiple machines?

We are currently running a very highly used postgres db (Ver 7.2) that eats 
up a lot of cpu time when its heavily used. We recently bought some very 
powerful equipment to accommodate this high use, (currently its on a single 
CPU, 1GB of Ram SCSI server - upgraded to a dual CPU, 4GB of ram SCSI 
server) but my fear is over time this issue is going to haunt me again and 
hardware can only be upgraded so much.

So is it possible to do some level of load balancing in postgres - I would 
love a situation where I could have multiple machines running multiple 
instances of the same db (that are all synced in real-time), where there is 
some smart level of load balancing happening.

So if it is possible is there some instruction info out there to help me 
along my way. Any help or insight would be greatly appreciated.

All the best

Adile 




view thread (11+ messages)  latest in thread

Message-ID: <007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb>
Permalink:  ../007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb/
Also on:    postgresql.org/message-id/007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: iain@mst.co.jp, adile@minitdrugs.com
  Subject: Re: Load Balancing/Multiple Postgres Machines
  In-Reply-To: <007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb>
  References: <NDEDLIMJNEEMMAFHLEEGAEBHCJAA.adile@minitdrugs.com> <007301c4d683$5bbd29c0$7201a8c0@mst1x5r347kymb>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox