Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6AFAA9FB6DD for ; Mon, 16 Apr 2007 19:08:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 17938-01-9 for ; Mon, 16 Apr 2007 19:08:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id C7C8A9FB6E8 for ; Mon, 16 Apr 2007 19:08:23 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l3GM8K8H029621; Mon, 16 Apr 2007 15:08:21 -0700 Message-ID: <4623F3F6.8040408@commandprompt.com> Date: Mon, 16 Apr 2007 15:08:54 -0700 From: "Joshua D. Drake" User-Agent: Thunderbird 1.5.0.10 (X11/20070306) MIME-Version: 1.0 To: "Kieran Cooper, Lyris UK" CC: pgsql-admin@postgresql.org Subject: Re: Vacuum stops with misleading max_fsm_pages error References: <001901c78071$9a9a1f00$0b02a8c0@KieranX15> In-Reply-To: <001901c78071$9a9a1f00$0b02a8c0@KieranX15> Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Mon, 16 Apr 2007 15:08:21 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/143 X-Sequence-Number: 25207 Kieran Cooper, Lyris UK wrote: > Hi there, > > We're managing a database on version 8.2.3 (running Lyris ListManager > email software). I ran a 'vacuum analyze full verbose' (we haven't been > able to get autovacuum to work properly on Lyris installs so we need to > run a full vacuum every week or so, and we'd been doing some work on > this machine so it needed it) Why not just set up a regular vacuum to run twice a day? . It ran happily for quite a while - about > 2 thirds of the way through the database - but then ended with the lines > > INFO: free space map contains 20914 pages in 61 relations > DETAIL: A total of 14992 page slots are in use (including overhead). > 14992 page slots are required to track all free space. > Current limits are: 900000 page slots, 6000 relations, using 5659 kB. > > This happens (at more or less the same point but with slightly different > figures) each time I try to run this command. It also happens even if I > only do vacuum verbose. I am not sure what your question is. The above looks perfectly reasonable. > > I can vacuum individual tables quite happily but I'm not sure I can > manage to do all of them individually. See comment above about using a vacuum/vacuum analyze twice a day. > > As you see, I've upped the page slots and relations (there are 131 > tables in the database in total) and it seems to me like there is more > than enough. Yep. > > Any ideas would be gratefully received! ...? The only thing I would say is that you are entirely too many max_fsm_pages and max_fsm_relations. I would drop it back dow: max_fsm_pages = 100000 max_fsm_relations = 1000 But that doesn't really answer your question as much as state that you don't need as much as you have. Sincerely, Joshua D. Drake > Thanks > Kieran Cooper > Lyris UK > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/