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 87083D1B494 for ; Wed, 26 Nov 2003 22:43:41 +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 91445-01 for ; Wed, 26 Nov 2003 18:43:18 -0400 (AST) Received: from jhuml1.jhmi.edu (jhuml1.jhmi.edu [162.129.234.20]) by svr1.postgresql.org (Postfix) with ESMTP id 19E40D1B489 for ; Wed, 26 Nov 2003 18:43:10 -0400 (AST) Received: from jhuml1.jhmi.edu (jhuml1.jhmi.edu [162.129.234.20]) by jhuml1.jhmi.edu (PMDF V6.2-X17 #30839) with SMTP id <0HOZ006IGDS1HD@jhuml1.jhmi.edu> for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 17:43:16 -0500 (EST) Received: from jhuml1.jhmi.edu ([162.129.234.20]) by jhuml1.jhmi.edu (SAVSMTP 3.1.0.29) with SMTP id M2003112617431615949 for ; Wed, 26 Nov 2003 17:43:16 -0500 Received: from jhmimail.jhmi.edu (jhem2.jhmi.edu [162.129.8.23]) by jhuml1.jhmi.edu (PMDF V6.2-X17 #30839) id <0HOZ00801DS4MO@jhuml1.jhmi.edu> (original mail from lshao2@jhmi.edu) for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 17:43:16 -0500 (EST) Received: from jhmimail.jhmi.edu (jhem2.jhmi.edu [162.129.8.23]) by jhuml1.jhmi.edu (PMDF V6.2-X17 #30839) with ESMTP id <0HOZ0040TDS3GN@jhuml1.jhmi.edu> for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 17:43:16 -0500 (EST) Received: from [162.129.178.60] by jhmimail.jhmi.edu (mshttpd); Wed, 26 Nov 2003 22:46:24 +0000 (GMT) Date: Wed, 26 Nov 2003 22:46:24 +0000 (GMT) From: LIANHE SHAO Subject: Re: very large db performance question To: Neil Conway Cc: pgsql-performance@postgresql.org Message-id: <4e2c4d4e061f.4e061f4e2c4d@jhmimail.jhmi.edu> MIME-version: 1.0 X-Mailer: iPlanet Messenger Express 5.2 HotFix 1.17 (built Jun 23 2003) Content-type: text/plain; charset=us-ascii Content-language: en Content-transfer-encoding: 7bit Content-disposition: inline X-Accept-Language: en X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/282 X-Sequence-Number: 4842 Thanks for reply. Actually our database only supply some scientists to use (we predict that). so there is no workload problem. there is only very infrequent updates. the query is not complex. the problem is, we have one table that store most of the data ( with 200 million rows). In this table, there is a text column which we need to do full text search for each row. The result will then join the data from another table which has 30,000 rows. Now the query runs almost forever. I tried a small table with 2 million rows using the following simple command, it takes me about 6 seconds to get the result back. So, I get confused. That is why I ask: Is it the hardware problem or something else. (I just vacuumed the whole database yesterday). PGA=> select count (*) from expressiondata ; count --------- 2197497 (1 row) PGA=> explain select count (*) from expressiondata ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (cost=46731.71..46731.71 rows=1 width=0) -> Seq Scan on expressiondata (cost=0.00..41237.97 rows=2197497 width=0) (2 rows) Regards, William ----- Original Message ----- From: Neil Conway Date: Wednesday, November 26, 2003 10:03 pm Subject: Re: [PERFORM] very large db performance question > LIANHE SHAO writes: > > 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. > > > Could anybody tell me that our hardware is an issue or not? > > IMHO the size of the DB is less relevant than the query workload. For > example, if you're storying 100GB of data but only doing a single > index scan on it every 10 seconds, any modern machine with enough HD > space should be fine. > > If you give us an idea of the # of queries you expect per second, the > approximate mix of reads and writes, and some idea of how complex the > queries are, we might be able to give you some better advice. > > -Neil > > >