X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C4E8D1B4F8 for ; Tue, 21 Oct 2003 16:44:18 +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 04526-01 for ; Tue, 21 Oct 2003 13:43:47 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id E7817D1B4E5 for ; Tue, 21 Oct 2003 13:43:46 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id h9LGhkNu038124 for ; Tue, 21 Oct 2003 16:43:46 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h9LGPtcq035214 for pgsql-performance@postgresql.org; Tue, 21 Oct 2003 16:25:55 GMT From: William Yu X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Tuning for mid-size server Date: Tue, 21 Oct 2003 09:25:56 -0700 Organization: Hub.Org Networking Services Lines: 38 Message-ID: References: <2F2E24372F10744588A27DEECC85FE04B6769C@vt-pe2550-001.vantage.vantage.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en In-Reply-To: <2F2E24372F10744588A27DEECC85FE04B6769C@vt-pe2550-001.vantage.vantage.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/625 X-Sequence-Number: 4373 Anjan Dave wrote: > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 250,000 is probably the max you can use due to the 2GB process limit unless you recompile the Linux Kernel to use 3GB process/1GB kernel. Yes, I've got 8GB also and I started at 262144 and kept working my way down until Linux would allocate the memory. > > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - 167772 > > Effective_cache_size = 262144 (same as shared_buffers - 25%) This should reflect the amount of memory available for caching. And unless you plan on running a ton of memory hogging software on the same machine, you probably will have 6GB available as cache. Top on my system confirms the 6GB number so I've got my setting at 750,000. (Left a little space for OS/programs/etc.) > In the /etc/sysctl file: > ================= > kernel.shmall = 536870912 (512MB) SHMALL Total amount of shared memory > available (bytes or pages) > kernel.shmmax = 536870912 (512MB) SHMMAX Maximum size of shared memory > segment (bytes) Ain't gonna happen unless you recompile the linux kernel to do 3/1. Through trial-and-error, I've found the largest number is: 2,147,483,648 > Are the above settings ok to begin with? Are there any other parameters > that I should configure now, or monitor lateron? Above is pretty good. I'd also bump up the free space map settings and maybe try to symlink the pg_xlog directory (log files) to a seperate drive.