From owner-freebsd-performance@FreeBSD.ORG Mon Mar 14 09:07:10 2005 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2BC1A16A4CE for ; Mon, 14 Mar 2005 09:07:10 +0000 (GMT) Received: from web26805.mail.ukl.yahoo.com (web26805.mail.ukl.yahoo.com [217.146.176.81]) by mx1.FreeBSD.org (Postfix) with SMTP id 2894243D5D for ; Mon, 14 Mar 2005 09:07:09 +0000 (GMT) (envelope-from cguttesen@yahoo.dk) Received: (qmail 4221 invoked by uid 60001); 14 Mar 2005 09:07:08 -0000 Message-ID: <20050314090708.4219.qmail@web26805.mail.ukl.yahoo.com> Received: from [194.248.174.58] by web26805.mail.ukl.yahoo.com via HTTP; Mon, 14 Mar 2005 10:07:08 CET Date: Mon, 14 Mar 2005 10:07:08 +0100 (CET) From: Claus Guttesen To: freebsd-performance@freebsd.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: postgresql, max_fsm_pages on FreeBSD 5.3 X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 14 Mar 2005 09:07:10 -0000 Hi. I'm running a postgresql 7.4.6 db, where the largest table having 13 mill. records. The db-server is a quad-opteron with 4 GB RAM. I've configured shared_buffers to 8192, sort_mem to 4096 and effective_cache_size to 4096. max_fsm_relations and max_fsm_pages was easy to configure when I found http://www.desknow.com/kb/idx/12/061/article/ (max_fsm_pages and max_fsm_relations). The odd thing is that according to a vacuum verbose where total pages needed always seems to be approx. 15.000 pages larger than what max_fsm_pages is configured to. So I keept increasing max_fsm_pages, from 60000, 80000 and now 100000. This morning my beloved crontab send me a verbose vacuum telling me "114848 total pages needed". max_fsm_relations remains steady at 300 (245 relations according to the output). Not that I mind increasing this value, but is there any limit to this value, or will I reach some thresshold where this setting will become counterproductive? The docs says that max_fsm_pages "Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map." But what does that actually mean? Postgresql does seem to perform better when I tweak max_fsm_pages. The above mentioned shared buffers, sort mem and effective cache size did become counterproductive when I increased them too much. regards Claus