Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C34EE64FEF7 for ; Wed, 12 Nov 2008 22:26:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73148-07 for ; Wed, 12 Nov 2008 22:26:03 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mail.postgresql.org (Postfix) with ESMTP id 846E964FDA8 for ; Wed, 12 Nov 2008 22:26:03 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so648975rvf.43 for ; Wed, 12 Nov 2008 18:26:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=iOPT0/4U8hyrxMJyKz07Mj27xnYMkBX0sRP+31xeIvM=; b=osJDEQKBx5buuVLihehVGAFc5A6mVNs303CSXcTfBDX2hqyeoAnTxOVTW0gd/V1LGc x2g6thkrZh/hxi5QllvPs2oAWaoRYDnL63pej2xmmk6oLt5jEBtw/yMIAkTJLFLIhMam 5YlOdQRHQFgx4yhNUhlp+s0pkwUwDdaqIxG4I= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=Pb3qybBfOSSw9dBPd7yWcS7yK74tXMHTz1PhFfrdltVYeg/3Lp3ObshN6BHkPn1usV A6b5svFiCkcMavecfDX/ht+DW0oWEcZBkigFyx2NqSwol3zUslwc70OesUeJ0HB98qA4 Hj8wboEDIpB3Igizr/iI7ofM60kmGXJ8/EXOU= Received: by 10.115.94.1 with SMTP id w1mr6498566wal.30.1226543162115; Wed, 12 Nov 2008 18:26:02 -0800 (PST) Received: by 10.114.39.11 with HTTP; Wed, 12 Nov 2008 18:26:02 -0800 (PST) Message-ID: Date: Wed, 12 Nov 2008 19:26:02 -0700 From: "Scott Marlowe" To: "- -" Subject: Re: Performance Question Cc: pgsql-performance@postgresql.org In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <838490.42593.qm@web34202.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/118 X-Sequence-Number: 31849 On Wed, Nov 12, 2008 at 9:27 AM, - - wrote: > I've been searching for performance metrics and tweaks for a few weeks now. > I'm trying to determine if the length of time to process my queries is > accurate or not and I'm having a difficult time determining that. I know > postgres performance is very dependent on hardware and settings and I > understand how difficult it is to tackle. However, I was wondering if I > could get some feedback based on my results please. > > The database is running on a dual-core 2GHz Opteron processor with 8GB of > RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad > for Postgres, but moving the database to another server didn't change > performance at all). Some of the key parameters from postgresql.conf are: I'm not sure what you mean. Did you move it to another server with a single drive? A 100 drive RAID-10 array with a battery backed caching controller? There's a lot of possibility in "another server". > > max_connections = 100 > shared_buffers = 16MB WAY low. try 512M to 2G on a machine that big. > work_mem = 64MB acceptable. For 100 clients, if each did a sort you'd need 6.4Gig of free ram, but since the chances of all 100 clients doing a sort that big at the same time are small, you're probably safe. > > One of my tables has 660,000 records and doing a SELECT * from that table > (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 > columns almost doubles that time to an average of 123 seconds. To me, those How wide is this table? IF it's got 300 columns, then it's gonna be a lot slower than if it has 10 columns. Try running your query like this: \timing select count(*) from (my big query goes here) as a; and see how long it takes. This will remove the network effect of transferring the data. If that runs fast enough, then the real problem is that your client is waiting til it gets all the data to display it.