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 5257232A087 for ; Tue, 19 Oct 2004 23:32:07 +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 40973-05 for ; Tue, 19 Oct 2004 22:31:59 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.195]) by svr1.postgresql.org (Postfix) with ESMTP id 1AA3132A0F4 for ; Tue, 19 Oct 2004 23:32:00 +0100 (BST) Received: by mproxy.gmail.com with SMTP id 73so395398rnk for ; Tue, 19 Oct 2004 15:32:00 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=jaqaVwwr0jWoW0C6unOf4Gk1VEACtIVDrSfW3+UHxuOwM2+COPo51IivOpOSVAyMRCWZwTLLdsrhoPB4OfwgDkW+5NoTfe/4uoZMh5kTSyor9vWGqBOs2asFyVycFtrBAPWARWrwq5nELo966bIqIL8VVFAt1REUathkn44xFfA Received: by 10.38.181.77 with SMTP id d77mr2218334rnf; Tue, 19 Oct 2004 15:32:00 -0700 (PDT) Received: by 10.38.76.42 with HTTP; Tue, 19 Oct 2004 15:31:59 -0700 (PDT) Message-ID: <4a0cafe2041019153133f854e9@mail.gmail.com> Date: Tue, 19 Oct 2004 17:31:59 -0500 From: Josh Close Reply-To: Josh Close To: POSTGRES-PERFORMANCE Subject: how much mem to give postgres? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit 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/296 X-Sequence-Number: 8772 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. 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? 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. What else can I do to speed this server up? I'm running vacuum analyze on the heavily updated/inserted/deleted db's once an hour, and doing a full vacuum once a night. Should I change the vacuum mem setting at all? 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. Any help would be great. This server is very very slow at the moment. Also, I'm using a 2.6.8.1 kernel with high mem enabled, so all the ram is recognized. Thanks. -Josh