X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 105973A4D13 for ; Sat, 22 Jan 2005 00:19:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36986-03 for ; Sat, 22 Jan 2005 00:19:37 +0000 (GMT) Received: from mail14.speakeasy.net (mail22.sea5.speakeasy.net [69.17.117.24]) by svr1.postgresql.org (Postfix) with ESMTP id A77F83A5113 for ; Fri, 21 Jan 2005 22:34:42 +0000 (GMT) Received: (qmail 30336 invoked from network); 21 Jan 2005 22:34:42 -0000 Received: from mail.kinesis-cem.com (HELO pdarley) ([64.81.9.230]) (envelope-sender ) by mail14.speakeasy.net (qmail-ldap-1.03) with SMTP for ; 21 Jan 2005 22:34:41 -0000 From: "Peter Darley" To: "Tatsuo Ishii" , Cc: , , , Subject: Re: PostgreSQL clustering VS MySQL clustering Date: Fri, 21 Jan 2005 14:34:40 -0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <20050121.104007.74755729.t-ishii@sra.co.jp> Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/402 X-Sequence-Number: 10092 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)