Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 499CF64FDE7 for ; Tue, 28 Oct 2008 12:36:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 94044-04 for ; Tue, 28 Oct 2008 12:36:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail136072.authsmtp.net (outmail136072.authsmtp.net [62.13.136.72]) by postgresql.org (Postfix) with ESMTP id 958EA64FD32 for ; Tue, 28 Oct 2008 12:36:29 -0300 (ADT) Received: from mail-c189.authsmtp.com (mail-c189.authsmtp.com [62.13.128.71]) by punt9.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m9SFZuCL020704; Tue, 28 Oct 2008 15:35:56 GMT Received: from [192.168.0.3] (85-211-99-122.dyn.gotadsl.co.uk [85.211.99.122]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m9SFZs7q036513; Tue, 28 Oct 2008 15:35:55 GMT Subject: Re: Updating FSM on recovery From: Simon Riggs To: Heikki Linnakangas Cc: PostgreSQL-development In-Reply-To: <49072021.7010801@enterprisedb.com> References: <49072021.7010801@enterprisedb.com> Content-Type: text/plain Date: Tue, 28 Oct 2008 15:35:49 +0000 Message-Id: <1225208150.3971.191.camel@ebony.2ndQuadrant> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-Server-Quench: 1cb13413-a506-11dd-bc7a-001f29070be2 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFag1bYlpF HGQLW1xEUVh7WmZ/ bgwfZQBDYEtPQQxj TklLQE1QEQdtHhxP Wxd9JHMxcERGe3h1 ZEUsX3lfXwp7JhR8 QUpVFnBXYGdmdWEe BBRFJVBTIR5Kfh4T aFl/U3NfNGdJBC9q VzwTFhsSEA9kHWxo Wg0EMVMUTg4lJRt0 QBZKBzgkGlMIXTRb X-Authentic-SMTP: 61633235383639.kestrel.dmpriest.net.uk:1378/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/1414 X-Sequence-Number: 126431 On Tue, 2008-10-28 at 16:22 +0200, Heikki Linnakangas wrote: > Arbitrarily, if after a > heap insert/update there's less than 20% of free space on the page, > the FSM is updated. Compared to updating it every time, that saves a > lot of overhead, while doing a pretty good job at marking full pages > as full in the FSM. My first thought was to update the FSM if there > isn't enough room on the page for a new tuple of the same size as the > one just > inserted; that would be pretty close to the logic we have during > normal > operation, where the FSM is updated when the tuple that we're about > to > insert doesn't fit on the page. But because we don't know the > fillfactor > during recovery, I don't think we can do reliably. With HOT, we tend to hover around the nearly-full state, so this seems like it will trigger repeatedly. Is it possible that we could put an extra field onto a heap_clean record to show remaining space. We would use it only for VACUUMs, not HOT, just as we do now. Probably good idea to make a list of user cases and say what we do in eahc case. e.g. COPY, other bulk ops, HOT etc.. I wonder if there is merit in having an XLogInsertMulti() which inserts multiple records in a batch as a way of reducing WALInsertLock traffic. It might be possible to piggyback FSM records onto the main heap changes. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support