Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6F4B364FCEE for ; Tue, 28 Oct 2008 09:02:59 -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 22980-08 for ; Tue, 28 Oct 2008 09:02:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.25]) by postgresql.org (Postfix) with ESMTP id B561164FD0F for ; Tue, 28 Oct 2008 09:02:56 -0300 (ADT) Received: by ey-out-2122.google.com with SMTP id 6so1107486eyi.61 for ; Tue, 28 Oct 2008 05:02:54 -0700 (PDT) Received: by 10.210.78.16 with SMTP id a16mr8227985ebb.79.1225195374443; Tue, 28 Oct 2008 05:02:54 -0700 (PDT) Received: from oxford.xeocode.com.enterprisedb.com ([82.16.16.210]) by mx.google.com with ESMTPS id h7sm7877901nfh.4.2008.10.28.05.02.52 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 28 Oct 2008 05:02:53 -0700 (PDT) To: Simon Riggs Cc: pgsql-hackers Subject: Re: VACUUMs and WAL In-Reply-To: <1225191891.3971.137.camel@ebony.2ndQuadrant> (Simon Riggs's message of "Tue\, 28 Oct 2008 11\:04\:51 +0000") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:HACKERS" 47295) References: <1225183785.3971.114.camel@ebony.2ndQuadrant> <87bpx5c7dk.fsf@oxford.xeocode.com> <1225191891.3971.137.camel@ebony.2ndQuadrant> From: Gregory Stark Organization: EnterpriseDB Date: Tue, 28 Oct 2008 12:02:48 +0000 Message-ID: <877i7tc4gn.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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/1386 X-Sequence-Number: 126403 Simon Riggs writes: > On Tue, 2008-10-28 at 10:59 +0000, Gregory Stark wrote: >> To do that he proposed we do: >> >> 1. scan heap doing two things: a) remove any marked tuples if they were marked >> by a previous vacuum which committed and b) prune and mark any tuples we >> find are deletable for a future vacuum to remove. >> >> 2. scan indexes and remove the tuples we marked in 1b. > > It's fairly hard to remove the second heap pass completely. > > I think what I am suggesting is two heap passes, but writing WAL and > dirtying blocks on only one of the passes. How small a patch would it be? I guess you just need to disable all pruning in the first pass and do it in the second patch? I would still rather see Pavan's optimizationo if we can do it cleanly, but if it's not going to happen and this is trivial then sure, we may as well. > > The biggest I/O cost comes from the writes, not the reads, ISTM. It's counter-intuitive but actually it's usually the other way around. Writes can be buffered, re-ordered, and scheduled during otherwise idle time. Reads however are always blocking. However in this situation I think you may be right. Vacuum is doing a sequential scan through the table so if the OS has to interrupt that scan to go do some writes it'll end up having to go back and forth. That would be a *lot* slower than just doing a sequential scan. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!