Thread

PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T14:03:31Z

Dear community,

My company, which I actually represent, is a fervent user of PostgreSQL.
We used to make all our applications using PostgreSQL for more than 5 years.
We usually do classical client/server applications under Linux, and Web 
interface (php, perl, C/C++). We used to manage also public web services with 
10/15 millions records and up to 8 millions pages view by month.

Now we are in front of a new need, but we do not find any good solution with 
PostgreSQL.
We need to make a sort of directory of millions of data growing about 4/8 
millions per month, and to be able to be used by many users from the web. In 
order to do this, our solution need to be able to run perfectly with many 
insert and many select access (done before each insert, and done by web site 
visitors). We will also need to make a search engine for the millions of data 
(140/150 millions records at the immediate beginning) ... No it's not google, 
but the kind of volume of data stored in the main table is similar.

Then ... we have made some tests, with the actual servers we have here, like a 
Bi-Pro Xeon 2.8 Ghz, with 4 Gb of RAM and the result of the cumulative 
inserts, and select access is slowing down the service really quickly ... 
(Load average is going up to 10 really quickly on the database).

We were at this moment thinking about a Cluster solution ... We saw on the 
Internet many solution talking about Cluster solution using MySQL ... but 
nothing about PostgreSQL ... the idea is to use several servers to make a 
sort of big virtual server using the disk space of each server as one, and 
having the ability to use the CPU and RAM of each servers in order to 
maintain good service performance ...one can imagin it is like a GFS but 
dedicated to postgreSQL...

Is there any solution with PostgreSQL matching these needs ... ?
Do we have to backport our development to MySQL for this kind of problem ?
Is there any other solution than a Cluster for our problem ?

Looking for your reply,

Regards,
-- 
Hervé

Re: PostgreSQL clustering VS MySQL clustering

Jean-Max Reymond <jmreymond@gmail.com> — 2005-01-20T14:23:03Z

On Thu, 20 Jan 2005 15:03:31 +0100, Hervé Piedvache <herve@elma.fr> wrote:

> We were at this moment thinking about a Cluster solution ... We saw on the
> Internet many solution talking about Cluster solution using MySQL ... but
> nothing about PostgreSQL ... the idea is to use several servers to make a
> sort of big virtual server using the disk space of each server as one, and
> having the ability to use the CPU and RAM of each servers in order to
> maintain good service performance ...one can imagin it is like a GFS but
> dedicated to postgreSQL...
> 

forget mysql cluster for now.
We have a small database which size is 500 Mb.
It is not possible to load these base in a computer with 2 Mb of RAM
and loading the base in RAM is required.
So, we shrink the database and it is ok with 350 Mb to fit in the 2 Gb RAM.
First tests of performance on a basic request: 500x slower, yes 500x.
This issue is reported to mysql team  but no answer (and correction)

Actually, the solution is running with a replication database: 1 node
for write request and all the other nodes for read requests and the
load balancer is made with round robin solution.

-- 
Jean-Max Reymond
CKR Solutions
Nice France
http://www.ckr-solutions.com

Re: PostgreSQL clustering VS MySQL clustering

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-01-20T14:24:05Z

> Is there any solution with PostgreSQL matching these needs ... ?

You want: http://www.slony.info/

> Do we have to backport our development to MySQL for this kind of problem ?
> Is there any other solution than a Cluster for our problem ?

Well, Slony does replication which is basically what you want :)

Only master->slave though, so you will need to have all inserts go via 
the master server, but selects can come off any server.

Chris

Re: PostgreSQL clustering VS MySQL clustering

Stephen Frost <sfrost@snowman.net> — 2005-01-20T14:30:45Z

* 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.

> Do we have to backport our development to MySQL for this kind of problem ?

Well, hopefully not. :)

> 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.

Just some thoughts.

	Stephen

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T14:36:08Z

Le Jeudi 20 Janvier 2005 15:24, Christopher Kings-Lynne a écrit :
> > Is there any solution with PostgreSQL matching these needs ... ?
>
> You want: http://www.slony.info/
>
> > Do we have to backport our development to MySQL for this kind of problem
> > ? Is there any other solution than a Cluster for our problem ?
>
> Well, Slony does replication which is basically what you want :)
>
> Only master->slave though, so you will need to have all inserts go via
> the master server, but selects can come off any server.

Sorry but I don't agree with this ... Slony is a replication solution ... I 
don't need replication ... what will I do when my database will grow up to 50 
Gb ... I'll need more than 50 Gb of RAM on each server ???
This solution is not very realistic for me ...

I need a Cluster solution not a replication one or explain me in details how I 
will do for managing the scalabilty of my database ...

regards,
-- 
Hervé

Re: PostgreSQL clustering VS MySQL clustering

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-01-20T14:38:34Z

> Sorry but I don't agree with this ... Slony is a replication solution ... I 
> don't need replication ... what will I do when my database will grow up to 50 
> Gb ... I'll need more than 50 Gb of RAM on each server ???
> This solution is not very realistic for me ...
> 
> I need a Cluster solution not a replication one or explain me in details how I 
> will do for managing the scalabilty of my database ...

Buy Oracle

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T14:39:49Z

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 ...

> > Do we have to backport our development to MySQL for this kind of problem
> > ?
>
> Well, hopefully not. :)

I hope so ;o)

> > 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 ?

regards,
-- 
Hervé

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T14:42:06Z

Le Jeudi 20 Janvier 2005 15:38, Christopher Kings-Lynne a écrit :
> > Sorry but I don't agree with this ... Slony is a replication solution ...
> > I don't need replication ... what will I do when my database will grow up
> > to 50 Gb ... I'll need more than 50 Gb of RAM on each server ???
> > This solution is not very realistic for me ...
> >
> > I need a Cluster solution not a replication one or explain me in details
> > how I will do for managing the scalabilty of my database ...
>
> Buy Oracle

I think this is not my solution ... sorry I'm talking about finding a 
PostgreSQL solution ... 
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T14:44:16Z

Hervé Piedvache wrote:

>Dear community,
>
>My company, which I actually represent, is a fervent user of PostgreSQL.
>We used to make all our applications using PostgreSQL for more than 5 years.
>We usually do classical client/server applications under Linux, and Web 
>interface (php, perl, C/C++). We used to manage also public web services with 
>10/15 millions records and up to 8 millions pages view by month.
>  
>
Depending on your needs either:

Slony: www.slony.info

or

Replicator: www.commandprompt.com

Will both do what you want. Replicator is easier to setup but
Slony is free.

Sincerely,

Joshua D. Drake



>Now we are in front of a new need, but we do not find any good solution with 
>PostgreSQL.
>We need to make a sort of directory of millions of data growing about 4/8 
>millions per month, and to be able to be used by many users from the web. In 
>order to do this, our solution need to be able to run perfectly with many 
>insert and many select access (done before each insert, and done by web site 
>visitors). We will also need to make a search engine for the millions of data 
>(140/150 millions records at the immediate beginning) ... No it's not google, 
>but the kind of volume of data stored in the main table is similar.
>
>Then ... we have made some tests, with the actual servers we have here, like a 
>Bi-Pro Xeon 2.8 Ghz, with 4 Gb of RAM and the result of the cumulative 
>inserts, and select access is slowing down the service really quickly ... 
>(Load average is going up to 10 really quickly on the database).
>
>We were at this moment thinking about a Cluster solution ... We saw on the 
>Internet many solution talking about Cluster solution using MySQL ... but 
>nothing about PostgreSQL ... the idea is to use several servers to make a 
>sort of big virtual server using the disk space of each server as one, and 
>having the ability to use the CPU and RAM of each servers in order to 
>maintain good service performance ...one can imagin it is like a GFS but 
>dedicated to postgreSQL...
>
>Is there any solution with PostgreSQL matching these needs ... ?
>Do we have to backport our development to MySQL for this kind of problem ?
>Is there any other solution than a Cluster for our problem ?
>
>Looking for your reply,
>
>Regards,
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Stephen Frost <sfrost@snowman.net> — 2005-01-20T14:44:16Z

* 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.

> > > 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 ?

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

Re: PostgreSQL clustering VS MySQL clustering

Jeff <threshar@torgo.978.org> — 2005-01-20T14:48:07Z

On Jan 20, 2005, at 9:36 AM, Hervé Piedvache wrote:

> Sorry but I don't agree with this ... Slony is a replication solution 
> ... I
> don't need replication ... what will I do when my database will grow 
> up to 50
> Gb ... I'll need more than 50 Gb of RAM on each server ???

Slony doesn't use much ram. The mysql clustering product, ndb I believe 
it is called, requires all data fit in RAM. (At least, it used to).  
What you'll need is disk space.

As for a cluster I think you are thinking of multi-master replication.

You should look into what others have said about trying to partiition 
data among several boxes and then join the results together.

Or you could fork over  hundreds of thousands of dollars for Oracle's 
RAC.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T14:49:56Z

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.

>  
>
>>>>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.


>
>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
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-01-20T14:51:21Z

>>>Sorry but I don't agree with this ... Slony is a replication solution ...
>>>I don't need replication ... what will I do when my database will grow up
>>>to 50 Gb ... I'll need more than 50 Gb of RAM on each server ???
>>>This solution is not very realistic for me ...
>>>
>>>I need a Cluster solution not a replication one or explain me in details
>>>how I will do for managing the scalabilty of my database ...
>>
>>Buy Oracle
> 
> 
> I think this is not my solution ... sorry I'm talking about finding a 
> PostgreSQL solution ... 

My point being is that there is no free solution.  There simply isn't. 
I don't know why you insist on keeping all your data in RAM, but the 
mysql cluster requires that ALL data MUST fit in RAM all the time.

PostgreSQL has replication, but not partitioning (which is what you want).

So, your only option is Oracle or another very expensive commercial 
database.

Chris

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T14:54:23Z

Le Jeudi 20 Janvier 2005 15:48, Jeff a écrit :
> On Jan 20, 2005, at 9:36 AM, Hervé Piedvache wrote:
> > Sorry but I don't agree with this ... Slony is a replication solution
> > ... I
> > don't need replication ... what will I do when my database will grow
> > up to 50
> > Gb ... I'll need more than 50 Gb of RAM on each server ???
>
> Slony doesn't use much ram. The mysql clustering product, ndb I believe
> it is called, requires all data fit in RAM. (At least, it used to).
> What you'll need is disk space.

Slony do not use RAM ... but PostgreSQL will need RAM for accessing a database 
of 50 Gb ... so having two servers with the same configuration replicated by 
slony do not slove the problem of the scalability of the database ...

> As for a cluster I think you are thinking of multi-master replication.

No I'm really thinking about a Cluster solution ... having several servers 
making one big virtual server to have several processors, and many RAM in 
many boxes ...

> You should look into what others have said about trying to partiition
> data among several boxes and then join the results together.

??? Who talk about this ?

> Or you could fork over  hundreds of thousands of dollars for Oracle's
> RAC.

No please do not talk about this again ... I'm looking about a PostgreSQL 
solution ... I know RAC ... and I'm not able to pay for a RAC certify 
hardware configuration plus a RAC Licence.

Regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-01-20T14:58:42Z

>>Or you could fork over  hundreds of thousands of dollars for Oracle's
>>RAC.
> 
> 
> No please do not talk about this again ... I'm looking about a PostgreSQL 
> solution ... I know RAC ... and I'm not able to pay for a RAC certify 
> hardware configuration plus a RAC Licence.

There is absolutely zero PostgreSQL solution...

You may have to split the data yourself onto two independent db servers 
and combine the results somehow in your application.

Chris

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T15:00:47Z

