What is the status of Postgres support for any sort of multi-machine
scaling support? What are you meant to do once you've upgraded your
box and tuned the conf files as much as you can? But your query load
is just too high for a single machine?

Upgrading stock Dell boxes (I know we could be using better machines,
but I am trying to tackle the real issue) is not a hugely price
efficient way of getting extra performance, nor particularly scalable
in the long term.

So, when/is PG meant to be getting a decent partitioning system?
MySQL is getting one (eventually) which is apparently meant to be
similiar to Oracle's according to the docs. Clusgres does not appear
to be widely/or at all used, and info on it seems pretty thin on the
ground, so I am
not too keen on going with that. Is the real solution to multi-
machine partitioning (as in, not like MySQLs MERGE tables) on
PostgreSQL actually doing it in our application API? This seems like
a less than perfect solution once we want to add redundancy and
things into the mix.

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

Alex Stapleton wrote:
> What is the status of Postgres support for any sort of multi-machine
> scaling support? What are you meant to do once you've upgraded your box
> and tuned the conf files as much as you can? But your query load is
> just too high for a single machine?
>
> Upgrading stock Dell boxes (I know we could be using better machines,
> but I am trying to tackle the real issue) is not a hugely price
> efficient way of getting extra performance, nor particularly scalable
> in the long term.

Switch from Dell Xeon boxes, and go to Opterons. Seriously, Dell is
far away from Big Iron. I don't know what performance you are looking
for, but you can easily get into inserting 10M rows/day with quality
hardware.

But actually is it your SELECT load that is too high, or your INSERT
load, or something inbetween.

Because Slony is around if it is a SELECT problem.
http://gborg.postgresql.org/project/...rojdisplay.php

Basically, Slony is a Master/Slave replication system. So if you have
INSERT going into the Master, you can have as many replicated slaves,
which can handle your SELECT load.
Slony is an asynchronous replicator, so there is a time delay from the
INSERT until it will show up on a slave, but that time could be pretty
small.

This would require some application level support, since an INSERT goes
to a different place than a SELECT. But there has been some discussion
about pg_pool being able to spread the query load, and having it be
aware of the difference between a SELECT and an INSERT and have it route
the query to the correct host. The biggest problem being that functions
could cause a SELECT func() to actually insert a row, which pg_pool
wouldn't know about. There are 2 possible solutions, a) don't do that
when you are using this system, b) add some sort of comment hint so that
pg_pool can understand that the select is actually an INSERT, and needs
to be done on the master.

>
> So, when/is PG meant to be getting a decent partitioning system? MySQL
> is getting one (eventually) which is apparently meant to be similiar to
> Oracle's according to the docs. Clusgres does not appear to be
> widely/or at all used, and info on it seems pretty thin on the ground,
> so I am
> not too keen on going with that. Is the real solution to multi- machine
> partitioning (as in, not like MySQLs MERGE tables) on PostgreSQL
> actually doing it in our application API? This seems like a less than
> perfect solution once we want to add redundancy and things into the mix.

There is also PGCluster
http://pgfoundry.org/projects/pgcluster/

Which is trying to be more of a Synchronous multi-master system. I
haven't heard of Clusgres, so I'm guessing it is an older attempt, which
has been overtaken by pgcluster.

Just realize that clusters don't necessarily scale like you would want
them too. Because at some point you have to insert into the same table,
which means you need to hold a lock which prevents the other machine
from doing anything. And with synchronous replication, you have to wait
for all of the machines to get a copy of the data before you can say it
has been committed, which does *not* scale well with the number of machines.

If you can make it work, I think having a powerful master server, who
can finish an INSERT quickly, and then having a bunch of Slony slaves
with a middleman (like pg_pool) to do load balancing among them, is the
best way to scale up. There are still some requirements, like not having
to see the results of an INSERT instantly (though if you are using
hinting to pg_pool, you could hint that this query must be done on the
master, realizing that the more you do it, the more you slow everything
down).

John
=:->

PS> I don't know what functionality has been actually implemented in
pg_pool, just that it was discussed in the past. Slony-II is also in the
works.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFCgMgBJdeBCYSNAAMRAvK7AJ4qOLvV2K3kG0fiHeYSMZ yxWpjvFwCeMgZ4
KJvZX263H47G5nWxXF1rZic=
=MgTR
-----END PGP SIGNATURE-----

