X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F2C7FD1BAC8 for ; Tue, 9 Mar 2004 22:13:13 +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 80730-02 for ; Tue, 9 Mar 2004 18:13:13 -0400 (AST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 6CDCDD1C95E for ; Tue, 9 Mar 2004 18:13:10 -0400 (AST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i29MCHAc002411; Tue, 9 Mar 2004 15:12:17 -0700 (MST) Date: Tue, 9 Mar 2004 15:12:01 -0700 (MST) From: "scott.marlowe" To: Mira Kaloper Cc: Subject: Re: postgres on apple OS X server In-Reply-To: <404E3145.2060209@ucdavis.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-IHS-MailScanner-Envelope-Sender: scott.marlowe@ihs.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/165 X-Sequence-Number: 12734 On Tue, 9 Mar 2004, Mira Kaloper wrote: > Hi, > > I have a postgres database that is VERY slow - in one table we have 50 > mil records in it. > I have tried to play with postgres.conf file and shared memory but was > not able to speed it up to something that we can work with. We have > apple RAID and I am planning to take database to the raid. I was > wondering if someone has any hints about improving the speed on postgres. > > Currently we have: > shmall = 134217728 > shmmax = 134217728 > > and in postgresql.conf I was setting up values to: > shared_buffers = 15200 > sort_mem = 32168 There are a few areas here. One, is database tuning: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html The next is query tuning. Use explain analyze select ... to see what is taking the longest, and if any of the estimates for rows are way off from actual. Further, check for things like mismatched FK relationships (i.e. a text field fking to an int field). Lastly, make sure you're analyzing your data, and that you've vacuumed the database lately. You may need a vacuum full if you've waited a long time since the last vacuum. The next issue is your hardware. There has been another report here on OSX performing pretty poorly, especially disappointing were the results with apple's XRAID product, which was running about 2/3 as fast as was advertised. You might want to post the output of explain analyze of one of the slow queries. The best list for this stuff is pgsql-perform..