Joshua,

Le Jeudi 20 Janvier 2005 15:44, Joshua D. Drake a écrit :
> Hervé Piedvache wrote:
> >
> >My company, which I actually represent, is a fervent user of PostgreSQL.
> >We used to make all our applications using PostgreSQL for more than 5
> > years. We usually do classical client/server applications under Linux,
> > and Web interface (php, perl, C/C++). We used to manage also public web
> > services with 10/15 millions records and up to 8 millions pages view by
> > month.
>
> Depending on your needs either:
>
> Slony: www.slony.info
>
> or
>
> Replicator: www.commandprompt.com
>
> Will both do what you want. Replicator is easier to setup but
> Slony is free.

No ... as I have said ... how I'll manage a database getting a table of may be 
250 000 000 records ? I'll need incredible servers ... to get quick access or 
index reading ... no ?

So what we would like to get is a pool of small servers able to make one 
virtual server ... for that is called a Cluster ... no ?

I know they are not using PostgreSQL ... but how a company like Google do to 
get an incredible database in size and so quick access ?

regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T15:02:39Z

Le Jeudi 20 Janvier 2005 15:51, Christopher Kings-Lynne a écrit :
> >>>Sorry but I don't agree with this ... Slony is a replication solution
> >>> ... I don't need replication ... what will I do when my database will
> >>> grow up to 50 Gb ... I'll need more than 50 Gb of RAM on each server
> >>> ??? This solution is not very realistic for me ...
> >>>
> >>>I need a Cluster solution not a replication one or explain me in details
> >>>how I will do for managing the scalabilty of my database ...
> >>
> >>Buy Oracle
> >
> > I think this is not my solution ... sorry I'm talking about finding a
> > PostgreSQL solution ...
>
> My point being is that there is no free solution.  There simply isn't.
> I don't know why you insist on keeping all your data in RAM, but the
> mysql cluster requires that ALL data MUST fit in RAM all the time.

I don't insist about have data in RAM .... but when you use PostgreSQL with 
big database you know that for quick access just for reading the index file 
for example it's better to have many RAM as possible ... I just want to be 
able to get a quick access with a growing and growind database ...

> PostgreSQL has replication, but not partitioning (which is what you want).

:o(

> So, your only option is Oracle or another very expensive commercial
> database.

That's not a good news ...
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T15:03:01Z

>
>No please do not talk about this again ... I'm looking about a PostgreSQL 
>solution ... I know RAC ... and I'm not able to pay for a RAC certify 
>hardware configuration plus a RAC Licence.
>  
>
What you want does not exist for PostgreSQL. You will either
have to build it yourself or pay somebody to build it for you.

Sincerely,

Joshua D. Drake



>Regards,
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T15:04:19Z


>So what we would like to get is a pool of small servers able to make one 
>virtual server ... for that is called a Cluster ... no ?
>
>I know they are not using PostgreSQL ... but how a company like Google do to 
>get an incredible database in size and so quick access ?
>  
>
You could use dblink with multiple servers across data partitions
within PostgreSQL but I don't know how fast that would be.

J



>regards,
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T15:05:25Z

Christopher Kings-Lynne wrote:

>>> Or you could fork over  hundreds of thousands of dollars for Oracle's
>>> RAC.
>>
>>
>>
>> No please do not talk about this again ... I'm looking about a 
>> PostgreSQL solution ... I know RAC ... and I'm not able to pay for a 
>> RAC certify hardware configuration plus a RAC Licence.
>
>
> There is absolutely zero PostgreSQL solution...


I just replied the same thing but then I was thinking. Couldn't he use 
multiple databases
over multiple servers with dblink?

It is not exactly how I would want to do it, but it would provide what 
he needs I think???

Sincerely,

Joshua D. Drake



>
> You may have to split the data yourself onto two independent db 
> servers and combine the results somehow in your application.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Stephen Frost <sfrost@snowman.net> — 2005-01-20T15:07:37Z

* Herv? Piedvache (herve@elma.fr) wrote:
> I know they are not using PostgreSQL ... but how a company like Google do to 
> get an incredible database in size and so quick access ?

They segment their data across multiple machines and have an algorithm
which tells the application layer which machine to contact for what
data.

	Stephen

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T15:07:51Z

Le Jeudi 20 Janvier 2005 16:05, Joshua D. Drake a écrit :
> Christopher Kings-Lynne wrote:
> >>> Or you could fork over  hundreds of thousands of dollars for Oracle's
> >>> RAC.
> >>
> >> No please do not talk about this again ... I'm looking about a
> >> PostgreSQL solution ... I know RAC ... and I'm not able to pay for a
> >> RAC certify hardware configuration plus a RAC Licence.
> >
> > There is absolutely zero PostgreSQL solution...
>
> I just replied the same thing but then I was thinking. Couldn't he use
> multiple databases
> over multiple servers with dblink?
>
> It is not exactly how I would want to do it, but it would provide what
> he needs I think???

Yes seems to be the only solution ... but I'm a little disapointed about 
this ... could you explain me why there is not this kind of 
functionnality ... it seems to be a real need for big applications no ?

Thanks all for your answers ...
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Stephen Frost <sfrost@snowman.net> — 2005-01-20T15:08:47Z

* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> PostgreSQL has replication, but not partitioning (which is what you want).

It doesn't have multi-server partitioning..  It's got partitioning
within a single server (doesn't it?  I thought it did, I know it was
discussed w/ the guy from Cox Communications and I thought he was using
it :).

> So, your only option is Oracle or another very expensive commercial 
> database.

Or partition the data at the application layer.

	Stephen

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T15:12:42Z

>> then I was thinking. Couldn't he use
>>multiple databases
>>over multiple servers with dblink?
>>
>>It is not exactly how I would want to do it, but it would provide what
>>he needs I think???
>>    
>>
>
>Yes seems to be the only solution ... but I'm a little disapointed about 
>this ... could you explain me why there is not this kind of 
>functionnality ... it seems to be a real need for big applications no ?
>  
>
Because it is really, really hard to do correctly and hard
equals expensive.

Sincerely,

Joshua D. Drake



>Thanks all for your answers ...
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Steve Wampler <swampler@noao.edu> — 2005-01-20T15:14:28Z

Hervé Piedvache wrote:

> 
> No ... as I have said ... how I'll manage a database getting a table of may be 
> 250 000 000 records ? I'll need incredible servers ... to get quick access or 
> index reading ... no ?
> 
> So what we would like to get is a pool of small servers able to make one 
> virtual server ... for that is called a Cluster ... no ?
> 
> I know they are not using PostgreSQL ... but how a company like Google do to 
> get an incredible database in size and so quick access ?

Probably by carefully partitioning their data.  I can't imagine anything
being fast on a single table in 250,000,000 tuple range.  Nor can I
really imagine any database that efficiently splits a single table
across multiple machines (or even inefficiently unless some internal
partitioning is being done).

So, you'll have to do some work at your end and not just hope that
a "magic bullet" is available.

Once you've got the data partitioned, the question becomes one of
how to inhance performance/scalability.  Have you considered RAIDb?

-- 
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T15:23:17Z

Le Jeudi 20 Janvier 2005 16:14, Steve Wampler a écrit :
> Once you've got the data partitioned, the question becomes one of
> how to inhance performance/scalability.  Have you considered RAIDb?

No but I'll seems to be very interesting ... close to the explanation of 
Joshua ... but automaticly done ...

Thanks !
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Dave Cramer <pg@fastcrypt.com> — 2005-01-20T15:23:34Z

Google uses something called the google filesystem, look it up in 
google. It is a distributed file system.

Dave

Hervé Piedvache wrote:

>Joshua,
>
>Le Jeudi 20 Janvier 2005 15:44, Joshua D. Drake a écrit :
>  
>
>>Hervé Piedvache wrote:
>>    
>>
>>>My company, which I actually represent, is a fervent user of PostgreSQL.
>>>We used to make all our applications using PostgreSQL for more than 5
>>>years. We usually do classical client/server applications under Linux,
>>>and Web interface (php, perl, C/C++). We used to manage also public web
>>>services with 10/15 millions records and up to 8 millions pages view by
>>>month.
>>>      
>>>
>>Depending on your needs either:
>>
>>Slony: www.slony.info
>>
>>or
>>
>>Replicator: www.commandprompt.com
>>
>>Will both do what you want. Replicator is easier to setup but
>>Slony is free.
>>    
>>
>
>No ... as I have said ... how I'll manage a database getting a table of may be 
>250 000 000 records ? I'll need incredible servers ... to get quick access or 
>index reading ... no ?
>
>So what we would like to get is a pool of small servers able to make one 
>virtual server ... for that is called a Cluster ... no ?
>
>I know they are not using PostgreSQL ... but how a company like Google do to 
>get an incredible database in size and so quick access ?
>
>regards,
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Re: PostgreSQL clustering VS MySQL clustering

Edgars Diebelis <edgars.diebelis@divi.lv> — 2005-01-20T15:24:37Z

I have no experience with pgCluster, but I found:
PGCluster is a multi-master and synchronous replication system that
supports load balancing of PostgreSQL.
http://www.software-facilities.com/databases-software/pgcluster.php

May be some have some expierience with this tool?

----- Original Message ----- 
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Hervé Piedvache" <herve@elma.fr>
Cc: "Jeff" <threshar@torgo.978.org>; <pgsql-performance@postgresql.org>
Sent: Thursday, January 20, 2005 4:58 PM
Subject: [spam] Re: [PERFORM] PostgreSQL clustering VS MySQL clustering


>>>Or you could fork over  hundreds of thousands of dollars for Oracle's
>>>RAC.
>>
>>
>> No please do not talk about this again ... I'm looking about a PostgreSQL 
>> solution ... I know RAC ... and I'm not able to pay for a RAC certify 
>> hardware configuration plus a RAC Licence.
>
> There is absolutely zero PostgreSQL solution...
>
> You may have to split the data yourself onto two independent db servers 
> and combine the results somehow in your application.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: PostgreSQL clustering VS MySQL clustering

Hervé Piedvache <herve@elma.fr> — 2005-01-20T15:32:27Z

Le Jeudi 20 Janvier 2005 16:23, Dave Cramer a écrit :
> Google uses something called the google filesystem, look it up in
> google. It is a distributed file system.

Yes that's another point I'm working on ... make a cluster of server using 
GFS ... and making PostgreSQL running with it ...

But I have not finished my test ... and may be people could have experience 
with this ...

Regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: PostgreSQL clustering VS MySQL clustering

Steve Wampler <swampler@noao.edu> — 2005-01-20T15:40:04Z

Hervé Piedvache wrote:
> Le Jeudi 20 Janvier 2005 16:23, Dave Cramer a écrit :
> 
>>Google uses something called the google filesystem, look it up in
>>google. It is a distributed file system.
> 
> 
> Yes that's another point I'm working on ... make a cluster of server using 
> GFS ... and making PostgreSQL running with it ...

A few years ago I played around with GFS, but not for postgresql.

I don't think it's going to help - logically there's no difference
between putting PG on GFS and putting PG on NFS - in both cases
the filesystem doesn't provide any support for distributing the
task at hand - and a PG database server isn't written to be
distributed across hosts regardless of the distribution of the
data across filesystems.

-- 
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

Re: PostgreSQL clustering VS MySQL clustering

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-01-20T15:57:43Z

> Probably by carefully partitioning their data.  I can't imagine anything
> being fast on a single table in 250,000,000 tuple range.  Nor can I
> really imagine any database that efficiently splits a single table
> across multiple machines (or even inefficiently unless some internal
> partitioning is being done).

Ah, what about partial indexes - those might help.  As a kind of 
'semi-partition'.

Chris

Re: PostgreSQL clustering VS MySQL clustering

Rod Taylor <pg@rbt.ca> — 2005-01-20T16:02:58Z

On Thu, 2005-01-20 at 15:36 +0100, Hervé Piedvache wrote:
> Le Jeudi 20 Janvier 2005 15:24, Christopher Kings-Lynne a écrit :
> > > Is there any solution with PostgreSQL matching these needs ... ?
> >
> > You want: http://www.slony.info/
> >
> > > Do we have to backport our development to MySQL for this kind of problem
> > > ? Is there any other solution than a Cluster for our problem ?
> >
> > Well, Slony does replication which is basically what you want :)
> >
> > Only master->slave though, so you will need to have all inserts go via
> > the master server, but selects can come off any server.
> 
> Sorry but I don't agree with this ... Slony is a replication solution ... I 
> don't need replication ... what will I do when my database will grow up to 50 
> Gb ... I'll need more than 50 Gb of RAM on each server ???
> This solution is not very realistic for me ...

