Search Postgresql Archives

Re: pgpool2 vs sequoia

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux