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 B87063A4B8E for ; Fri, 21 Jan 2005 18:18:14 +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 72159-06 for ; Fri, 21 Jan 2005 18:18:05 +0000 (GMT) Received: from outputservices.com (outputt1130.customer.frii.net [216.17.159.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5EA2F3A4B17 for ; Fri, 21 Jan 2005 18:18:04 +0000 (GMT) Received: from outputservices.com (outputservices.com [137.106.76.15]) by outputservices.com (8.10.2+Sun/8.11.3) with ESMTP id j0LII0J01596; Fri, 21 Jan 2005 11:18:00 -0700 (MST) Message-ID: <41F14758.4030602@outputservices.com> Date: Fri, 21 Jan 2005 11:18:00 -0700 From: Marty Scholes User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:0.9.9) Gecko/20020517 X-Accept-Language: en-us, en MIME-Version: 1.0 To: herve@elma.fr, pgsql-performance@postgresql.org Cc: Marty Scholes Subject: Re: PostgreSQL clustering VS MySQL clustering References: <41F144FE.30902@outputservices.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/397 X-Sequence-Number: 10087 This is probably a lot easier than you would think. You say that your=20 DB will have lots of data, lots of updates and lots of reads. Very likely the disk bottleneck is mostly index reads and writes, with=20 some critical WAL fsync() calls. In the grand scheme of things, the=20 actual data is likely not accessed very often. The indexes can be put on a RAM disk tablespace and that's the end of=20 index problems -- just make sure you have enough memory available. Also = make sure that the machine can restart correctly after a crash: the=20 tablespace is dropped and recreated, along with the indexes. This will=20 cause a machine restart to take some time. After that, if the WAL fsync() calls are becoming a problem, put the WAL = files on a fast RAID array, etiher a card or external enclosure, that=20 has a good amount of battery-backed write cache. This way, the WAL=20 fsync() calls will flush quickly to the RAM and Pg can move on while the = RAID controller worries about putting the data to disk. With WAL, low=20 access time is usually more important than total throughput. The truth is that you could have this running for not much money. Good Luck, Marty > Le Jeudi 20 Janvier 2005 19:09, Bruno Almeida do Lago a =E9crit : > > Could you explain us what do you have in mind for that solution? I m= ean, > > forget the PostgreSQL (or any other database) restrictions and=20 > explain us > > how this hardware would be. Where the data would be stored? > > > > I've something in mind for you, but first I need to understand your = > needs! >=20 > I just want to make a big database as explained in my first mail ... At= the > beginning we will have aprox. 150 000 000 records ... each month we wil= l=20 > add > about 4/8 millions new rows in constant flow during the day ... and in = same > time web users will access to the database in order to read those data.= > Stored data are quite close to data stored by google ... (we are not=20 > making a > google clone ... just a lot of data many small values and some big ones= ... > that's why I'm comparing with google for data storage). > Then we will have a search engine searching into those data ... >=20 > Dealing about the hardware, for the moment we have only a bi-pentium Xe= on > 2.8Ghz with 4 Gb of RAM ... and we saw we had bad performance results=20 > ... so > we are thinking about a new solution with maybe several servers (server= > design may vary from one to other) ... to get a kind of cluster to get = > better > performance ... >=20 > Am I clear ? >=20 > Regards,