Slony has some other issues with databases > 200GB in size as well
(well, it hates long running transactions -- and pg_dump is a regular
long running transaction)

However, you don't need RAM one each server for this, you simply need
enough disk space.

Have a Master which takes writes, a "replicator" which you can consider
to be a hot-backup of the master, have N slaves replicate off of the
otherwise untouched "replicator" machine.

For your next trick, have the application send read requests for Clients
A-C to slave 1, D-F to slave 2, ...

You need enough memory to hold the index sections for clients A-C on
slave 1. The rest of the index can remain on disk. It's available should
it be required (D-F box crashed, so your application is now feeding
those read requests to the A-C machine)...

Go to more slaves and smaller segments as you require. Use the absolute
cheapest hardware you can find for the slaves that gives reasonable
performance. They don't need to be reliable, so RAID 0 on IDE drives is
perfectly acceptable.

PostgreSQL can do the replication portion quite nicely. You need to
implement the "cluster" part in the application side.
--

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-20T16:04:04Z

Christopher Kings-Lynne wrote:

>> Probably by carefully partitioning their data.  I can't imagine anything
>> being fast on a single table in 250,000,000 tuple range.  Nor can I
>> really imagine any database that efficiently splits a single table
>> across multiple machines (or even inefficiently unless some internal
>> partitioning is being done).
>
>
> Ah, what about partial indexes - those might help.  As a kind of 
> 'semi-partition'.

He could also you schemas to partition out the information within the 
same database.

J

>
> Chris



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

amrit@health2.moph.go.th — 2005-01-20T16:10:03Z

What you want is some kind of huge pararell computing , isn't it? I have heard
from many groups of Japanese Pgsql developer did it but they are talking in
japanese website and of course  in Japanese.
I can name one of them " Asushi Mitani"  and his website
http://www.csra.co.jp/~mitani/jpug/pgcluster/en/index.html
and you may directly contact him.

Amrit
Thailand

Re: PostgreSQL clustering VS MySQL clustering

Greg Stark <gsstark@mit.edu> — 2005-01-20T16:44:20Z

Steve Wampler <swampler@noao.edu> writes:

> Hervé Piedvache wrote:
> 
> > No ... as I have said ... how I'll manage a database getting a table of may
> > be 250 000 000 records ? I'll need incredible servers ... to get quick access
> > or index reading ... no ?
> 
> Probably by carefully partitioning their data.  I can't imagine anything
> being fast on a single table in 250,000,000 tuple range.  

Why are you all so psyched out by the size of the table? That's what indexes
are for.

The size of the table really isn't relevant here. The important thing is the
size of the working set. Ie, How many of those records are required to respond
to queries.

As long as you tune your application so every query can be satisfied by
reading a (very) limited number of those records and have indexes to speed
access to those records you can have quick response time even if you have
terabytes of raw data. 

I would start by looking at the plans for the queries you're running and
seeing if you have any queries that are reading more than hundred records or
so. If so then you have to optimize them or rethink your application design.
You might need to restructure your data so you don't have to scan too many
records for any query.

No clustering system is going to help you if your application requires reading
through too much data. If every query is designed to not have to read more
than a hundred or so records then there's no reason you can't have sub-100ms
response time even if you had terabytes of raw data.

If the problem is just that each individual query is fast but there's too many
coming for a single server then something like slony is all you need. It'll
spread the load over multiple machines. If you spread the load in an
intelligent way you can even concentrate each server on certain subsets of the
data. But that shouldn't even really be necessary, just a nice improvement.

-- 
greg

Re: PostgreSQL clustering VS MySQL clustering

Holger Hoffstaette <holger@wizards.de> — 2005-01-20T16:55:56Z

On Thu, 20 Jan 2005 16:32:27 +0100, Hervé Piedvache wrote:

> Le Jeudi 20 Janvier 2005 16:23, Dave Cramer a écrit :
>> Google uses something called the google filesystem, look it up in
>> google. It is a distributed file system.
> 
> Yes that's another point I'm working on ... make a cluster of server using
> GFS ... and making PostgreSQL running with it ...