I think that perhaps he was trying to avoid having to buy "Big Iron" at all.

With all the Opteron v. Xeon around here, and talk of $30,000 machines,
perhaps it would be worth exploring the option of buying 10 cheapass
machines for $300 each. At the moment, that $300 buys you, from Dell, a
2.5Ghz Pentium 4 w/ 256mb of RAM and a 40Gb hard drive and gigabit ethernet.
The aggregate CPU and bandwidth is pretty stupendous, but not as easy to
harness as a single machine.

For those of us looking at batch and data warehousing applications, it would
be really handy to be able to partition databases, tables, and processing
load across banks of cheap hardware.

Yes, clustering solutions can distribute the data, and can even do it on a
per-table basis in some cases. This still leaves it up to the application's
logic to handle reunification of the data.

Ideas:
1. Create a table/storage type that consists of a select statement
on another machine. While I don't think the current executor is capable of
working on multiple nodes of an execution tree at the same time, it would be
great if it could offload a select of tuples from a remote table to an
entirely different server and merge the resulting data into the current
execution. I believe MySQL has this, and Oracle may implement it in another
way.

2. There is no #2 at this time, but I'm sure one can be
hypothesized.

....Google and other companies have definitely proved that one can harness
huge clusters of cheap hardware. It can't be _that_ hard, can it.


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailtogsql-performance-owner@postgresql.org] On Behalf Of John A Meinel
Sent: Tuesday, May 10, 2005 7:41 AM
To: Alex Stapleton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partitioning / Clustering

Alex Stapleton wrote:
> What is the status of Postgres support for any sort of multi-machine
> scaling support? What are you meant to do once you've upgraded your
> box and tuned the conf files as much as you can? But your query load
> is just too high for a single machine?
>
> Upgrading stock Dell boxes (I know we could be using better machines,
> but I am trying to tackle the real issue) is not a hugely price
> efficient way of getting extra performance, nor particularly scalable
> in the long term.

Switch from Dell Xeon boxes, and go to Opterons. Seriously, Dell is far
away from Big Iron. I don't know what performance you are looking for, but
you can easily get into inserting 10M rows/day with quality hardware.

But actually is it your SELECT load that is too high, or your INSERT load,
or something inbetween.

Because Slony is around if it is a SELECT problem.
http://gborg.postgresql.org/project/...rojdisplay.php

Basically, Slony is a Master/Slave replication system. So if you have INSERT
going into the Master, you can have as many replicated slaves, which can
handle your SELECT load.
Slony is an asynchronous replicator, so there is a time delay from the
INSERT until it will show up on a slave, but that time could be pretty
small.

This would require some application level support, since an INSERT goes to a
different place than a SELECT. But there has been some discussion about
pg_pool being able to spread the query load, and having it be aware of the
difference between a SELECT and an INSERT and have it route the query to the
correct host. The biggest problem being that functions could cause a SELECT
func() to actually insert a row, which pg_pool wouldn't know about. There
are 2 possible solutions, a) don't do that when you are using this system,
b) add some sort of comment hint so that pg_pool can understand that the
select is actually an INSERT, and needs to be done on the master.

>
> So, when/is PG meant to be getting a decent partitioning system?
> MySQL is getting one (eventually) which is apparently meant to be
> similiar to Oracle's according to the docs. Clusgres does not appear
> to be widely/or at all used, and info on it seems pretty thin on the
> ground, so I am not too keen on going with that. Is the real solution
> to multi- machine partitioning (as in, not like MySQLs MERGE tables)
> on PostgreSQL actually doing it in our application API? This seems
> like a less than perfect solution once we want to add redundancy and
> things into the mix.

There is also PGCluster
http://pgfoundry.org/projects/pgcluster/

Which is trying to be more of a Synchronous multi-master system. I haven't
heard of Clusgres, so I'm guessing it is an older attempt, which has been
overtaken by pgcluster.

Just realize that clusters don't necessarily scale like you would want them
too. Because at some point you have to insert into the same table, which
means you need to hold a lock which prevents the other machine from doing
anything. And with synchronous replication, you have to wait for all of the
machines to get a copy of the data before you can say it has been committed,
which does *not* scale well with the number of machines.

If you can make it work, I think having a powerful master server, who can
finish an INSERT quickly, and then having a bunch of Slony slaves with a
middleman (like pg_pool) to do load balancing among them, is the best way to
scale up. There are still some requirements, like not having to see the
results of an INSERT instantly (though if you are using hinting to pg_pool,
you could hint that this query must be done on the master, realizing that
the more you do it, the more you slow everything down).

