X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D34F1329F9B for ; Wed, 20 Oct 2004 01:33:22 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91479-03 for ; Wed, 20 Oct 2004 00:33:10 +0000 (GMT) Received: from cmailm1.svr.pol.co.uk (cmailm1.svr.pol.co.uk [195.92.193.18]) by svr1.postgresql.org (Postfix) with ESMTP id 29BB7329F0C for ; Wed, 20 Oct 2004 01:33:12 +0100 (BST) Received: from modem-1859.llama.dialup.pol.co.uk ([217.135.183.67] helo=Nightingale) by cmailm1.svr.pol.co.uk with smtp (Exim 4.14) id 1CK4Pg-0008QN-Hz; Wed, 20 Oct 2004 01:33:12 +0100 Message-ID: <01f301c4b63c$643340b0$6400a8c0@Nightingale> From: "Simon Riggs" To: "Josh Close" , "POSTGRES-PERFORMANCE" References: <4a0cafe2041019153133f854e9@mail.gmail.com> Subject: Re: how much mem to give postgres? Date: Wed, 20 Oct 2004 01:33:16 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/301 X-Sequence-Number: 8777 >Josh Close > I'm trying to figure out what I need to do to get my postgres server > moving faster. It's just crawling right now. It's on a p4 HT with 2 > gigs of mem. ....and using what version of PostgreSQL are you using? 8.0beta, I hope? > I was thinking I need to increase the amount of shared buffers, but > I've been told "the sweet spot for shared_buffers is usually on the > order of 10000 buffers". I already have it set at 21,078. If you have, > say 100 gigs of ram, are you supposed to still only give postgres > 10,000? Thats under test currently. My answer would be, "clearly not", others differ, for varying reasons. > Also, do I need to up the shmmax at all? I've used the formula "250 kB > + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity" > at http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC > but it's never quite high enough, so I just make sure it's above the > amount that the postgres log says it needs. shmmax isn't a tuning parameter for PostgreSQL, its just a limit. If you get no error messages, then its high enough. > Are there any other settings I should be concerned with? I've heard > about the effective_cache_size setting, but I haven't seen anything on > what the size should be. wal_buffers if the databases are heavily updated. > Any help would be great. This server is very very slow at the moment. > Try *very fast disks*, especially for the logs. Best regards, Simon Riggs