Re: PostgreSQL clustering VS MySQL clustering - Mailing list pgsql-performance

Tatsuo,
    What would happen with SELECT queries that, through a function or some
other mechanism, updates data in the database?  Would those need to be
passed to pgpool in some special way?
Thanks,
Peter Darley

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Tatsuo Ishii
Sent: Thursday, January 20, 2005 5:40 PM
To: darcy@wavefire.com
Cc: jd@www.commandprompt.com; sfrost@snowman.net; herve@elma.fr;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL clustering VS MySQL clustering


> On January 20, 2005 06:49 am, Joshua D. Drake wrote:
> > Stephen Frost wrote:
> > >* Herv? Piedvache (herve@elma.fr) wrote:
> > >>Le Jeudi 20 Janvier 2005 15:30, Stephen Frost a écrit :
> > >>>* Herv? Piedvache (herve@elma.fr) wrote:
> > >>>>Is there any solution with PostgreSQL matching these needs ... ?
> > >>>
> > >>>You might look into pg_pool.  Another possibility would be slony,
though
> > >>>I'm not sure it's to the point you need it at yet, depends on if you
can
> > >>>handle some delay before an insert makes it to the slave select
systems.
> > >>
> > >>I think not ... pgpool or slony are replication solutions ... but as I
> > >> have said to Christopher Kings-Lynne how I'll manage the scalabilty
of
> > >> the database ? I'll need several servers able to load a database
growing
> > >> and growing to get good speed performance ...
> > >
> > >They're both replication solutions, but they also help distribute the
> > >load.  For example:
> > >
> > >pg_pool will distribute the select queries amoung the servers.  They'll
> > >all get the inserts, so that hurts, but at least the select queries are
> > >distributed.
> > >
> > >slony is similar, but your application level does the load distribution
> > >of select statements instead of pg_pool.  Your application needs to
know
> > >to send insert statements to the 'main' server, and select from the
> > >others.
> >
> > You can put pgpool in front of replicator or slony to get load
> > balancing for reads.
>
> Last time I checked load ballanced reads was only available in pgpool if
you
> were using pgpools's internal replication.  Has something changed
recently?

Yes. However it would be pretty easy to modify pgpool so that it could
cope with Slony-I. I.e.

1) pgpool does the load balance and sends query to Slony-I's slave and
   master if the query is SELECT.

2) pgpool sends query only to the master if the query is other than
   SELECT.

Remaining problem is that Slony-I is not a sync replication
solution. Thus you need to prepare that the load balanced query
results might differ among servers.

If there's enough demand, I would do such that enhancements to pgpool.
--
Tatsuo Ishii

> > >>>>Is there any other solution than a Cluster for our problem ?
> > >>>
> > >>>Bigger server, more CPUs/disks in one box.  Try to partition up your
> > >>>data some way such that it can be spread across multiple machines,
then
> > >>>if you need to combine the data have it be replicated using slony to
a
> > >>>big box that has a view which joins all the tables and do your big
> > >>>queries against that.
> > >>
> > >>But I'll arrive to limitation of a box size quickly I thing a 4
> > >> processors with 64 Gb of RAM ... and after ?
> >
> > Opteron.
>
> IBM Z-series, or other big iron.
>
> >
> > >Go to non-x86 hardware after if you're going to continue to increase
the
> > >size of the server.  Personally I think your better bet might be to
> > >figure out a way to partition up your data (isn't that what google
> > >does anyway?).
> > >
> > >    Stephen
>
> --
> Darcy Buskermolen
> Wavefire Technologies Corp.
> ph: 250.717.0200
> fx:  250.763.1759
> http://www.wavefire.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)