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 9AB24D1DC04 for ; Tue, 25 Nov 2003 14:05:46 +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 57255-01 for ; Tue, 25 Nov 2003 10:05:19 -0400 (AST) Received: from mail.dsvr.co.uk (mail.dsvr.co.uk [212.69.192.9]) by svr1.postgresql.org (Postfix) with ESMTP id 53F83D1DC3E for ; Tue, 25 Nov 2003 10:05:15 -0400 (AST) Received: from dsvr.net (eth0.rt1.o1-1.tck.dsvr.net [::ffff:212.69.216.20]) by mail.dsvr.co.uk with esmtp; Tue, 25 Nov 2003 14:05:17 +0000 Message-ID: <3FC3623E.3020304@dsvr.net> Date: Tue, 25 Nov 2003 14:07:58 +0000 From: Rob Fielding User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030807 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/239 X-Sequence-Number: 4799 The problems with giving suggestions about increasing performance is that one persons increase is another persons decrease. having said that, there are a few general suggestions : Set-up some shared memory, about a tenth of your available RAM, and configure shared_memory and max_clients correctly. I've used the following formula, ripped off the net from somewhere. It's not entirely acurate, as other settings steal a little shared memory, but it works for the most part : ((1024*RAM_SIZE) - (14.2 * max_connections) - 250) / 8.2 as I say, it should get you a good value, otherwise lower it bit by bit if you have trouble starting your db. Increase effective_cache (50%-70% avail ram) and sort_mem (about 1/20th ram) and lower you random_page_cost to around 2 or less (as low as 0.3) if you have fast SCSI drives in a RAID10 set-up - this was a big speedup ;) But this might not be the answer though. The values detailed above are when tuning an already stable setup. Perhaps you need to look at your system resource usage. If you're degrading performance over time it sounds to me like you are slowly running out of memory and swap ? Generall if I take something over, I'll try and get it onto my terms. Have you tried importing the DB to a fresh installation, one where you know sensible defaults are set, so you aren't inheriting any cruft from the previous sysadmin. To be honest tho, I've never run pg so that it actually shutdown because it was running so badly - i just wouldn't think it would do that. -- Rob Fielding rob@dsvr.net www.dsvr.co.uk Development Designer Servers Ltd