John
=:->

PS> I don't know what functionality has been actually implemented in
pg_pool, just that it was discussed in the past. Slony-II is also in the
works.


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

On 10 May 2005, at 15:41, John A Meinel wrote:

> Alex Stapleton wrote:
>
>> What is the status of Postgres support for any sort of multi-machine
>> scaling support? What are you meant to do once you've upgraded
>> your box
>> and tuned the conf files as much as you can? But your query load is
>> just too high for a single machine?
>>
>> Upgrading stock Dell boxes (I know we could be using better machines,
>> but I am trying to tackle the real issue) is not a hugely price
>> efficient way of getting extra performance, nor particularly scalable
>> in the long term.
>>
>
> Switch from Dell Xeon boxes, and go to Opterons. Seriously, Dell is
> far away from Big Iron. I don't know what performance you are looking
> for, but you can easily get into inserting 10M rows/day with quality
> hardware.

Better hardware = More Efficient != More Scalable

> But actually is it your SELECT load that is too high, or your INSERT
> load, or something inbetween.
>
> Because Slony is around if it is a SELECT problem.
> http://gborg.postgresql.org/project/...rojdisplay.php
>
> Basically, Slony is a Master/Slave replication system. So if you have
> INSERT going into the Master, you can have as many replicated slaves,
> which can handle your SELECT load.
> Slony is an asynchronous replicator, so there is a time delay from the
> INSERT until it will show up on a slave, but that time could be pretty
> small.

<snip>

>
>>
>> So, when/is PG meant to be getting a decent partitioning system?
>> MySQL
>> is getting one (eventually) which is apparently meant to be
>> similiar to
>> Oracle's according to the docs. Clusgres does not appear to be
>> widely/or at all used, and info on it seems pretty thin on the
>> ground,
>> so I am
>> not too keen on going with that. Is the real solution to multi-
>> machine
>> partitioning (as in, not like MySQLs MERGE tables) on PostgreSQL
>> actually doing it in our application API? This seems like a less
>> than
>> perfect solution once we want to add redundancy and things into
>> the mix.
>>
>
> There is also PGCluster
> http://pgfoundry.org/projects/pgcluster/
>
> Which is trying to be more of a Synchronous multi-master system. I
> haven't heard of Clusgres, so I'm guessing it is an older attempt,
> which
> has been overtaken by pgcluster.
>
> Just realize that clusters don't necessarily scale like you would want
> them too. Because at some point you have to insert into the same
> table,
> which means you need to hold a lock which prevents the other machine
> from doing anything. And with synchronous replication, you have to
> wait
> for all of the machines to get a copy of the data before you can
> say it
> has been committed, which does *not* scale well with the number of
> machines.

This is why I mention partitioning. It solves this issue by storing
different data sets on different machines under the same schema.
These seperate chunks of the table can then be replicated as well for
data redundancy and so on. MySQL are working on these things, but PG
just has a bunch of third party extensions, I wonder why these are
not being integrated into the main trunk :/ Thanks for pointing me to
PGCluster though. It looks like it should be better than Slony at least.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On 10 May 2005, at 16:02, Adam Haberlach wrote:

>
> I think that perhaps he was trying to avoid having to buy "Big
> Iron" at all.

You would be right. Although we are not against paying a bit more
than $300 for a server

> With all the Opteron v. Xeon around here, and talk of $30,000
> machines,
> perhaps it would be worth exploring the option of buying 10 cheapass
> machines for $300 each. At the moment, that $300 buys you, from
> Dell, a
> 2.5Ghz Pentium 4 w/ 256mb of RAM and a 40Gb hard drive and gigabit
> ethernet.
> The aggregate CPU and bandwidth is pretty stupendous, but not as
> easy to
> harness as a single machine.

<snip>

> Yes, clustering solutions can distribute the data, and can even do
> it on a
> per-table basis in some cases. This still leaves it up to the
> application's
> logic to handle reunification of the data.

If your going to be programming that sort of logic into your API in
the beginning, it's not too much more work to add basic replication,
load balancing and partitioning into it either. But the DB should be
able to do it for you, adding that stuff in later is often more
difficult and less likely to get done.

