Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A810F650518 for ; Thu, 13 Nov 2008 09:07:36 -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 99486-02-9 for ; Thu, 13 Nov 2008 09:07:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.177]) by mail.postgresql.org (Postfix) with ESMTP id 1D54864FCF0 for ; Thu, 13 Nov 2008 09:05:57 -0400 (AST) Received: by ik-out-1112.google.com with SMTP id c21so741637ika.3 for ; Thu, 13 Nov 2008 05:05:55 -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=+TWFOk6TghiyyW4v+IXBpV2ME251ehh2qzt+8g4bzmc=; b=UA8DIRX0OXMMwSKtoF8mjMaXYmRrmwWpX90a/OpAxMmHhLDlkbQe0GRx8CwGtNiGH2 ge3SusxUdRV6dN7Ybz5OlhXDt/dWtlp6+0vz40KKwPcmL+ZWjjWc+cNhIA++7BsKJK5u WbPh0cwP5WTNksh3FdKETVScV0APLoBULHbn0= 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=DWa8qa6iU7b5EoPZhwO4Zw2F++CFpfewfH5er3lMXEJkVXYyDGPk0/3rEtSsVpf+Yt 5nzjPWXYdoZiWHnyOJVVP86jQAQV1k4iRTFMuKoY300dE6+D4n0h9y+RQm6OhIpuwDgy JSTYY5Do/1JhCCQP13XSiLV5mpKEBbf4XWTa0= Received: by 10.103.239.10 with SMTP id q10mr5813094mur.67.1226581555603; Thu, 13 Nov 2008 05:05:55 -0800 (PST) Received: by 10.102.247.18 with HTTP; Thu, 13 Nov 2008 05:05:55 -0800 (PST) Message-ID: Date: Thu, 13 Nov 2008 08:05:55 -0500 From: "Merlin Moncure" 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/121 X-Sequence-Number: 31852 On Wed, Nov 12, 2008 at 11: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: > > max_connections = 100 > shared_buffers = 16MB > work_mem = 64MB > everything else is set to the default > > 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 > numbers are crazy slow and I don't understand why the queries are taking so > long. The tables are UTF-8 encode and contain a mix of languages (English, > Spanish, etc). I'm running the query from pgadmin3 on a remote host. The > server has nothing else running on it except the database. > > As a test I tried splitting up the data across a number of other tables. I > ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join > the results together. This was even slower, taking an average of 103 seconds > to complete the generic select all query. > > I'm convinced something is wrong, I just can't pinpoint where it is. I can > provide any other information necessary. If anyone has any suggestions it > would be greatly appreciated. Maybe there is a lot of dead rows? Do a VACUUM VERBOSE; That performance is quite slow unless the rows are really big (you have huge text or bytea columns). What is the average row size in bytes? Try running the following command as a benchmark: select generate_series(1,500000); on my imac that takes about 600ms. merlin