Re: pgpool2 vs sequoia - Mailing list pgsql-general

Thank you guys! But now i am clueless as before. please, enlighten me:

i need about 200 concurrent db connections at peak time and - at the moment -
i only have cheap hardware (2-4 GB Ram, Dual Opteron CPU, SATA Disks, RAID 1)
My database has a size of 11 GigaByte, largest table has 100.000.000 records
All queries are already optimized, i do regular reindex and clusetring of
tables and indizes.

the last few years we ran with horizontal partitioning. i always ran into
problems with horizontal partioning because few tables must be shared across
the databases and sometimes things are moving and i got lot of trouble with
my primary keys being the same on different nodes.

We have only few write operations and almost no write operations at peak time
We have lots of read operations and a peak time with even more read
operations.  We are now using Hibernate/JDBC to connect to the database
(before we used PHP)

At the moment i see the following solutions:
1 synchronous replication: pgpool2 (or sequoia)
2 horizontal partitioning
3 better hardware
4 asynchronous replication: slony

option 1 looks so easy and smart so i decided to go with solution 1. But you
told me not to do so.

option 2 was already used and had lots of problems. And it's not an easy task
to use horizontal partioning with hibernate (it works but it's not
trustworthy IMHO)

option 3 would be nice but my hosting provider has only cheap hardware and as
we have not much experience with enterprise hardware we fear to buy something
really expensive.

So your advice is option 4 and use "Master-Slave Replication" with Slony-I,
right? But i fear slony. i don't know why. (BTW: Is there a PDF version of
slony documentation anywhere? )

Or is pgpool2 still a reasonable solution in my setup? It looks so smart and
so easy to setup.

kind regards,
janning


Am Montag, 6. August 2007 14:16 schrieb Markus Schiltknecht:
> Hi,
>
> David Fetter wrote:
> > Very few people actually need synchronous replication, and those who
> > do buy Oracle's RAC (and curse it) or use DB2's offering (and also
> > curse it ;).  For most purposes, fast asynchronous replication is good
> > enough.
>
> While this is certainly true, please keep in mind that async replication
> always brings up the potential of conflicts, per definition - no matter
> how fast it is.
>
> IMO, it would often be a lot simpler and less expensive to use sync
> replication and bite the bullet of a small commit delay (depending on
> the interconnect) - but not having to deal with conflicts.
>
> OTOH, of course there's no real (at least no OSS) solution to sync
> replication, so this is just theory. I'm trying to change that with
> Postgres-R [1].
>
> As a second note, I might add that all of this really only applies to
> writing transactions. Read-only transactions are, of course, not
> affected by replication and can be balanced across multiple servers with
> both types of replication. Only sync replication guarantees consistent
> snapshots, though. Which is the reason for conflicts...
>
> But again, this is just gray theory. And practically speaking, I'm
> giving you the same general advice: prefer async replication, because
> there are solutions, which are mature and used in production.
>
> Regards
>
> Markus
>
> [1]: For more information, see: www.postgres-r.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings