X-Original-To: pgsql-www-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 90AE53A478C; Thu, 4 Nov 2004 01:53:45 +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 69526-07; Thu, 4 Nov 2004 01:53:40 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 988A63A4789; Thu, 4 Nov 2004 01:53:43 +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 iA41rhCG019431; Wed, 3 Nov 2004 20:53:43 -0500 (EST) To: "Marc G. Fournier" Cc: Justin Clift , pgsql-www@postgresql.org Subject: Re: pg_autovacuum is nice ... but ... In-reply-to: <20041103201625.S82047@ganymede.hub.org> References: <20041103155855.O82047@ganymede.hub.org> <41895BDA.1090903@postgresql.org> <20041103201625.S82047@ganymede.hub.org> Comments: In-reply-to "Marc G. Fournier" message dated "Wed, 03 Nov 2004 20:20:47 -0400" Date: Wed, 03 Nov 2004 20:53:43 -0500 Message-ID: <19430.1099533223@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/77 X-Sequence-Number: 5808 "Marc G. Fournier" writes: > Here is a vacuum verbose on gborg's database: > INFO: free space map: 1000 relations, 7454 pages stored; 23072 total pages needed > DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory. > and this is portal: > INFO: free space map: 1000 relations, 7425 pages stored; 23024 total pages needed > DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory. > so, you tell me ... should I increase them? Yup. 20000 < 23072, so you're losing some proportion of FSM entries. What's worse, the FSM relation table is maxed out (1000 = 1000) which suggests that there are relations not being tracked at all; you have no idea how much space is getting leaked in those. You can determine the number of relations potentially needing FSM entries by select count(*) from pg_class where relkind in ('r','i','t'); --- sum over all databases in the cluster to get the right result. Once you've fixed max_fsm_relations, do vacuums in all databases, and then vacuum verbose should give you a usable lower bound for max_fsm_pages. regards, tom lane