From owner-freebsd-performance@FreeBSD.ORG Mon Mar 14 09:07:10 2005 Return-Path: <owner-freebsd-performance@FreeBSD.ORG> 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 <freebsd-performance@freebsd.org>; 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 <freebsd-performance@freebsd.org>; 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 <cguttesen@yahoo.dk> 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 <freebsd-performance.freebsd.org> List-Unsubscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-performance>, <mailto:freebsd-performance-request@freebsd.org?subject=unsubscribe> List-Archive: <http://lists.freebsd.org/pipermail/freebsd-performance> List-Post: <mailto:freebsd-performance@freebsd.org> List-Help: <mailto:freebsd-performance-request@freebsd.org?subject=help> List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-performance>, <mailto:freebsd-performance-request@freebsd.org?subject=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