Did you read the GFS whitepaper? It really works differently from other
filesystems with regard to latency and consistency. You'll probably have
better success with Lustre (http://www.clusterfs.com/) or RedHat's Global
File System (http://www.redhat.com/software/rha/gfs/).
If you're looking for a 'cheap, free and easy' solution you can just as
well stop right now. :-)

-h

Re: PostgreSQL clustering VS MySQL clustering

William Yu <wyu@talisys.com> — 2005-01-20T17:12:01Z

Hervé Piedvache wrote:
> Sorry but I don't agree with this ... Slony is a replication solution ... I 
> don't need replication ... what will I do when my database will grow up to 50 
> Gb ... I'll need more than 50 Gb of RAM on each server ???
> This solution is not very realistic for me ...

Have you confirmed you need a 1:1 RAM:data ratio? Of course more memory 
gets more speed but often at a diminishing rate of return. Unless every 
record of your 50GB is used in every query, only the most commonly used 
elements of your DB needs to be in RAM. This is the very idea of caching.

Re: PostgreSQL clustering VS MySQL clustering

Darcy Buskermolen <darcy@wavefire.com> — 2005-01-20T17:29:37Z

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?

>
> >>>>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

Re: PostgreSQL clustering VS MySQL clustering

Darcy Buskermolen <darcy@wavefire.com> — 2005-01-20T17:33:42Z

On January 20, 2005 06:51 am, Christopher Kings-Lynne wrote:
> >>>Sorry but I don't agree with this ... Slony is a replication solution
> >>> ... I don't need replication ... what will I do when my database will
> >>> grow up to 50 Gb ... I'll need more than 50 Gb of RAM on each server
> >>> ??? This solution is not very realistic for me ...
> >>>
> >>>I need a Cluster solution not a replication one or explain me in details
> >>>how I will do for managing the scalabilty of my database ...
> >>
> >>Buy Oracle
> >
> > I think this is not my solution ... sorry I'm talking about finding a
> > PostgreSQL solution ...
>
> My point being is that there is no free solution.  There simply isn't.
> I don't know why you insist on keeping all your data in RAM, but the
> mysql cluster requires that ALL data MUST fit in RAM all the time.
>
> PostgreSQL has replication, but not partitioning (which is what you want).
>
> So, your only option is Oracle or another very expensive commercial
> database.

Another Option to consider would be pgmemcache.  that way you just build the 
farm out of lots of large memory, diskless boxes for keeping the whole 
database in memory in the whole cluster.  More information on it can be found 
at: http://people.freebsd.org/~seanc/pgmemcache/

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

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com

Re: PostgreSQL clustering VS MySQL clustering

Mitch Pirtle <mitch.pirtle@gmail.com> — 2005-01-20T18:42:25Z

On Thu, 20 Jan 2005 09:33:42 -0800, Darcy Buskermolen
<darcy@wavefire.com> wrote:
> 
> Another Option to consider would be pgmemcache.  that way you just build the
> farm out of lots of large memory, diskless boxes for keeping the whole
> database in memory in the whole cluster.  More information on it can be found
> at: http://people.freebsd.org/~seanc/pgmemcache/

Which brings up another question: why not just cluster at the hardware
layer? Get an external fiberchannel array, and cluster a bunch of dual
Opterons, all sharing that storage. In that sense you would be getting
one big PostgreSQL 'image' running across all of the servers.

Or is that idea too 90's?  ;-)

-- Mitch

Re: PostgreSQL clustering VS MySQL clustering

Darcy Buskermolen <darcy@wavefire.com> — 2005-01-20T19:07:23Z

On January 20, 2005 10:42 am, Mitch Pirtle wrote:
> On Thu, 20 Jan 2005 09:33:42 -0800, Darcy Buskermolen
>
> <darcy@wavefire.com> wrote:
> > Another Option to consider would be pgmemcache.  that way you just build
> > the farm out of lots of large memory, diskless boxes for keeping the
> > whole database in memory in the whole cluster.  More information on it
> > can be found at: http://people.freebsd.org/~seanc/pgmemcache/
>
> Which brings up another question: why not just cluster at the hardware
> layer? Get an external fiberchannel array, and cluster a bunch of dual
> Opterons, all sharing that storage. In that sense you would be getting
> one big PostgreSQL 'image' running across all of the servers.

It dosn't quite work that way, thanks to shared memory, and kernel disk cache.  
(among other things)
>
> Or is that idea too 90's?  ;-)
>
> -- Mitch
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com

Re: PostgreSQL clustering VS MySQL clustering

Steve Wampler <swampler@noao.edu> — 2005-01-20T19:13:17Z

Mitch Pirtle wrote:

> Which brings up another question: why not just cluster at the hardware
> layer? Get an external fiberchannel array, and cluster a bunch of dual
> Opterons, all sharing that storage. In that sense you would be getting
> one big PostgreSQL 'image' running across all of the servers.

This isn't as easy as it sounds.  Simply sharing the array among
hosts with a 'standard' file system won't work because of cache
inconsistencies.  So, you need to put a shareable filesystem
(such as GFS or Lustre) on it.

But that's not enough, because you're going to be running separate
postgresql backends on the different hosts, and there are
definitely consistency issues with trying to do that.  So far as
I know (right, experts?) postgresql isn't designed with providing
distributed consistency in mind (isn't shared memory used for
consistency, which restricts all the backends to a single host?).

-- 
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

Re: PostgreSQL clustering VS MySQL clustering

Jean-Max Reymond <jmreymond@gmail.com> — 2005-01-20T19:35:44Z

On Thu, 20 Jan 2005 12:13:17 -0700, Steve Wampler <swampler@noao.edu> wrote:
> Mitch Pirtle wrote:

> But that's not enough, because you're going to be running separate
> postgresql backends on the different hosts, and there are
> definitely consistency issues with trying to do that.  So far as
> I know (right, experts?) postgresql isn't designed with providing
> distributed consistency in mind (isn't shared memory used for
> consistency, which restricts all the backends to a single host?).

yes, you're right: you'll need a Distributed Lock Manager and an
application to manage it , Postgres ?

Re: PostgreSQL clustering VS MySQL clustering

Bruno Almeida do Lago <teolupus@gmail.com> — 2005-01-21T00:40:02Z

I was thinking the same! I'd like to know how other databases such as Oracle
do it.

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mitch Pirtle
Sent: Thursday, January 20, 2005 4:42 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

On Thu, 20 Jan 2005 09:33:42 -0800, Darcy Buskermolen
<darcy@wavefire.com> wrote:
> 
> Another Option to consider would be pgmemcache.  that way you just build
the
> farm out of lots of large memory, diskless boxes for keeping the whole
> database in memory in the whole cluster.  More information on it can be
found
> at: http://people.freebsd.org/~seanc/pgmemcache/

Which brings up another question: why not just cluster at the hardware
layer? Get an external fiberchannel array, and cluster a bunch of dual
Opterons, all sharing that storage. In that sense you would be getting
one big PostgreSQL 'image' running across all of the servers.

Or is that idea too 90's?  ;-)

-- Mitch

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

Re: PostgreSQL clustering VS MySQL clustering

Dave Cramer <davec@fastcrypt.com> — 2005-01-21T01:04:19Z

This idea won't work with postgresql only one instance can operate on a 
datastore at a time.

Dave

Bruno Almeida do Lago wrote:

> 
>I was thinking the same! I'd like to know how other databases such as Oracle
>do it.
>
>-----Original Message-----
>From: pgsql-performance-owner@postgresql.org
>[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mitch Pirtle
>Sent: Thursday, January 20, 2005 4:42 PM
>To: pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
>
>On Thu, 20 Jan 2005 09:33:42 -0800, Darcy Buskermolen
><darcy@wavefire.com> wrote:
>  
>
>>Another Option to consider would be pgmemcache.  that way you just build
>>    
>>
>the
>  
>
>>farm out of lots of large memory, diskless boxes for keeping the whole
>>database in memory in the whole cluster.  More information on it can be
>>    
>>
>found
>  
>
>>at: http://people.freebsd.org/~seanc/pgmemcache/
>>    
>>
>
>Which brings up another question: why not just cluster at the hardware
>layer? Get an external fiberchannel array, and cluster a bunch of dual
>Opterons, all sharing that storage. In that sense you would be getting
>one big PostgreSQL 'image' running across all of the servers.
>
>Or is that idea too 90's?  ;-)
>
>-- Mitch
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>  
>

Re: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-21T01:25:41Z

Bruno,

> Which brings up another question: why not just cluster at the hardware
> layer? Get an external fiberchannel array, and cluster a bunch of dual
> Opterons, all sharing that storage. In that sense you would be getting
> one big PostgreSQL 'image' running across all of the servers.
>
> Or is that idea too 90's?  ;-)

No, it just doesn't work.   Multiple postmasters can't share one database.

LinuxLabs (as I've gathered) tried to go one better by using a tool that 
allows shared memory to bridge multple networked servers -- in other words, 
one postmaster controlling 4 or 5 servers.   The problem is that IPC via this 
method is about 1,000 times slower than IPC on a single machine, wiping out 
all of the scalability gains from having the cluster in the first place.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: PostgreSQL clustering VS MySQL clustering

decibel <decibel@decibel.org> — 2005-01-21T01:30:40Z

On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote:
>  
> I was thinking the same! I'd like to know how other databases such as Oracle
> do it.
> 
In a nutshell, in a clustered environment (which iirc in oracle means
shared disks), they use a set of files for locking and consistency
across machines. So you better have fast access to the drive array, and
the array better have caching of some kind.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: PostgreSQL clustering VS MySQL clustering

decibel <decibel@decibel.org> — 2005-01-21T01:32:04Z

On Thu, Jan 20, 2005 at 10:08:47AM -0500, Stephen Frost wrote:
> * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> > PostgreSQL has replication, but not partitioning (which is what you want).
> 
> It doesn't have multi-server partitioning..  It's got partitioning
> within a single server (doesn't it?  I thought it did, I know it was
> discussed w/ the guy from Cox Communications and I thought he was using
> it :).

No, PostgreSQL doesn't support any kind of partitioning, unless you
write it yourself. I think there's some work being done in this area,
though.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: PostgreSQL clustering VS MySQL clustering

decibel <decibel@decibel.org> — 2005-01-21T01:39:22Z

On Thu, Jan 20, 2005 at 07:12:42AM -0800, Joshua D. Drake wrote:
> 
> >>then I was thinking. Couldn't he use
> >>multiple databases
> >>over multiple servers with dblink?
> >>
> >>It is not exactly how I would want to do it, but it would provide what
> >>he needs I think???
> >>   
> >>
> >
> >Yes seems to be the only solution ... but I'm a little disapointed about 
> >this ... could you explain me why there is not this kind of 
> >functionnality ... it seems to be a real need for big applications no ?
> > 
> >
> Because it is really, really hard to do correctly and hard
> equals expensive.

To expand on what Josh said, the expense in this case is development
resources. If you look on the developer site you'll see a huge TODO list
and a relatively small list of PostgreSQL developers. To develop a
cluster solution similar to RAC would probably take the efforts of the
entire development team for a year or more, during which time very
little else would be done.

I'm glad to see your persistance in wanting to use PostgreSQL, and there
might be some kind of limited clustering scheme that could be
implemented without a great amount of effort by the core developers. In
that case I think there's a good chance you could find people willing to
work on it.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: PostgreSQL clustering VS MySQL clustering

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-21T01:40:07Z

> 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
>

Re: PostgreSQL clustering VS MySQL clustering

Iain <iain@mst.co.jp> — 2005-01-21T02:14:59Z

Oracle's RAC is good, but I think it's best to view it as a step in the high 
availability direction rather than a performance enhancer. While it can help 
your application scale up, that depends on the usage pattern. Also it's not 
100% transparent to the application for example you can't depend on a 
sequence numbers being allocated uniquely as there can be delays propagating 
them to all nodes. So in clusters where insert rates are high this means you 
should explicitly check for unique key violations and try again. Dealing 
with propagation delays comes with the clustering technology I guess. 
Nonetheless, I would love to see this kind of functionality in postgres.

Regards
Iain

----- Original Message ----- 
From: "Jim C. Nasby" <decibel@decibel.org>
To: "Bruno Almeida do Lago" <teolupus@gmail.com>
Cc: "'Mitch Pirtle'" <mitch.pirtle@gmail.com>; 
<pgsql-performance@postgresql.org>
Sent: Friday, January 21, 2005 10:30 AM
Subject: Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

> On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote:
>>
>> I was thinking the same! I'd like to know how other databases such as 
>> Oracle
>> do it.
>>
> In a nutshell, in a clustered environment (which iirc in oracle means
> shared disks), they use a set of files for locking and consistency
> across machines. So you better have fast access to the drive array, and
> the array better have caching of some kind.
> -- 
> Jim C. Nasby, Database Consultant               decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-21T03:16:15Z

>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.
>  
>
Well I know that Replicator could also use this functionality.

Sincerely,

Joshua D. Drake



>--
>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)
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-21T03:49:24Z

Tatsuo,

> 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.

Yes, please, some of us are already doing the above ad-hoc.

The simple workaround to replication lag is to calculate the longest likely 
lag (<3 seconds if Slony is tuned right) and have the dispatcher (pgpool) 
send all requests from that connection to the master for that period.   Then 
it switches back to "pool" mode where the slaves may be used.

Of course, all of the above is only useful if you're doing a web app where 96% 
of query activity is selects.   For additional scalability, put all of your 
session maintenance in memcached, so that you're not doing database writes 
every time a page loads.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: PostgreSQL clustering VS MySQL clustering

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-21T08:07:31Z

> Tatsuo,
> 
> > 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.
> 
> Yes, please, some of us are already doing the above ad-hoc.
> 
> The simple workaround to replication lag is to calculate the longest likely 
> lag (<3 seconds if Slony is tuned right) and have the dispatcher (pgpool) 
> send all requests from that connection to the master for that period.   Then 
> it switches back to "pool" mode where the slaves may be used.

Can I ask a question?

Suppose table A gets updated on the master at time 00:00. Until 00:03
pgpool needs to send all queries regarding A to the master only. My
question is, how can pgpool know a query is related to A?
--
Tatsuo Ishii

> Of course, all of the above is only useful if you're doing a web app where 96% 
> of query activity is selects.   For additional scalability, put all of your 
> session maintenance in memcached, so that you're not doing database writes 
> every time a page loads.
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

Re: PostgreSQL clustering VS MySQL clustering

Matt Clark <matt@ymogen.net> — 2005-01-21T09:16:08Z

Presumably it can't _ever_ know without being explicitly told, because 
even for a plain SELECT there might be triggers involved that update 
tables, or it might be a select of a stored proc, etc.  So in the 
general case, you can't assume that a select doesn't cause an update, 
and you can't be sure that the table list in an update is a complete 
list of the tables that might be updated.

Tatsuo Ishii wrote:

>Can I ask a question?
>
>Suppose table A gets updated on the master at time 00:00. Until 00:03
>pgpool needs to send all queries regarding A to the master only. My
>question is, how can pgpool know a query is related to A?
>--
>Tatsuo Ishii
>
>  
>

Re: PostgreSQL clustering VS MySQL clustering

Joshua D. Drake <jd@commandprompt.com> — 2005-01-21T15:40:57Z

Matt Clark wrote:

> Presumably it can't _ever_ know without being explicitly told, because 
> even for a plain SELECT there might be triggers involved that update 
> tables, or it might be a select of a stored proc, etc.  So in the 
> general case, you can't assume that a select doesn't cause an update, 
> and you can't be sure that the table list in an update is a complete 
> list of the tables that might be updated.

Uhmmm no :) There is no such thing as a select trigger. The closest you 
would get
is a function that is called via select which could be detected by 
making sure
you are prepending with a BEGIN or START Transaction. Thus yes pgPool 
can be made
to do this.

Sincerely,

Joshua D. Drake



>
>
>
> Tatsuo Ishii wrote:
>
>> Can I ask a question?
>>
>> Suppose table A gets updated on the master at time 00:00. Until 00:03
>> pgpool needs to send all queries regarding A to the master only. My
>> question is, how can pgpool know a query is related to A?
>> -- 
>> Tatsuo Ishii
>>
>>  
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PostgreSQL clustering VS MySQL clustering

Bjoern Metzdorf <bm@turtle-entertainment.de> — 2005-01-21T16:04:56Z

Joshua D. Drake wrote:
> Matt Clark wrote:
> 
>> Presumably it can't _ever_ know without being explicitly told, because 
>> even for a plain SELECT there might be triggers involved that update 
>> tables, or it might be a select of a stored proc, etc.  So in the 
>> general case, you can't assume that a select doesn't cause an update, 
>> and you can't be sure that the table list in an update is a complete 
>> list of the tables that might be updated.
> 
> 
> Uhmmm no :) There is no such thing as a select trigger. The closest you 
> would get
> is a function that is called via select which could be detected by 
> making sure
> you are prepending with a BEGIN or START Transaction. Thus yes pgPool 
> can be made
> to do this.

SELECT SETVAL() is another case.

I'd really love to see pgpool do this.

I am also curious about Slony-II development, Tom mentioned a first 
meeting about it :)

Regards,
Bjoern

Re: PostgreSQL clustering VS MySQL clustering

Matt Clark <matt@ymogen.net> — 2005-01-21T17:35:13Z

Yes, I wasn't really choosing my examples particularly carefully, but I 
think the conclusion stands: pgpool (or anyone/thing except for the 
server) cannot in general tell from the SQL it is handed by the client 
whether an update will occur, nor which tables might be affected.

That's not to say that pgpool couldn't make a good guess in the majority 
of cases!

M

Joshua D. Drake wrote:

> Matt Clark wrote:
>
>> Presumably it can't _ever_ know without being explicitly told, 
>> because even for a plain SELECT there might be triggers involved that 
>> update tables, or it might be a select of a stored proc, etc.  So in 
>> the general case, you can't assume that a select doesn't cause an 
>> update, and you can't be sure that the table list in an update is a 
>> complete list of the tables that might be updated.
>
>
> Uhmmm no :) There is no such thing as a select trigger. The closest 
> you would get
> is a function that is called via select which could be detected by 
> making sure
> you are prepending with a BEGIN or START Transaction. Thus yes pgPool 
> can be made
> to do this.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>

