On 9/28/07, Will Langford <unfies at gmail.com> wrote:
>
> Hi,
>
> I work for a company that installs network services in many locations,
> each are completely isolated/localized.  These services include moderate =
to
> heavy postgresql traffic, with either a balanced select/update ratio, or
> slightly leaning towards more updates.
>
> Due to the nature of our products, we typically only need a single mid to
> low range server to handle our loads.  Times are changing, our products a=
re
> becoming more demanding, and needs are changing as well.
>
> We've always had a heartbeat/drbd fail over system on location so that if
> our system goes down, a backup takes over.  We've had insanely great succ=
ess
> with this setup.  The low amount of down time is acceptable, although our
> system is generally a 24/7 grinder.
>
> If we keep our current setup where the two primary nodes have the database
> live in DRBD, and there's a third report crunching box, how tolerant is
> slony-1 to heartbeat/drbd switch over to a new system ? Or is there some
> kind of multi-master replication system that's effective for a 2 or 3 sys=
tem
> setup ?
>
> Lately, I've been looking at several bottle necks in our system.  I've had
> good results with moving to a newer version of postgres, and also with
> changing out the underlying filesystem from ext3 to reiser.  I've done ab=
out
> all I can to tweak configuration file stuffs relating to
> wal/fsm/buffers/etc.  I've also done what I can to get proper columns
> indexed in the myriad of tables used.  The last couple things left for
> optimization attempts would be to generate diagrammed output of query
> patterns of our software and possibly also to move as many software queri=
es
> to function calls rather than just strings passed to the c lib back end (=
ie:
> to avoid constant tokenizing).
>
> Given that we typically have only two servers on location, it seems that
> many of the multi-master replication/clustering addons would end up with a
> negative speed benefit, especially given the 'bad' update/select ratio.  =
As
> such, we do have some client softwares that do only some mean ole huge na=
sty
> selects explicitly (no updates), and there are daily report crunchings on
> nonvolatile (ie: previous day's) data.
>
> We're a small software house, and many of the enterprise solutions are
> beyond our budget... so... make do with what ya got, etc.  We could
> probabily easily justify an additional low end server to handle the
> read-only client softwares, as long as they were executed soley on this b=
ox.
>
>
> So..... if the non-volatile read only queries are being executed on the
> slave system.... I can see this as being a fairly decent performance boon
> during peak report generation etc.  Anything that would need write access
> would either connect to the master system, or generate some .sql files to=
 be
> copied over and ran on the master system.
>
> Our current DRBD/failover system is for the database files and all other
> our-software specific files to live on the DRBD replicated mount point.
> We've not had any problems with a crashed / tripped-over-power-chord fail
> over issues with postgres firing up on the backup server after discovering
> the first node is dead.  And, I actually kind of prefer this system compa=
red
> to application level replication, because any kind of lock/stall caused by
> the application replication subsystem is detrimental, and our client
> software is a bit volatile at times (a 5 year old work in progress....
> feature creep etc). So... my question becomes (just to copy/paste) :
>
> If we keep our current setup where the two primary nodes have the database
> live in DRBD, and there's a third report crunching box, how tolerant is
> slony-1 to heartbeat/drbd switch over to a new system ? Or is there some
> kind of multi-master replication system that's effective for a 2 or 3 sys=
tem
> setup ?
>


So long as the ip's used by the slons are glued to the data, you might
survive. I'd test the heck out of such a system before deploying it in
production. I recall having plenty of issues with HACMP (the IBM / AIX
flavour, using SAN storage with multiple connections instead of drbd).
You'll also need some way to trigger slon resets. Otherwise this is more a
question about PostgreSQL surviving drbd/failover, which according to the
above, it already does.

A
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20071002/=
e761f723/attachment.htm