> Ideas:
> 1. Create a table/storage type that consists of a select statement
> on another machine. While I don't think the current executor is
> capable of
> working on multiple nodes of an execution tree at the same time, it
> would be
> great if it could offload a select of tuples from a remote table to an
> entirely different server and merge the resulting data into the
> current
> execution. I believe MySQL has this, and Oracle may implement it
> in another
> way.

MySQL sort of has this, it's not as good as Oracle's though.
Apparently there is a much better version of it in 5.1 though, that
should make it to stable sometime next year I imagine.

> 2. There is no #2 at this time, but I'm sure one can be
> hypothesized.

I would of thought a particularly smart version of pg_pool could do
it. It could partition data to different servers if it knew which
columns to key by on each table.

> ...Google and other companies have definitely proved that one can
> harness
> huge clusters of cheap hardware. It can't be _that_ hard, can it.

I shudder to think how much the "Big Iron" equivalent of a google
data-center would cost.

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailtogsql-performance-owner@postgresql.org] On Behalf Of John A
> Meinel
> Sent: Tuesday, May 10, 2005 7:41 AM
> To: Alex Stapleton
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Partitioning / Clustering
>
> Alex Stapleton wrote:
>
>> What is the status of Postgres support for any sort of multi-machine
>> scaling support? What are you meant to do once you've upgraded your
>> box and tuned the conf files as much as you can? But your query load
>> is just too high for a single machine?
>>
>> Upgrading stock Dell boxes (I know we could be using better machines,
>> but I am trying to tackle the real issue) is not a hugely price
>> efficient way of getting extra performance, nor particularly scalable
>> in the long term.
>>
>
> Switch from Dell Xeon boxes, and go to Opterons. Seriously, Dell
> is far
> away from Big Iron. I don't know what performance you are looking
> for, but
> you can easily get into inserting 10M rows/day with quality hardware.
>
> But actually is it your SELECT load that is too high, or your
> INSERT load,
> or something inbetween.
>
> Because Slony is around if it is a SELECT problem.
> http://gborg.postgresql.org/project/...rojdisplay.php
>
> Basically, Slony is a Master/Slave replication system. So if you
> have INSERT
> going into the Master, you can have as many replicated slaves,
> which can
> handle your SELECT load.
> Slony is an asynchronous replicator, so there is a time delay from the
> INSERT until it will show up on a slave, but that time could be pretty
> small.
>
> This would require some application level support, since an INSERT
> goes to a
> different place than a SELECT. But there has been some discussion
> about
> pg_pool being able to spread the query load, and having it be aware
> of the
> difference between a SELECT and an INSERT and have it route the
> query to the
> correct host. The biggest problem being that functions could cause
> a SELECT
> func() to actually insert a row, which pg_pool wouldn't know about.
> There
> are 2 possible solutions, a) don't do that when you are using this
> system,
> b) add some sort of comment hint so that pg_pool can understand
> that the
> select is actually an INSERT, and needs to be done on the master.
>
>
>>
>> So, when/is PG meant to be getting a decent partitioning system?
>> MySQL is getting one (eventually) which is apparently meant to be
>> similiar to Oracle's according to the docs. Clusgres does not appear
>> to be widely/or at all used, and info on it seems pretty thin on the
>> ground, so I am not too keen on going with that. Is the real solution
>> to multi- machine partitioning (as in, not like MySQLs MERGE tables)
>> on PostgreSQL actually doing it in our application API? This seems
>> like a less than perfect solution once we want to add redundancy and
>> things into the mix.
>>
>
> There is also PGCluster
> http://pgfoundry.org/projects/pgcluster/
>
> Which is trying to be more of a Synchronous multi-master system. I
> haven't
> heard of Clusgres, so I'm guessing it is an older attempt, which
> has been
> overtaken by pgcluster.
>
> Just realize that clusters don't necessarily scale like you would
> want them
> too. Because at some point you have to insert into the same table,
> which
> means you need to hold a lock which prevents the other machine from
> doing
> anything. And with synchronous replication, you have to wait for
> all of the
> machines to get a copy of the data before you can say it has been
> committed,
> which does *not* scale well with the number of machines.
>
> If you can make it work, I think having a powerful master server,
> who can
> finish an INSERT quickly, and then having a bunch of Slony slaves
> with a
> middleman (like pg_pool) to do load balancing among them, is the
> best way to
> scale up. There are still some requirements, like not having to see
> the
> results of an INSERT instantly (though if you are using hinting to
> pg_pool,
> you could hint that this query must be done on the master,
> realizing that
> the more you do it, the more you slow everything down).
>
> John
> =:->
>
> PS> I don't know what functionality has been actually implemented in
> pg_pool, just that it was discussed in the past. Slony-II is also
> in the
> works.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>


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