Re: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-21T17:47:53Z

Tatsuo,

> Suppose table A gets updated on the master at time 00:00. Until 00:03
> pgpool needs to send all queries regarding A to the master only. My
> question is, how can pgpool know a query is related to A?

Well, I'm a little late to head off tangental discussion about this, but ....

The systems where I've implemented something similar are for web applications.  
In the case of the web app, you don't care if a most users see data which is 
2 seconds out of date; with caching and whatnot, it's often much more than 
that!

The one case where it's not permissable for a user to see "old" data is the 
case where the user is updating the data.   Namely:

(1) 00:00 User A updates "My Profile"
(2) 00:01 "My Profile" UPDATE finishes executing.
(3) 00:02  User A sees "My Profile" re-displayed
(6) 00:04  "My Profile":UserA cascades to the last Slave server

So in an application like the above, it would be a real problem if User A were 
to get switched over to a slave server immediately after the update; she 
would see the old data, assume that her update was not saved, and update 
again.  Or send angry e-mails to webmaster@.   

However, it makes no difference what User B sees:

(1) 00:00 User A updates "My Profile"v1			Master
(2) 00:01 "My Profile" UPDATE finishes executing.	Master
(3) 00:02  User A sees "My Profile"v2 displayed		Master
(4) 00:02  User B requests "MyProfile":UserA		Slave2
(5) 00:03  User B sees "My Profile"v1				Slave2
(6) 00:04  "My Profile"v2 cascades to the last Slave server  Slave2

If the web application is structured properly, the fact that UserB is seeing 
UserA's information which is 2 seconds old is not a problem (though it might 
be for web auctions, where it could result in race conditions.   Consider 
memcached as a helper).   This means that pgPool only needs to monitor 
"update switching" by *connection* not by *table*.

Make sense?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: PostgreSQL clustering VS MySQL clustering

Peter Darley <pdarley@kinesis-cem.com> — 2005-01-21T22:34:40Z

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)

Re: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-22T00:34:39Z

Peter, Tatsuo:

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?

Oh, yes, that reminds me.  It would be helpful if pgPool accepted a control 
string ... perhaps one in a SQL comment ... which indicated that the 
statement to follow was, despite appearances, an update.  For example:
--STATEMENT_IS_UPDATE\n

The alternative is, of course, that pgPool direct all explicit transactions to 
the master ... which is a good idea anyway.    So you could do:

BEGIN;
SELECT some_update_function();
COMMIT;

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: PostgreSQL clustering VS MySQL clustering

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-22T03:01:28Z

> Tatsuo,
> 
> > Suppose table A gets updated on the master at time 00:00. Until 00:03
> > pgpool needs to send all queries regarding A to the master only. My
> > question is, how can pgpool know a query is related to A?
> 
> Well, I'm a little late to head off tangental discussion about this, but ....
> 
> The systems where I've implemented something similar are for web applications.  
> In the case of the web app, you don't care if a most users see data which is 
> 2 seconds out of date; with caching and whatnot, it's often much more than 
> that!
> 
> The one case where it's not permissable for a user to see "old" data is the 
> case where the user is updating the data.   Namely:
> 
> (1) 00:00 User A updates "My Profile"
> (2) 00:01 "My Profile" UPDATE finishes executing.
> (3) 00:02  User A sees "My Profile" re-displayed
> (6) 00:04  "My Profile":UserA cascades to the last Slave server
> 
> So in an application like the above, it would be a real problem if User A were 
> to get switched over to a slave server immediately after the update; she 
> would see the old data, assume that her update was not saved, and update 
> again.  Or send angry e-mails to webmaster@.   
> 
> However, it makes no difference what User B sees:
> 
> (1) 00:00 User A updates "My Profile"v1			Master
> (2) 00:01 "My Profile" UPDATE finishes executing.	Master
> (3) 00:02  User A sees "My Profile"v2 displayed		Master
> (4) 00:02  User B requests "MyProfile":UserA		Slave2
> (5) 00:03  User B sees "My Profile"v1				Slave2
> (6) 00:04  "My Profile"v2 cascades to the last Slave server  Slave2
> 
> If the web application is structured properly, the fact that UserB is seeing 
> UserA's information which is 2 seconds old is not a problem (though it might 
> be for web auctions, where it could result in race conditions.   Consider 
> memcached as a helper).   This means that pgPool only needs to monitor 
> "update switching" by *connection* not by *table*.
> 
> Make sense?

I'm not clear what "pgPool only needs to monitor "update switching" by
*connection* not by *table*" means. In your example:

> (1) 00:00 User A updates "My Profile"
> (2) 00:01 "My Profile" UPDATE finishes executing.
> (3) 00:02  User A sees "My Profile" re-displayed
> (6) 00:04  "My Profile":UserA cascades to the last Slave server

I think (2) and (3) are on different connections, thus pgpool cannot
judge if SELECT in (3) should go only to the master or not.

To solve the problem you need to make pgpool understand "web sessions"
not "database connections" and it seems impossible for pgpool to
understand "sessions".
--
Tatsuo Ishii

Re: PostgreSQL clustering VS MySQL clustering

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-22T03:39:28Z

> Peter, Tatsuo:
> 
> 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?
> 
> Oh, yes, that reminds me.  It would be helpful if pgPool accepted a control 
> string ... perhaps one in a SQL comment ... which indicated that the 
> statement to follow was, despite appearances, an update.  For example:
> --STATEMENT_IS_UPDATE\n

Actually the way judging if it's a "pure" SELECT or not in pgpool is
very simple. pgpool just checkes if the SQL statement exactly begins
with "SELECT" (case insensitive, of course). So, for example, you
could insert an SQL comment something like "/*this SELECT has side
effect*/ at the beginning of line to indicate that pgpool should not
send this query to the slave.

> The alternative is, of course, that pgPool direct all explicit transactions to 
> the master ... which is a good idea anyway.    So you could do:
> 
> BEGIN;
> SELECT some_update_function();
> COMMIT;

Yes. pgpool has already done this in load balancing. Expanding this
for Slony-I is pretty easy.
--
Tatsuo Ishii

Re: PostgreSQL clustering VS MySQL clustering

Chris Browne <cbbrowne@acm.org> — 2005-01-23T05:41:22Z

In an attempt to throw the authorities off his trail, herve@elma.fr (Hervé Piedvache) transmitted:
> Le Jeudi 20 Janvier 2005 15:24, Christopher Kings-Lynne a écrit :
>> > Is there any solution with PostgreSQL matching these needs ... ?
>>
>> You want: http://www.slony.info/
>>
>> > Do we have to backport our development to MySQL for this kind of problem
>> > ? Is there any other solution than a Cluster for our problem ?
>>
>> Well, Slony does replication which is basically what you want :)
>>
>> Only master->slave though, so you will need to have all inserts go via
>> the master server, but selects can come off any server.
>
> Sorry but I don't agree with this ... Slony is a replication
> solution ... I don't need replication ... what will I do when my
> database will grow up to 50 Gb ... I'll need more than 50 Gb of RAM
> on each server ???  This solution is not very realistic for me ...

Huh?  Why on earth do you imagine that Slony-I requires a lot of
memory?

It doesn't.  A fairly _large_ Slony-I process is about 10MB.  There
will be some demand for memory on the DB servers, but you don't need
an enormous quantity of extra memory to run it.

There is a MySQL "replicating/clustering" system that uses an
in-memory database which means that if your DB is 50GB in size, you
need something like 200GB of RAM.  If you're thinking of that, that's
not relevant to PostgreSQL or Slony-I...

> I need a Cluster solution not a replication one or explain me in
> details how I will do for managing the scalabilty of my database ...

I'm not sure you understand clustering if you imagine it doesn't
involve replication.

There are numerous models for clustering, much as there are numerous
RAID models.

But the only sorts of clustering cases where you get to NOT do
replication are the cases where all you're looking for from clustering
is improved speed, and you're willing for any breakage on any host to
potentially destroy your cluster.

Perhaps you need to describe what you _think_ you mean by a "cluster
solution."  It may be that it'll take further thought to determine
what you actually need...
-- 
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/postgresql.html
"Not  me, guy. I  read the  Bash man  page each  day like  a Jehovah's
Witness reads  the Bible.  No  wait, the Bash  man page IS  the bible.
Excuse    me..."    (More   on    confusing   aliases,    taken   from
comp.os.linux.misc)

Re: PostgreSQL clustering VS MySQL clustering

Chris Browne <cbbrowne@acm.org> — 2005-01-23T05:46:51Z

In the last exciting episode, herve@elma.fr (Hervé Piedvache) wrote:
> Le Jeudi 20 Janvier 2005 16:05, Joshua D. Drake a écrit :
>> Christopher Kings-Lynne wrote:
>> >>> Or you could fork over  hundreds of thousands of dollars for Oracle's
>> >>> RAC.
>> >>
>> >> No please do not talk about this again ... I'm looking about a
>> >> PostgreSQL solution ... I know RAC ... and I'm not able to pay for a
>> >> RAC certify hardware configuration plus a RAC Licence.
>> >
>> > There is absolutely zero PostgreSQL solution...
>>
>> I just replied the same thing but then I was thinking. Couldn't he use
>> multiple databases
>> over multiple servers with dblink?
>>
>> It is not exactly how I would want to do it, but it would provide what
>> he needs I think???
>
> Yes seems to be the only solution ... but I'm a little disapointed about 
> this ... could you explain me why there is not this kind of 
> functionnality ... it seems to be a real need for big applications no ?

If this is what you actually need, well, it's something that lots of
people would sort of like to have, but it's really DIFFICULT to
implement it.

Partitioning data onto different servers appears like it ought to be a
good idea.  Unfortunately, getting _exactly_ the right semantics is
hard.  

If the data is all truly independent, then it's no big deal; just have
one server for one set of data, and another for the other.

But reality normally is that if you _think_ you need a cluster, that's
because some of the data needs to be _shared_, which means you need to
either:

 a) Have queries that run across two databases, or

 b) Replicate the shared data between the systems.

We're likely back to the need for replication.
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/rdbms.html
"It is the user who should parameterize procedures, not their
creators." -- Alan Perlis

Re: PostgreSQL clustering VS MySQL clustering

Chris Browne <cbbrowne@acm.org> — 2005-01-23T05:58:28Z

After a long battle with technology, herve@elma.fr (Hervé Piedvache), an earthling, wrote:
> Joshua,
>
> Le Jeudi 20 Janvier 2005 15:44, Joshua D. Drake a écrit :
>> Hervé Piedvache wrote:
>> >
>> >My company, which I actually represent, is a fervent user of PostgreSQL.
>> >We used to make all our applications using PostgreSQL for more than 5
>> > years. We usually do classical client/server applications under Linux,
>> > and Web interface (php, perl, C/C++). We used to manage also public web
>> > services with 10/15 millions records and up to 8 millions pages view by
>> > month.
>>
>> Depending on your needs either:
>>
>> Slony: www.slony.info
>>
>> or
>>
>> Replicator: www.commandprompt.com
>>
>> Will both do what you want. Replicator is easier to setup but
>> Slony is free.
>
> No ... as I have said ... how I'll manage a database getting a table
> of may be 250 000 000 records ? I'll need incredible servers ... to
> get quick access or index reading ... no ?
>
> So what we would like to get is a pool of small servers able to make
> one virtual server ... for that is called a Cluster ... no ?

