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 3AF8153A27 for ; Thu, 31 Mar 2005 20:07: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 47781-07 for ; Thu, 31 Mar 2005 19:07:20 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id 8748353A12 for ; Thu, 31 Mar 2005 20:07:19 +0100 (BST) Received: from deg.cc (unknown [205.242.255.66]) by mail.deg.cc (Postfix) with ESMTP id 9F80C55FB5 for ; Thu, 31 Mar 2005 14:07:15 -0500 (EST) Message-ID: <424C4A66.3050604@deg.cc> Date: Thu, 31 Mar 2005 14:07:18 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: performance pgsql Subject: Postgresql.conf setting recommendations for 8.0.1 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/687 X-Sequence-Number: 11392 Hi, We are migrating to a new server with more memory and also from postgres 7.4 to postgres 8.0.1 version. Here are my settings on the current 7.4 version: OS : RedHat 9 CPUS: 2 hyperthreaded Memory: 4gig shared_buffers: 65536 sort_mem: 16384 vacuum_mem: 32768 wal_buffers: 64 effective_cache_size: 393216 checkpoint_segments: 3 checkpoint_timeout: 300 checkpoint_warning: 30 These are settings which I am planning on the new machine with 8.0.1 version: OS: Fedora Core 2 CPUs: 2 hyperthreaded Memory: 8 gig shared_buffers: 131072 work_mem: 32768 maintanence_work_mem: 65536 wal_buffers: 64 effective_cache_size: 786432 checkpoint_segments: 8 checkpoint_timeout: 600 checkpoint_warning: 30 The current settings on my 7.4 version gives me very good performance, so I basically doubled the settings since i will be having the double the memory in the new machine. What my concern is about the effective_cache_settings , according the docs its recommends me to set max to about 2/3 of the total memory and I went little over on top of it, is that ok ? I went little over on my current 7.4 system too, and its giving me very good performance so I used the same calculation for my new system too. Also, can anyone guide me with the ideal settings for vacuum_cost_delay, vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty, vacuum_cost_limit, background_delay, bgwriter_percent, bgwriter_maxpages settings. I am not sure what settings should I make to these parameters , are there any ideal settings for these parameters in a OLTP environment ? Thanks! Pallav