> exploring the option of buying 10 cheapass
> machines for $300 each. At the moment, that $300 buys you, from Dell, a
> 2.5Ghz Pentium 4

Buy cheaper ass Dells with an AMD 64 3000+. Beats the crap out of the 2.5
GHz Pentium, especially for PostgreSQL.

See the thread "Whence the Opterons" for more....

Rick

pgsql-performance-owner@postgresql.org wrote on 05/10/2005 10:02:50 AM:

>
> I think that perhaps he was trying to avoid having to buy "Big Iron" at
all.
>
> With all the Opteron v. Xeon around here, and talk of $30,000 machines,
> perhaps it would be worth exploring the option of buying 10 cheapass
> machines for $300 each. At the moment, that $300 buys you, from Dell, a
> 2.5Ghz Pentium 4 w/ 256mb of RAM and a 40Gb hard drive and gigabit
ethernet.
> The aggregate CPU and bandwidth is pretty stupendous, but not as easy to
> harness as a single machine.
>
> For those of us looking at batch and data warehousing applications, it
would
> be really handy to be able to partition databases, tables, and processing
> load across banks of cheap hardware.
>
> Yes, clustering solutions can distribute the data, and can even do it on
a
> per-table basis in some cases. This still leaves it up to the
application's
> logic to handle reunification of the data.
>
> Ideas:
> 1. Create a table/storage type that consists of a select statement
> on another machine. While I don't think the current executor is capable
of
> working on multiple nodes of an execution tree at the same time, it would
be
> great if it could offload a select of tuples from a remote table to an
> entirely different server and merge the resulting data into the current
> execution. I believe MySQL has this, and Oracle may implement it in
another
> way.
>
> 2. There is no #2 at this time, but I'm sure one can be
> hypothesized.
>
> ...Google and other companies have definitely proved that one can harness
> huge clusters of cheap hardware. It can't be _that_ hard, can it.
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailtogsql-performance-owner@postgresql.org] On Behalf Of John A
Meinel
> Sent: Tuesday, May 10, 2005 7:41 AM
> To: Alex Stapleton
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Partitioning / Clustering
>
> Alex Stapleton wrote:
> > What is the status of Postgres support for any sort of multi-machine
> > scaling support? What are you meant to do once you've upgraded your
> > box and tuned the conf files as much as you can? But your query load
> > is just too high for a single machine?
> >
> > Upgrading stock Dell boxes (I know we could be using better machines,
> > but I am trying to tackle the real issue) is not a hugely price
> > efficient way of getting extra performance, nor particularly scalable
> > in the long term.
>
> Switch from Dell Xeon boxes, and go to Opterons. Seriously, Dell is
far
> away from Big Iron. I don't know what performance you are looking for,
but
> you can easily get into inserting 10M rows/day with quality hardware.
>
> But actually is it your SELECT load that is too high, or your INSERT
load,
> or something inbetween.
>
> Because Slony is around if it is a SELECT problem.
> http://gborg.postgresql.org/project/...rojdisplay.php
>
> Basically, Slony is a Master/Slave replication system. So if you have
INSERT
> going into the Master, you can have as many replicated slaves, which can
> handle your SELECT load.
> Slony is an asynchronous replicator, so there is a time delay from the
> INSERT until it will show up on a slave, but that time could be pretty
> small.
>
> This would require some application level support, since an INSERT goes
to a
> different place than a SELECT. But there has been some discussion about
> pg_pool being able to spread the query load, and having it be aware of
the
> difference between a SELECT and an INSERT and have it route the query to
the
> correct host. The biggest problem being that functions could cause a
SELECT
> func() to actually insert a row, which pg_pool wouldn't know about. There
> are 2 possible solutions, a) don't do that when you are using this
system,
> b) add some sort of comment hint so that pg_pool can understand that the
> select is actually an INSERT, and needs to be done on the master.
>
> >
> > So, when/is PG meant to be getting a decent partitioning system?
> > MySQL is getting one (eventually) which is apparently meant to be
> > similiar to Oracle's according to the docs. Clusgres does not appear
> > to be widely/or at all used, and info on it seems pretty thin on the
> > ground, so I am not too keen on going with that. Is the real solution
> > to multi- machine partitioning (as in, not like MySQLs MERGE tables)
> > on PostgreSQL actually doing it in our application API? This seems
> > like a less than perfect solution once we want to add redundancy and
> > things into the mix.
>
> There is also PGCluster
> http://pgfoundry.org/projects/pgcluster/
>
> Which is trying to be more of a Synchronous multi-master system. I
haven't
> heard of Clusgres, so I'm guessing it is an older attempt, which has been
> overtaken by pgcluster.
>
> Just realize that clusters don't necessarily scale like you would want
them
> too. Because at some point you have to insert into the same table, which
> means you need to hold a lock which prevents the other machine from doing
> anything. And with synchronous replication, you have to wait for all of
the
> machines to get a copy of the data before you can say it has been
committed,
> which does *not* scale well with the number of machines.
>
> If you can make it work, I think having a powerful master server, who can
> finish an INSERT quickly, and then having a bunch of Slony slaves with a
> middleman (like pg_pool) to do load balancing among them, is the best way
to
> scale up. There are still some requirements, like not having to see the
> results of an INSERT instantly (though if you are using hinting to
pg_pool,
> you could hint that this query must be done on the master, realizing that
> the more you do it, the more you slow everything down).
>
> John
> =:->
>
> PS> I don't know what functionality has been actually implemented in
> pg_pool, just that it was discussed in the past. Slony-II is also in the
> works.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


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