The term "cluster" simply indicates the use of multiple servers.

There are numerous _DIFFERENT_ forms of "clusters," so that for
someone to say "I want a cluster" commonly implies that since they
didn't realize the need to specify things further, they really don't
know what they want in a usefully identifiable way.

> I know they are not using PostgreSQL ... but how a company like
> Google do to get an incredible database in size and so quick access
> ?

Google has built a specialized application that evidently falls into
the category known as "embarrassingly parallel."
<http://c2.com/cgi/wiki?EmbarrassinglyParallel>

There are classes of applications that are amenable to
parallelization.

Those tend to be applications completely different from those
implemented atop transactional data stores like PostgreSQL.

If your problem is "embarrassingly parallel," then I'd bet lunch that
PostgreSQL (and all other SQL databases) are exactly the _wrong_ tool
for implementing its data store.

If your problem is _not_ "embarrassingly parallel," then you'll almost
certainly discover that the cheapest way to make it fast involves
fitting all the data onto _one_ computer so that you do not have to
pay the costs of transmitting data over slow inter-computer
communications links.
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/
It isn't that  physicists enjoy physics  more than they enjoy sex, its
that they enjoy sex more when they are thinking of physics.

Re: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-23T22:42:52Z

Tatsuo,

> I'm not clear what "pgPool only needs to monitor "update switching" by
>
> *connection* not by *table*" means. In your example:
> > (1) 00:00 User A updates "My Profile"
> > (2) 00:01 "My Profile" UPDATE finishes executing.
> > (3) 00:02  User A sees "My Profile" re-displayed
> > (6) 00:04  "My Profile":UserA cascades to the last Slave server
>
> I think (2) and (3) are on different connections, thus pgpool cannot
> judge if SELECT in (3) should go only to the master or not.
>
> To solve the problem you need to make pgpool understand "web sessions"
> not "database connections" and it seems impossible for pgpool to
> understand "sessions".

Depends on your connection pooling software, I suppose.  Most connection 
pooling software only returns connections to the pool after a user has been 
inactive for some period ... generally more than 3 seconds.  So connection 
continuity could be trusted.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: PostgreSQL clustering VS MySQL clustering

Hannu Krosing <hannu@tm.ee> — 2005-01-23T23:28:29Z

Ühel kenal päeval (neljapäev, 20. jaanuar 2005, 11:02-0500), kirjutas
Rod Taylor:


> Slony has some other issues with databases > 200GB in size as well
> (well, it hates long running transactions -- and pg_dump is a regular
> long running transaction)

IIRC it hates pg_dump mainly on master. If you are able to run pg_dump
from slave, it should be ok.

-- 
Hannu Krosing <hannu@tm.ee>

Re: PostgreSQL clustering VS MySQL clustering

Hannu Krosing <hannu@tm.ee> — 2005-01-23T23:44:39Z

Ühel kenal päeval (neljapäev, 20. jaanuar 2005, 16:00+0100), kirjutas
Hervé Piedvache:

> > Will both do what you want. Replicator is easier to setup but
> > Slony is free.
> 
> No ... as I have said ... how I'll manage a database getting a table of may be 
> 250 000 000 records ? I'll need incredible servers ... to get quick access or 
> index reading ... no ?
> 
> So what we would like to get is a pool of small servers able to make one 
> virtual server ... for that is called a Cluster ... no ?
> 
> I know they are not using PostgreSQL ... but how a company like Google do to 
> get an incredible database in size and so quick access ?

They use lots of boxes and lots custom software to implement a very
specific kind of cluster.

> regards,
-- 
Hannu Krosing <hannu@tm.ee>

Re: PostgreSQL clustering VS MySQL clustering

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-24T01:30:33Z

> Tatsuo,
> 
> > I'm not clear what "pgPool only needs to monitor "update switching" by
> >
> > *connection* not by *table*" means. In your example:
> > > (1) 00:00 User A updates "My Profile"
> > > (2) 00:01 "My Profile" UPDATE finishes executing.
> > > (3) 00:02  User A sees "My Profile" re-displayed
> > > (6) 00:04  "My Profile":UserA cascades to the last Slave server
> >
> > I think (2) and (3) are on different connections, thus pgpool cannot
> > judge if SELECT in (3) should go only to the master or not.
> >
> > To solve the problem you need to make pgpool understand "web sessions"
> > not "database connections" and it seems impossible for pgpool to
> > understand "sessions".
> 
> Depends on your connection pooling software, I suppose.  Most connection 
> pooling software only returns connections to the pool after a user has been 
> inactive for some period ... generally more than 3 seconds.  So connection 
> continuity could be trusted.

Not sure what you mean by "most connection pooling software", but I'm
sure that pgpool behaves differently.
--
Tatsuo Ishii

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering

Josh Berkus <josh@agliodbs.com> — 2005-01-24T17:52:40Z

Tatsuo,

> > Depends on your connection pooling software, I suppose.  Most connection
> > pooling software only returns connections to the pool after a user has
> > been inactive for some period ... generally more than 3 seconds.  So
> > connection continuity could be trusted.
>
> Not sure what you mean by "most connection pooling software", but I'm
> sure that pgpool behaves differently.

Ah, clarity problem here.    I'm talking about connection pooling tools from 
the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
Jakarta's connection pools, etc.   Not pooling on the database server side, 
which is what pgPool provides.

Most of these tools allocate a database connection to an HTTP/middleware 
client, and only release it after a specific period of inactivity.    This 
means that you *could* count on "web-user==connection" for purposes of 
switching back and forth to the master -- as long as the connection-recycling 
timeout were set higher than the pgPool switch-off period.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Ragnar Hafstað <gnari@simnet.is> — 2005-01-24T18:24:05Z

On Mon, 2005-01-24 at 09:52 -0800, Josh Berkus wrote:
> [about keeping connections open in web context]
> Ah, clarity problem here.    I'm talking about connection pooling tools from 
> the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
> Jakarta's connection pools, etc.   Not pooling on the database server side, 
> which is what pgPool provides.

note that these sometimes do not provide connection pooling as such,
just persistent connections (Apache::DBI)

> Most of these tools allocate a database connection to an HTTP/middleware 
> client, and only release it after a specific period of inactivity.    This 
> means that you *could* count on "web-user==connection" for purposes of 
> switching back and forth to the master -- as long as the connection-recycling 
> timeout were set higher than the pgPool switch-off period.

no. you can only count on web-server-process==connection, but not
web-user==connection, unless you can garantee that the same user
client always connects to same web-server process.

am i missing something ?

gnari

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Josh Berkus <josh@agliodbs.com> — 2005-01-24T23:45:51Z

Ragnar,

> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Yes, right.

> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

Are there ones that you use which might use several different connections to 
send a series of queries from a single web-user, less than 5 seconds apart?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-25T00:21:09Z

> On Mon, 2005-01-24 at 09:52 -0800, Josh Berkus wrote:
> > [about keeping connections open in web context]
> > Ah, clarity problem here.    I'm talking about connection pooling tools from 
> > the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
> > Jakarta's connection pools, etc.   Not pooling on the database server side, 
> > which is what pgPool provides.
> 
> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Right. Same thing can be said to pg_pconnect.

> > Most of these tools allocate a database connection to an HTTP/middleware 
> > client, and only release it after a specific period of inactivity.    This 
> > means that you *could* count on "web-user==connection" for purposes of 
> > switching back and forth to the master -- as long as the connection-recycling 
> > timeout were set higher than the pgPool switch-off period.
> 
> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

I have same opinion.

> am i missing something ?
--
Tatsuo Ishii

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Ragnar Hafstað <gnari@simnet.is> — 2005-01-25T09:52:06Z

On Mon, 2005-01-24 at 15:45 -0800, Josh Berkus wrote:

> [about keeping open DB connections between web-client connections]

> [I wrote:]
> > no. you can only count on web-server-process==connection, but not
> > web-user==connection, unless you can garantee that the same user
> > client always connects to same web-server process.
> 
> Are there ones that you use which might use several different connections to 
> send a series of queries from a single web-user, less than 5 seconds apart?

actually, it had never occurred to me to test all browsers in this
reguard, but i can think of LWP::UserAgent.

gnari

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Peter Darley <pdarley@kinesis-cem.com> — 2005-01-25T16:49:58Z

Josh,

	Please excuse how my client quotes things...

