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 2A57756B43 for ; Mon, 21 Mar 2005 23:49:27 +0000 (GMT) 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 48390-03 for ; Mon, 21 Mar 2005 23:49:22 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id C1DFB56B6D for ; Mon, 21 Mar 2005 23:49:19 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr4.postgresql.org (Postfix) with ESMTP id 802465AF986 for ; Mon, 21 Mar 2005 23:03:17 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2LMustL002603; Mon, 21 Mar 2005 17:56:54 -0500 (EST) To: Steve Crawford Cc: "Matthew T. O'Connor" , pgsql-admin@postgresql.org Subject: Re: Excessive growth of pg_attribute and other system tables In-reply-to: <200503211235.22731.scrawford@pinpointresearch.com> References: <200503171515.38214.scrawford@pinpointresearch.com> <423F17D7.7040904@zeut.net> <1219.1111434030@sss.pgh.pa.us> <200503211235.22731.scrawford@pinpointresearch.com> Comments: In-reply-to Steve Crawford message dated "Mon, 21 Mar 2005 12:35:22 -0800" Date: Mon, 21 Mar 2005 17:56:54 -0500 Message-ID: <2602.1111445814@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/336 X-Sequence-Number: 16834 Steve Crawford writes: > Just to make sure I'm understanding things correctly this time...I > originally (mis)understood these as settings related to resources > used _during_ vacuuming. My current understanding is that they are > basically pointers that track what space is available for reclamation > by vaccum and that the amount of fsm resources required depends on > both frequency of vacuums and volume of updates/deletes. The FSM is where VACUUM stores pointers to the free space it's found (or created) in each table. Subsequent INSERTs/UPDATEs will use this free space instead of appending to the file. So to prevent table growth, you need enough FSM slots to remember enough free space to satisfy all the INSERTs/UPDATEs between successive VACUUM runs. In practice people tend to allocate enough FSM to cover all of their database, instead of worrying about exactly which pages might contain free space. In a low-update-volume situation you could probably get away with less. > 2) What happens with all that free-space information at server restart > (ie. does a server restart lead to dead-tuple leakage)? Assuming you had a normal database shutdown rather than a crash, it's written out at shutdown and reloaded. In any case, a VACUUM recomputes the info from scratch. > 4) Is there a way to query what proportion of the fsm resources are in > use and would access to that info be useful to the autovacuum daemon > or a system tuner? VACUUM VERBOSE will tell you about this. regards, tom lane