Adam Haberlach wrote:
> I think that perhaps he was trying to avoid having to buy "Big Iron" at all.
>
> With all the Opteron v. Xeon around here, and talk of $30,000 machines,
> perhaps it would be worth exploring the option of buying 10 cheapass
> machines for $300 each. At the moment, that $300 buys you, from Dell, a
> 2.5Ghz Pentium 4 w/ 256mb of RAM and a 40Gb hard drive and gigabit ethernet.
> The aggregate CPU and bandwidth is pretty stupendous, but not as easy to
> harness as a single machine.
>
> For those of us looking at batch and data warehousing applications, it would
> be really handy to be able to partition databases, tables, and processing
> load across banks of cheap hardware.
>
> Yes, clustering solutions can distribute the data, and can even do it on a
> per-table basis in some cases. This still leaves it up to the application's
> logic to handle reunification of the data.

Sure. A lot of this is application dependent, though. For instance
foreign key constraints. In a general cluster solution, you would allow
foreign keys across partitions. I have a feeling this would be extra
slow, and hard to do correctly. Multi-machine transactions are also a
difficulty, since WAL now has to take into account all machines, and you
have to wait for fsync on all of them.

I'm not sure how Oracle does it, but these things seem like they prevent
clustering from really scaling very well.

>
> Ideas:
> 1. Create a table/storage type that consists of a select statement
> on another machine. While I don't think the current executor is capable of
> working on multiple nodes of an execution tree at the same time, it would be
> great if it could offload a select of tuples from a remote table to an
> entirely different server and merge the resulting data into the current
> execution. I believe MySQL has this, and Oracle may implement it in another
> way.
>
> 2. There is no #2 at this time, but I'm sure one can be
> hypothesized.
>
> ...Google and other companies have definitely proved that one can harness
> huge clusters of cheap hardware. It can't be _that_ hard, can it.

Again, it depends on the application. A generic database with lots of
cross reference integrity checking does not work on a cluster very well.
A very distributed db where you don't worry about cross references does
scale. Google made a point of making their application work in a
distributed manner.

In the other post he mentions that pg_pool could naturally split out the
rows into different machines based on partitioning, etc. I would argue
that it is more of a custom pool daemon based on the overall
application. Because you have to start dealing with things like
cross-machine joins. Who handles that? the pool daemon has to, since it
is the only thing that talks to both tables. I think you could certainly
write a reasonably simple application specific daemon where all of the
clients send their queries to, and it figures out where they need to go,
and aggregates them as necessary. But a fully generic one is *not*
simple at all, and I think is far out of the scope of something like
pg_pool.

I'm guessing that PGCluster is looking at working on that, and it might
be true that pg_pool is thinking about it. But just thinking about the
very simple query:

SELECT row1, row2 FROM table1_on_machine_a NATURAL JOIN table2_on_machine_b
WHERE restrict_table_1 AND restrict_table_2
AND restrict_1_based_on_2;

This needs to be broken into something like:

SELECT row1 FROM table1_on_machine_a
WHERE restrict_table_1
ORDER BY join_column;
SELECT row2 FROM table2_on_machine_b
WHERE restrict_table_2
ORDER BY join_column;

Then these rows need to be merge_joined, and the restrict_1_based_on_2
needs to be applied.
This is in no way trivial, and I think it is outside the scope of
pg_pool. Now maybe if you restrict yourself so that each query stays
within one machine you can make it work. Or write your own app to handle
some of this transparently for the clients. But I would expect to make
the problem feasible, it would not be a generic solution.

Maybe I'm off base, I don't really keep track of pg_pool/PGCluster/etc.
But I can see that the problem is very difficult. Not at the very least,
this is a simple query. And it doesn't even do optimizations. You might
actually prefer the above to be done with a Nestloop style, where
table_1 is selected, and then for each row you do a single index select
on table_2. But how is your app going to know that? It has to have the
statistics from the backend databases. And if it has to place an extra
query to get those statistics, you just hurt your scalability even more.
Whereas big-iron already has all the statistics, and can optimize the
query plan.

Perhaps pg_cluster will handle this, by maintaining full statistics
across the cluster on each machine, so that more optimal queries can be
performed. I don't really know.

John
=:->

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFCgNd1JdeBCYSNAAMRAh4wAKCQ7MKHlM/DojbGapkhP4EQNfXziQCg028j
ofzXk6pjacA7SvQqKJ3g1A0=
=XU9d
-----END PGP SIGNATURE-----

> SELECT row1, row2 FROM table1_on_machine_a NATURAL JOIN
> table2_on_machine_b
> WHERE restrict_table_1 AND restrict_table_2
> AND restrict_1_based_on_2;

I don't think that's ever going to be efficient...
What would be efficient would be, for instance, a Join of a part of a
table against another part of another table which both happen to be on the
same machine, because the partitioning was done with this in mind (ie. for
instance partitioning on client_id and keeping the information for each
client on the same machine).

You could build your smart pool daemon in pl/pgsql and use dblink ! At
least you have the query parser built-in.

I wonder how Oracle does it

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Alex,

> This is why I mention partitioning. It solves this issue by storing *
> different data sets on different machines under the same schema. *

That's clustering, actually. Partitioning is simply dividing up a table into
chunks and using the chunks intelligently. Putting those chunks on seperate
machines is another thing entirely.

We're working on partitioning through the Bizgres sub-project:
www.bizgres.org / http://pgfoundry.org/projects/bizgres/
.... and will be pushing it to the main PostgreSQL when we have something.

I invite you to join the mailing list.

> These seperate chunks of the table can then be replicated as well for *
> data redundancy and so on. MySQL are working on these things,

Don't hold your breath. MySQL, to judge by their first "clustering"
implementation, has a *long* way to go before they have anything usable. In
fact, at OSCON their engineers were asking Jan Wieck for advice.

If you have $$$ to shell out, my employer (GreenPlum) has a multi-machine
distributed version of PostgreSQL. It's proprietary, though.
www.greenplum.com.

If you have more time than money, I understand that Stanford is working on
this problem:
http://www-db.stanford.edu/~bawa/

But, overall, some people on this list are very mistaken in thinking it's an
easy problem. GP has devoted something like 5 engineers for 3 years to
develop their system. Oracle spent over $100 million to develop RAC.

> but PG *
> just has a bunch of third party extensions, I wonder why these are *
> not being integrated into the main trunk :/

Because it represents a host of complex functionality which is not applicable
to most users? Because there are 4 types of replication and 3 kinds of
clusering and not all users want the same kind?

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Quoting Richard_D_Levine@raytheon.com:

> > exploring the option of buying 10 cheapass
> > machines for $300 each. At the moment, that $300 buys you, from
> Dell, a
> > 2.5Ghz Pentium 4
>
> Buy cheaper ass Dells with an AMD 64 3000+. Beats the crap out of
> the 2.5
> GHz Pentium, especially for PostgreSQL.

Whence "Dells with an AMD 64" ?? Perhaps you skimmed:

http://www.thestreet.com/tech/kcswanson/10150604.html
or
http://www.eweek.com/article2/0,1759,1553822,00.asp





---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match