> Are there ones that you use which might use several different connections to 
> send a series of queries from a single web-user, less than 5 seconds apart?

	Using Apache/Perl I often have a situation where we're sending several queries from the same user (web client) within seconds, or even simultaneously, that use different connections.

	When someone logs in to our system they get a frameset that has 5 windows, each of which is filled with data from queries.  Since the pages in the frames are requested separately by the client the system doesn't insure that they go to the same process, and subsequently, that they're not served by the same db connection.

	Session information is stored in the database (so it's easily persistent across server processes), so it would be bad if a request for a page was served by a db server that didn't yet have information about the user (such as that they're logged in, etc.).

	If we ever have enough traffic to warrant it, we're going to go to a load balancer that passes requests to different identical web servers, at which point we won't even be getting requests from the same machine, much less the same connection.

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Monday, January 24, 2005 3:46 PM
To: Ragnar Hafstað
Cc: pgsql-performance@postgresql.org; Tatsuo Ishii
Subject: Re: [PERFORM] PgPool changes WAS: PostgreSQL clustering VS
MySQL


Ragnar,

> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Yes, right.

> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

Are there ones that you use which might use several different connections to 
send a series of queries from a single web-user, less than 5 seconds apart?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Josh Berkus <josh@agliodbs.com> — 2005-01-25T16:58:37Z

Peter, Ragnar,

> > Are there ones that you use which might use several different connections
> > to send a series of queries from a single web-user, less than 5 seconds
> > apart?
>
> 	Using Apache/Perl I often have a situation where we're sending several
> queries from the same user (web client) within seconds, or even
> simultaneously, that use different connections.

So from the sound of it, the connection methods I've been using are the 
exception rather than the rule.   Darn, it worked well for us.  :-(

What this would point to is NOT being able to use Slony-I for database server 
pooling for most web applications.   Yes?  Users should look to pgCluster and 
C-JDBC instead.

BTW, Tatsuo, what's the code relationship between pgPool and pgCluster, if 
any?

--Josh

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: PgPool changes WAS: PostgreSQL clustering VS MySQL

Tatsuo Ishii <t-ishii@sra.co.jp> — 2005-01-26T01:09:04Z

> Peter, Ragnar,
> 
> > > Are there ones that you use which might use several different connections
> > > to send a series of queries from a single web-user, less than 5 seconds
> > > apart?
> >
> > 	Using Apache/Perl I often have a situation where we're sending several
> > queries from the same user (web client) within seconds, or even
> > simultaneously, that use different connections.
> 
> So from the sound of it, the connection methods I've been using are the 
> exception rather than the rule.   Darn, it worked well for us.  :-(
> 
> What this would point to is NOT being able to use Slony-I for database server 
> pooling for most web applications.   Yes?  Users should look to pgCluster and 
> C-JDBC instead.

Yup. That's the limitaion of async replication solutions.

> BTW, Tatsuo, what's the code relationship between pgPool and pgCluster, if 
> any?

PGCluster consists of three kind of servers, "load balance server",
"cluster server"(modified PostgreSQL backend) and "replication
server". I believe some of codes of pgpool are used in the load
balance server to avoid "re-invent a wheel". This is a beauty of open
source software project.
--
Tatsuo Ishii

Re: PostgreSQL clustering VS MySQL clustering

Andrew Sullivan <ajs@crankycanuck.ca> — 2005-01-28T15:29:58Z

On Thu, Jan 20, 2005 at 04:02:39PM +0100, Hervé Piedvache wrote:
> 
> I don't insist about have data in RAM .... but when you use PostgreSQL with 
> big database you know that for quick access just for reading the index file 
> for example it's better to have many RAM as possible ... I just want to be 
> able to get a quick access with a growing and growind database ...

Well, in any case, you need much better hardware than you're looking
at.  I mean, dual Xeon with 2 Gig isn't hardly big iron.  Why don't
you try benchmarking on a honking big box -- IBM P690 or a big Sun
(I'd counsel against that, though) or something like that?  Or even
some Opterons.  Dual Xeon is probablt your very worst choice at the
moment.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.
		--Bruce Schneier

Re: PostgreSQL clustering VS MySQL clustering

Andrew Sullivan <ajs@crankycanuck.ca> — 2005-01-28T15:31:38Z

On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote:
>  
> I was thinking the same! I'd like to know how other databases such as Oracle
> do it.

You mean "how Oracle does it".  They're the only ones in the market
that really have this technology.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie

Re: PostgreSQL clustering VS MySQL clustering

Andrew Sullivan <ajs@crankycanuck.ca> — 2005-01-28T15:33:03Z

On Thu, Jan 20, 2005 at 03:54:23PM +0100, Hervé Piedvache wrote:
> Slony do not use RAM ... but PostgreSQL will need RAM for accessing a database 
> of 50 Gb ... so having two servers with the same configuration replicated by 
> slony do not slove the problem of the scalability of the database ...

You could use SSD for your storage.  That'd make it go rather quickly
even if it had to seek on disk.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
		--Roger Brinner

Re: PostgreSQL clustering VS MySQL clustering

Andrew Sullivan <ajs@crankycanuck.ca> — 2005-01-28T15:34:25Z

On Thu, Jan 20, 2005 at 04:07:51PM +0100, Hervé Piedvache wrote:
> Yes seems to be the only solution ... but I'm a little disapointed about 
> this ... could you explain me why there is not this kind of 
> functionnality ... it seems to be a real need for big applications no ?

I hate to be snarky, but the reason there isn't this kind of system
just hanging around is that it's a Very Hard Problem.  I spent 2 days
last week in a room with some of the smartest people I know, and
there was widespread agreement that what you want is a very tough
problem.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
                --Brad Holland

Re: PostgreSQL clustering VS MySQL clustering

Andrew Sullivan <ajs@crankycanuck.ca> — 2005-01-28T15:36:20Z

On Mon, Jan 24, 2005 at 01:28:29AM +0200, Hannu Krosing wrote:
> 
> IIRC it hates pg_dump mainly on master. If you are able to run pg_dump
> from slave, it should be ok.

For the sake of the archives, that's not really a good idea.  There
is some work afoot to solve it, but at the moment dumping from a
slave gives you a useless database dump.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun

Re: PostgreSQL clustering VS MySQL clustering

Alex Turner <armtuk@gmail.com> — 2005-01-28T15:59:58Z

At this point I will interject a couple of benchmark numbers based on
a new system we just configured as food for thought.

System A (old system):
Compaq Proliant Dual Pentium III 933 with Smart Array 5300, one RAID
1, one 3 Disk RAID 5 on 10k RPM drives, 2GB PC133 RAM.  Original
Price: $6500

System B (new system):
Self Built Dual Opteron 242 with 2x3ware 9500S-8MI SATA, one RAID 1
(OS), one 4 drive RAID 10 (pg_xlog), one 6 drive RAID 10 (data) on 10k
RPM Raptors, 4GB PC3200 RAM.  Current price $7200

System A for our large insert job: 125 minutes
System B for our large insert job: 10 minutes.

There is no logical way there should be a 12x performance difference
between these two systems,  maybe 2x or even 4x, but not 12x

Bad controler cards/configuration will seriously ruin your day.  3ware
escalade cards are very well supported on linux, and work excellently.
 Compaq smart array cards are not.  Bonnie++ benchmarks show a 9MB/sec
write, 29MB/sec read on the RAID 5, but a 172MB/sec write on the
6xRAID 10, and 66MB/sec write on the RAID 1 on the 3ware.

With the right configuration you can get very serious throughput.  The
new system is processing over 2500 insert transactions per second.  We
don't need more RAM with this config.  The disks are fast enough. 
2500 transaction/second is pretty damn fast.

Alex Turner

On Fri, 28 Jan 2005 10:31:38 -0500, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote:
> >
> > I was thinking the same! I'd like to know how other databases such as Oracle
> > do it.
> 
> You mean "how Oracle does it".  They're the only ones in the market
> that really have this technology.
> 
> A
> 
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
> This work was visionary and imaginative, and goes to show that visionary
> and imaginative work need not end up well.
>                 --Dennis Ritchie
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: PostgreSQL clustering VS MySQL clustering

Frank Wiles <frank@wiles.org> — 2005-01-28T16:17:24Z

On Fri, 28 Jan 2005 10:59:58 -0500
Alex Turner <armtuk@gmail.com> wrote:

> At this point I will interject a couple of benchmark numbers based on
> a new system we just configured as food for thought.
> 
> System A (old system):
> Compaq Proliant Dual Pentium III 933 with Smart Array 5300, one RAID
> 1, one 3 Disk RAID 5 on 10k RPM drives, 2GB PC133 RAM.  Original
> Price: $6500
> 
> System B (new system):
> Self Built Dual Opteron 242 with 2x3ware 9500S-8MI SATA, one RAID 1
> (OS), one 4 drive RAID 10 (pg_xlog), one 6 drive RAID 10 (data) on 10k
> RPM Raptors, 4GB PC3200 RAM.  Current price $7200
> 
> System A for our large insert job: 125 minutes
> System B for our large insert job: 10 minutes.
> 
> There is no logical way there should be a 12x performance difference
> between these two systems,  maybe 2x or even 4x, but not 12x
> 
> Bad controler cards/configuration will seriously ruin your day.  3ware
> escalade cards are very well supported on linux, and work excellently.
>  Compaq smart array cards are not.  Bonnie++ benchmarks show a 9MB/sec
> write, 29MB/sec read on the RAID 5, but a 172MB/sec write on the
> 6xRAID 10, and 66MB/sec write on the RAID 1 on the 3ware.
> 
> With the right configuration you can get very serious throughput.  The
> new system is processing over 2500 insert transactions per second.  We
> don't need more RAM with this config.  The disks are fast enough. 
> 2500 transaction/second is pretty damn fast.

  I agree that badly supported or configured cards can ruin your
  performance. 

  However, don't you think moving pg_xlog onto a separate RAID and
  increasing your number of spindles from 3 to 6 on the data RAID would
  also have a significant impact on performance, no matter what card was
  used? 

  I'm not sure you can give all the credit to the card on this one. 

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------

Re: PostgreSQL clustering VS MySQL clustering

Christopher Weimann <cweimann@k12hq.com> — 2005-01-28T16:54:57Z

On 01/28/2005-10:59AM, Alex Turner wrote:
> At this point I will interject a couple of benchmark numbers based on
> a new system we just configured as food for thought.
> 
> System A (old system):
> Compaq Proliant Dual Pentium III 933 with Smart Array 5300, one RAID
> 1, one 3 Disk RAID 5 on 10k RPM drives, 2GB PC133 RAM.  Original
> Price: $6500
> 
> System B (new system):
> Self Built Dual Opteron 242 with 2x3ware 9500S-8MI SATA, one RAID 1
> (OS), one 4 drive RAID 10 (pg_xlog), one 6 drive RAID 10 (data) on 10k
> RPM Raptors, 4GB PC3200 RAM.  Current price $7200
> 
> System A for our large insert job: 125 minutes
> System B for our large insert job: 10 minutes.
> 
> There is no logical way there should be a 12x performance difference
> between these two systems,  maybe 2x or even 4x, but not 12x
> 

Your system A has the absolute worst case Raid 5, 3 drives. The more
drives you add to Raid 5 the better it gets but it will never beat Raid
10. On top of it being the worst case, pg_xlog is not on a separate
spindle.

Your system B has a MUCH better config. Raid 10 is faster than Raid 5 to
begin with but on top of that you have more drives involved plus pg_xlog
is on a separate spindle.

I'd say I am not surprised by your performance difference.

> Bad controler cards/configuration will seriously ruin your day.  3ware
> escalade cards are very well supported on linux, and work excellently.
>  Compaq smart array cards are not.  Bonnie++ benchmarks show a 9MB/sec
> write, 29MB/sec read on the RAID 5, but a 172MB/sec write on the
> 6xRAID 10, and 66MB/sec write on the RAID 1 on the 3ware.
> 

What does bonnie say about the Raid 1 on the Compaq? Comparing the two
Raid 1s is really the only valid comparison that can be made between
these two machines.  Other than that you are comparing apples to 
snow shovels.

Re: PostgreSQL clustering VS MySQL clustering

Christopher Browne <cbbrowne@ca.afilias.info> — 2005-01-28T19:49:29Z

ajs@crankycanuck.ca (Andrew Sullivan) writes:
> On Mon, Jan 24, 2005 at 01:28:29AM +0200, Hannu Krosing wrote:
>> 
>> IIRC it hates pg_dump mainly on master. If you are able to run pg_dump
>> from slave, it should be ok.
>
> For the sake of the archives, that's not really a good idea.  There
> is some work afoot to solve it, but at the moment dumping from a
> slave gives you a useless database dump.

That overstates things a tad; I think it's worth elaborating on a bit.

There's a problem with the results of dumping the _schema_ from a
Slony-I 'subscriber' node; you want to get the schema from the origin
node.  The problem has to do with triggers; Slony-I suppresses RI
triggers and such like on subscriber nodes in a fashion that leaves
the dumped schema a bit broken with regard to triggers.

But there's nothing wrong with the idea of using "pg_dump --data-only"
against a subscriber node to get you the data without putting a load
on the origin.  And then pulling the schema from the origin, which
oughtn't be terribly expensive there.
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Re: PostgreSQL clustering VS MySQL clustering

William Yu <wyu@talisys.com> — 2005-01-28T22:04:24Z

Hervé Piedvache wrote:
>>My point being is that there is no free solution.  There simply isn't.
>>I don't know why you insist on keeping all your data in RAM, but the
>>mysql cluster requires that ALL data MUST fit in RAM all the time.
> 
> 
> I don't insist about have data in RAM .... but when you use PostgreSQL with 
> big database you know that for quick access just for reading the index file 
> for example it's better to have many RAM as possible ... I just want to be 
> able to get a quick access with a growing and growind database ...

If it's an issue of RAM and not CPU power, think about this scenario. 
Let's just say you *COULD* partition your DB over multiple servers. What 
  are your plans then? Are you going to buy 4 Dual Xeon servers? Ok, 
let's price that out.

For a full-blown rackmount server w/ RAID, 6+ SCSI drives and so on, you 
are looking at roughly $4000 per machine. So now you have 4 machines -- 
total of 16GB of RAM over the 4 machines.

On the otherhand, let's say you spent that money on a Quad Opteron 
instead. 4x850 will cost you roughly $8000. 16GB of RAM using 1GB DIMMs 
is $3000. If you went with 2GB DIMMs, you could stuff 32GB of RAM onto 
that machine for $7500.

Let's review the math:

4X server cluster, total 16GB RAM = $16K
1 beefy server w/ 16GB RAM = $11K
1 beefy server w/ 32GB RAM = $16K

I know what I would choose. I'd get the mega server w/ a ton of RAM and 
skip all the trickyness of partitioning a DB over multiple servers. Yes 
your data will grow to a point where even the XXGB can't cache 
everything. On the otherhand, memory prices drop just as fast. By that 
time, you can ebay your original 16/32GB and get 64/128GB.

Re: PostgreSQL clustering VS MySQL clustering

Alex Turner <armtuk@gmail.com> — 2005-01-28T22:57:11Z

On Fri, 28 Jan 2005 11:54:57 -0500, Christopher Weimann
<cweimann@k12hq.com> wrote:
> On 01/28/2005-10:59AM, Alex Turner wrote:
> > At this point I will interject a couple of benchmark numbers based on
> > a new system we just configured as food for thought.
> >
> > System A (old system):
> > Compaq Proliant Dual Pentium III 933 with Smart Array 5300, one RAID
> > 1, one 3 Disk RAID 5 on 10k RPM drives, 2GB PC133 RAM.  Original
> > Price: $6500
> >
> > System B (new system):
> > Self Built Dual Opteron 242 with 2x3ware 9500S-8MI SATA, one RAID 1
> > (OS), one 4 drive RAID 10 (pg_xlog), one 6 drive RAID 10 (data) on 10k
> > RPM Raptors, 4GB PC3200 RAM.  Current price $7200
> >
> > System A for our large insert job: 125 minutes
> > System B for our large insert job: 10 minutes.
> >
> > There is no logical way there should be a 12x performance difference
> > between these two systems,  maybe 2x or even 4x, but not 12x
> >
> 
> Your system A has the absolute worst case Raid 5, 3 drives. The more
> drives you add to Raid 5 the better it gets but it will never beat Raid
> 10. On top of it being the worst case, pg_xlog is not on a separate
> spindle.
> 

True for writes, but not for reads.

> Your system B has a MUCH better config. Raid 10 is faster than Raid 5 to
> begin with but on top of that you have more drives involved plus pg_xlog
> is on a separate spindle.

I absolutely agree, it is a much better config, thats why we bought it
;)..  In system A, the xlog was actualy on the RAID 1, so it was
infact on a seperate spindle set.

> 
> I'd say I am not surprised by your performance difference.
> 

I'm not surprised at all that the new system outperformed the old,
it's more the factor of improvement.  12x is a _VERY_ big performance
jump.

> > Bad controler cards/configuration will seriously ruin your day.  3ware
> > escalade cards are very well supported on linux, and work excellently.
> >  Compaq smart array cards are not.  Bonnie++ benchmarks show a 9MB/sec
> > write, 29MB/sec read on the RAID 5, but a 172MB/sec write on the
> > 6xRAID 10, and 66MB/sec write on the RAID 1 on the 3ware.
> >
> 
> What does bonnie say about the Raid 1 on the Compaq? Comparing the two
> Raid 1s is really the only valid comparison that can be made between
> these two machines.  Other than that you are comparing apples to
> snow shovels.
> 
> 

My main point is that you can spend $7k on a server and believe you
have a fast system.  The person who bought the original system was
under the delusion that it would make a good DB server.  For the same
$7k a different configuration can yield a vastly different performance
output.  This means that it's not quite apples to snow shovels. 
People who _believe_ they have an adequate config are often sorely
mistaken, and ask misguided questions about needed 20GB of RAM because
the system can't page to disk fast enough, when what they really need
is a good RAID 10 with a high quality controler.  A six drive RAID 10
is going to run a bit less than 20G of SSD.

Alex Turner
NetEconomist

Re: PostgreSQL clustering VS MySQL clustering

Christopher Weimann <cweimann@k12hq.com> — 2005-01-29T00:48:37Z

On 01/28/2005-05:57PM, Alex Turner wrote:
> > 
> > Your system A has the absolute worst case Raid 5, 3 drives. The more
> > drives you add to Raid 5 the better it gets but it will never beat Raid
> > 10. On top of it being the worst case, pg_xlog is not on a separate
> > spindle.
> > 
> 
> True for writes, but not for reads.
> 

Good point.

> 
> My main point is that you can spend $7k on a server and believe you
> have a fast system.  The person who bought the original system was
> under the delusion that it would make a good DB server.  For the same
> $7k a different configuration can yield a vastly different performance
> output.  This means that it's not quite apples to snow shovels. 

That point is definatly made.  I primarily wanted to point out that the
controlers involved were not the only difference. 

In my experience with SQL servers of various flavors fast disks and 
getting things onto a separate spindles is more important than just
about anything else.  Depending on the size of your 'hot' dataset
RAM could be more important and CPU never is.

Re: PostgreSQL clustering VS MySQL clustering

Greg Stark <gsstark@mit.edu> — 2005-01-29T07:30:14Z

William Yu <wyu@talisys.com> writes:

> 1 beefy server w/ 32GB RAM = $16K
> 
> I know what I would choose. I'd get the mega server w/ a ton of RAM and skip
> all the trickyness of partitioning a DB over multiple servers. Yes your data
> will grow to a point where even the XXGB can't cache everything. On the
> otherhand, memory prices drop just as fast. By that time, you can ebay your
> original 16/32GB and get 64/128GB.

a) What do you do when your calculations show you need 256G of ram? [Yes such
machines exist but you're not longer in the realm of simply "add more RAM".
Administering such machines is nigh as complex as clustering]

b) What do you do when you find you need multiple machines anyways to divide
the CPU or I/O or network load up. Now you need n big beefy servers when n
servers 1/nth as large would really have sufficed. This is a big difference
when you're talking about the difference between colocating 16 1U boxen with
4G of ram vs 16 4U opterons with 64G of RAM...

All that said, yes, speaking as a user I think the path of least resistance is
to build n complete slaves using Slony and then just divide the workload.
That's how I'm picturing going when I get to that point.

Even if I just divide the workload randomly it's easier than building a
machine with n times the cpu and i/o. And if I divide the workload up in a way
that correlates with data in the database I can probably get close to the same
performance as clustering. The actual cost of replicating the unused data is
slight. And the simplicity of master-slave makes it much more appealing than
full on clustering.

-- 
greg

Re: PostgreSQL clustering VS MySQL clustering

William Yu <wyu@talisys.com> — 2005-01-29T08:22:14Z

>>I know what I would choose. I'd get the mega server w/ a ton of RAM and skip
>>all the trickyness of partitioning a DB over multiple servers. Yes your data
>>will grow to a point where even the XXGB can't cache everything. On the
>>otherhand, memory prices drop just as fast. By that time, you can ebay your
>>original 16/32GB and get 64/128GB.
> 
> 
> a) What do you do when your calculations show you need 256G of ram? [Yes such
> machines exist but you're not longer in the realm of simply "add more RAM".
> Administering such machines is nigh as complex as clustering]

If you need that much memory, you've got enough customers paying you 
cash to pay for anything. :) Technology always increase -- 8X Opterons 
would double your memory capacity, higher capacity DIMMs, etc.

