X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3B62952A3D for ; Fri, 29 Jul 2005 12:47:44 -0300 (ADT) 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 72508-05 for ; Fri, 29 Jul 2005 15:47:40 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 7760C52A14 for ; Fri, 29 Jul 2005 12:47:37 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 29 Jul 2005 10:47:35 -0500 Subject: Re: postgres optimization From: Scott Marlowe To: Kailash Vyas Cc: pgsql-admin@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1122652055.32465.56.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 29 Jul 2005 10:47:35 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/249 X-Sequence-Number: 18305 On Fri, 2005-07-29 at 08:24, Kailash Vyas wrote: > hi > > i am optmizing postgres database and need some help on it. > I currently have a server with 2 Gb RAM. > > I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax > to 536870912 i.e 512 Mb. > should i change it to 2 Gb and how will it affect the perfomance and > will there be any downsides to it or is it better to keep it less. Generally speaking, you're better off letting your kernel do the majority of CACHING, and letting postgresql do BUFFERING. On a linux box, the kernel will use all spare memory to cache disk accesses automagically. So, assuming postgresql uses up a few hundred megs, after the machine reaches a steady state, the kernel should be using the 1.5 gig or so left over for caching. While the latest versions of postgresql have much improved caching algorithms for its buffers, it still pretty much dumps the buffers when the last backend looking at them disconnects. I.e. it's not a persistent caching system like the kernel cache is. > I then changed the shared buffers in config file according to this > expression specifies in postgres manual > > max connections=128 > 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or > infinity > > > shared buffer=65277 That's large, but not unreasonable for a machine handling a large dataset. I assume you do have a decent sized dataset (a gig or more) to handle. > what value should i specify for Effective Cache Size. I came across > this article but it is slightly confusing. Effective cache size just tells the query planner about how much memory the OS is using to cache your dataset. Bring the machine up, run lots of queries, and check the cache and buffers with top, and there's your amount. divide by 8k to get the setting for effective cache size.