X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B9C41D1B451 for ; Thu, 27 Nov 2003 08:41:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47479-07 for ; Thu, 27 Nov 2003 04:41:28 -0400 (AST) Received: from ecbull20.frec.bull.fr (ecbull20.frec.bull.fr [129.183.4.3]) by svr1.postgresql.org (Postfix) with ESMTP id BE283D1B43F for ; Thu, 27 Nov 2003 04:41:25 -0400 (AST) Received: from ecn002.frec.bull.fr (ecn002.frec.bull.fr [129.183.4.6]) by ecbull20.frec.bull.fr (8.9.2/8.9.1) with ESMTP id JAA20544; Thu, 27 Nov 2003 09:42:29 +0100 Received: from BULL.NET ([129.183.148.134]) by ecn002.frec.bull.fr (Lotus Domino Release 5.0.12) with ESMTP id 2003112709454775:638 ; Thu, 27 Nov 2003 09:45:47 +0100 Message-ID: <3FC5B8F6.49A663E7@BULL.NET> Date: Thu, 27 Nov 2003 09:42:30 +0100 From: Thierry Missimilly Organization: BSIS/R&D X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: fr,en MIME-Version: 1.0 To: LIANHE SHAO Cc: pgsql-performance@postgresql.org Subject: Re: very large db performance question References: <4cbacd4cb3c2.4cb3c24cbacd@jhmimail.jhmi.edu> X-MIMETrack: Itemize by SMTP Server on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 27/11/2003 09:45:47, Serialize by Router on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 27/11/2003 09:45:55, Serialize complete at 27/11/2003 09:45:55 Content-Type: multipart/mixed; boundary="------------F715D175693138A496FD42FA" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/290 X-Sequence-Number: 4850 This is a multi-part message in MIME format. --------------F715D175693138A496FD42FA Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I have done some performance tests on 1Gb and 4 Gb Databases on a mono Pentium 4 , 1 Gb RAM, IDE disk, SCSI disks and RAID0 LUN on DAS 5300 on Linux RedHat 7.3. In each cases my tests make select, update and insert. One of them is pgbench. You can find it in Postgres/contrib/pgbench. The other one is DBT1 form OSDL. I have port it on Postgres and you can find it in Source Forge. Jenny Wang is writting a better Postgres DBT1 based on C transactions instead of PL/pgSQL Transactions. With this size of database, even after a fine tuning of Postgres the problem is I/O Wait. So in you case with 100 Gb, you will have I/O Wait. To resume my observations regarding diskss performances : 1) IDE disk are the slower. 2) SCSI disks are a little more faster but you can decrease I/O Wait by 25% by creating a stripped volume group on 3 disks. 3) A RAID0 on 5 DAS5300 disks improve again performances by 20% as the DAS Storage Processeur use internal caches One thing, very important in my case was the time of (hot) backup / restore. In that case the pgbench database schema is to simple to have an idea but DBT1 schema is enough complex and on the RAID0 LUN the backup takes 12 min but the restore takes 16 min + 10 min to recreate the indexes + 255 min to recreate the Foreign Keys. So 4h41 for a 4Gb database. That means for a 100 Gb database, if your schema as Foreign keys and indexes : about 5 hours to backup and 117 hours to restore (~5 days). So, if your database in a critical database, it is better to use cold backup with Snapshot tools. Regards, Thierry Missimilly LIANHE SHAO wrote: > Hello All, > We will have a very large database to store > microarray data (may exceed 80-100G some day). now > we have 1G RAM, 2G Hz Pentium 4, 1 CPU. and enough > hard disk. > > I never touched such large database before. I ask > several dbas if the hardware is ok, some said it is > ok for the query, but I am not so convinced. Because > I check the mailing list and learned that it is not > unreasonable to take several minutes to do the > query. But I want to query to be as fast as possible. > > Could anybody tell me that our hardware is an issue > or not? do we really need better hardware to make > real difference? > > Regards, > William > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend --------------F715D175693138A496FD42FA Content-Type: text/x-vcard; charset=us-ascii; name="THIERRY.MISSIMILLY.vcf" Content-Description: Card for Thierry Missimilly Content-Disposition: attachment; filename="THIERRY.MISSIMILLY.vcf" Content-Transfer-Encoding: 7bit begin:vcard n:Missimilly;Thierry tel;fax:+33 (0)4 76 29 78 78 tel;work:+33 (0)4 76 29 74 54 x-mozilla-html:FALSE url:http:\\www.bull.com org:BIS/R&D adr:;;Bull SA, 1, rue de provence - BP 208;ECHIROLLES;;38432;FRANCE version:2.1 email;internet:Thierry.Missimilly@bull.net x-mozilla-cpt:;-18184 fn:Thierry Missimilly end:vcard --------------F715D175693138A496FD42FA--