> b) What do you do when you find you need multiple machines anyways to divide
> the CPU or I/O or network load up. Now you need n big beefy servers when n
> servers 1/nth as large would really have sufficed. This is a big difference
> when you're talking about the difference between colocating 16 1U boxen with
> 4G of ram vs 16 4U opterons with 64G of RAM...
> 
> All that said, yes, speaking as a user I think the path of least resistance is
> to build n complete slaves using Slony and then just divide the workload.
> That's how I'm picturing going when I get to that point.

Replication is good for uptime and high read systems. The problem is 
that if your system has a high volume of writes and you need near 
realtime data syncing, clusters don't get you anything. A write on one 
server means a write on every server. Spreading out the damage over 
multiple machines doesn't help a bit.

Plus the fact that we don't have multi-master replication yet is quite a 
bugaboo. That requires writing quite extensive code if you can't afford 
to have 1 server be your single point of failure. We wrote our own 
multi-master replication code at the client app level and it's quite a 
chore making sure the replication act logically. Every table needs to 
have separate logic to parse situations like "voucher was posted on 
server 1 but voided after on server 2, what's the correct action here?" 
So I've got a slew of complicated if-then-else statements that not only 
have to take into account type of update being made but the sequence.

And yes, I tried doing realtime locks over a VPN link over our servers 
in SF and VA. Ugh...latency was absolutely horrible and made 
transactions run 1000X slower.

Re: PostgreSQL clustering VS MySQL clustering

Jan Wieck <janwieck@yahoo.com> — 2005-02-06T16:42:41Z

On 1/20/2005 9:23 AM, Jean-Max Reymond wrote:

> On Thu, 20 Jan 2005 15:03:31 +0100, Hervé Piedvache <herve@elma.fr> wrote:
> 
>> We were at this moment thinking about a Cluster solution ... We saw on the
>> Internet many solution talking about Cluster solution using MySQL ... but
>> nothing about PostgreSQL ... the idea is to use several servers to make a
>> sort of big virtual server using the disk space of each server as one, and
>> having the ability to use the CPU and RAM of each servers in order to
>> maintain good service performance ...one can imagin it is like a GFS but
>> dedicated to postgreSQL...
>> 
> 
> forget mysql cluster for now.

Sorry for the late reply.

I'd second that. I was just on the Solutions Linux in Paris and spoke 
with MySQL people.

There were some questions I had around the new NDB cluster tables and I 
stopped by at their booth. My question if there are any plans to add 
foreign key support to NDB cluster tables got answered with "it will 
definitely be in the next version, which is the one containing NDB 
cluster, so yes, it will support foreign key from the start".

Back home I found some more time to investigate and found this forum 
article http://lists.mysql.com/cluster/1442 posted by a MySQL AB senior 
software architect, where he says exactly the opposite.

I don't know about your application, but trust me that maintaining 
proper referential integrity on the application level against a 
multimaster clustered database isn't that easy. So this is in fact a 
very important question.

Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: PostgreSQL clustering VS MySQL clustering

Jan Wieck <janwieck@yahoo.com> — 2005-02-06T23:06:03Z

On 1/28/2005 2:49 PM, Christopher Browne wrote:

> But there's nothing wrong with the idea of using "pg_dump --data-only"
> against a subscriber node to get you the data without putting a load
> on the origin.  And then pulling the schema from the origin, which
> oughtn't be terribly expensive there.

And there is a script in the current CVS head that extracts the schema 
from the origin in a clean, slony-traces-removed state.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: PostgreSQL clustering VS MySQL clustering

Gaetano Mendola <mendola@bigfoot.com> — 2005-02-18T23:20:08Z

Josh Berkus wrote:
> Tatsuo,
> 
> 
>>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.

Don't you think that this is unsafe ?


SELECT foo(id), id
FROM bar;


where foo have side effect.

Is pgpool able to detect it and perform this select on the master ?


Regards
Gaetano Mendola

Re: PostgreSQL clustering VS MySQL clustering

Gaetano Mendola <mendola@bigfoot.com> — 2005-02-18T23:27:07Z

Jim C. Nasby wrote:
> On Thu, Jan 20, 2005 at 10:08:47AM -0500, Stephen Frost wrote:
> 
>>* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
>>
>>>PostgreSQL has replication, but not partitioning (which is what you want).
>>
>>It doesn't have multi-server partitioning..  It's got partitioning
>>within a single server (doesn't it?  I thought it did, I know it was
>>discussed w/ the guy from Cox Communications and I thought he was using
>>it :).
> 
> 
> No, PostgreSQL doesn't support any kind of partitioning, unless you
> write it yourself. I think there's some work being done in this area,
> though.

Seen my last attempts to perform an horizontal partition I have to say
that postgres do not support it even if you try to write it yourself
(see my post "horizontal partion" ).


Regards
Gaetano Mendola