X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 23D1ED1C932 for ; Tue, 5 Aug 2003 14:38:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 11804-07 for ; Tue, 5 Aug 2003 11:36:51 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 22C46D1C9C8 for ; Tue, 5 Aug 2003 11:37:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h75EbwSa022365; Tue, 5 Aug 2003 10:37:59 -0400 (EDT) To: Jeff Cc: pgsql-performance@postgresql.org Subject: Re: Some vacuum & tuning help In-reply-to: References: Comments: In-reply-to Jeff message dated "Tue, 05 Aug 2003 08:09:19 -0400" Date: Tue, 05 Aug 2003 10:37:58 -0400 Message-ID: <22364.1060094278@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/64 X-Sequence-Number: 2881 Jeff writes: > Here's the msot recent vacuum for the "active" table. It gets a few > hundred updates/inserts a minute constantly throughout the day. > INFO: Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed > 1003361. > Total CPU 2.18s/0.61u sec elapsed 2.78 sec. > I see unused is quite high. This morning I bumped max_fsm_pages to 500000. > If I'm thinking right you want unused and max_fsm to be closish, right? No, they're unrelated. UnUsed is the number of currently-unused tuple pointers in page headers, whereas the FSM parameters are measured in pages. 30000 FSM slots would be more than enough for this table. The above numbers don't seem terribly unreasonable to me, although probably UnUsed would be smaller if you'd been vacuuming more often. If you see UnUsed continuing to increase then you definitely ought to shorten the intervacuum time. VACUUM FULL does not reclaim unused tuple pointers AFAIR, except where it is able to release entire pages at the end of the relation. So if you really wanted to get back down to nil UnUsed, you'd need to do a dump and reload of the table (or near equivalent, such as CLUSTER). Not sure it's worth the trouble. regards, tom lane