X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9FE7F3A53BF for ; Mon, 24 Jan 2005 08:47:19 +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 66983-10 for ; Mon, 24 Jan 2005 08:47:09 +0000 (GMT) Received: from cmailg4.svr.pol.co.uk (cmailg4.svr.pol.co.uk [195.92.195.174]) by svr1.postgresql.org (Postfix) with ESMTP id 7BA963A5351 for ; Mon, 24 Jan 2005 08:47:09 +0000 (GMT) Received: from modem-42.leopard.dialup.pol.co.uk ([217.135.144.42] helo=192.168.0.102) by cmailg4.svr.pol.co.uk with esmtp (Exim 4.41) id 1CszsG-00062Y-RC; Mon, 24 Jan 2005 08:47:05 +0000 Subject: Faster and more frequent VACUUM (was PostgreSQL clustering VS MySQL clustering) From: Simon Riggs To: Tom Lane Cc: Tatsuo Ishii , marty@outputservices.com, herve@elma.fr, pgsql-performance@postgresql.org In-Reply-To: <6346.1106512803@sss.pgh.pa.us> References: <41F144FE.30902@outputservices.com> <41F14758.4030602@outputservices.com> <20050122.121300.41631131.t-ishii@sra.co.jp> <25335.1106428223@sss.pgh.pa.us> <1106511352.31592.112.camel@localhost.localdomain> <6346.1106512803@sss.pgh.pa.us> Content-Type: text/plain Organization: 2nd Quadrant Date: Mon, 24 Jan 2005 08:41:37 +0000 Message-Id: <1106556097.31592.139.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.04 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/433 X-Sequence-Number: 10123 On Sun, 2005-01-23 at 15:40 -0500, Tom Lane wrote: > Simon Riggs writes: > > Changing the idea slightly might be better: if a row update would cause > > a block split, then if there is more than one row version then we vacuum > > the whole block first, then re-attempt the update. > > "Block split"? I think you are confusing tables with indexes. Terminologically loose, as ever. :( I meant both tables and indexes and was referring to the part of the algorithm that is entered when we have a block-full situation. > Chasing down prior versions of the same row is not very practical > anyway, since there is no direct way to find them. > > One possibility is, if you tried to insert a row on a given page but > there's not room, to look through the other rows on the same page to see > if any are deletable (xmax below the GlobalXmin event horizon). This > strikes me as a fairly expensive operation though, especially when you > take into account the need to get rid of their index entries first. Thats what I was suggesting, vac the whole page, not just those rows. Doing it immediately greatly increases the chance that the index blocks would be in cache also. > Moreover, the check would often be unproductive. > The real issue with any such scheme is that you are putting maintenance > costs into the critical paths of foreground processes that are executing > user queries. I think that one of the primary advantages of the > Postgres storage design is that we keep that work outside the critical > path and delegate it to maintenance processes that can run in the > background. We shouldn't lightly toss away that advantage. Completely agree. ...which is why I was trying to find a place for such an operation in-front-of another expensive operation which is also currently on the critical path. That way there might be benefit rather than just additional overhead. > There was some discussion in Toronto this week about storing bitmaps > that would tell VACUUM whether or not there was any need to visit > individual pages of each table. Getting rid of useless scans through > not-recently-changed areas of large tables would make for a significant > reduction in the cost of VACUUM. ISTM there are two issues here, which are only somewhat related: - speed of VACUUM on large tables - ability to run VACUUM very frequently on very frequently updated tables The needs-maintenance bitmap idea hits both, whilst the on-the-spot idea only hits the second one, even if it does it +/- better. Gut feel says we would implement only one idea...so... On balance that indicates the need-maintenance bitmap is a better idea, and one for which we already have existing code. A few questions... - wouldn't we need a bitmap per relation? - wouldn't all the extra bitmaps need to be cached in shared_buffers, which could use up a good proportion of buffer cache space - maybe we should use a smaller block size and a different cache for it - how would we update the bitmap without creating a new LWlock that needs to be acquired for every block write and so reducing scalability? - would this be implemented as an option for each table, so that we could avoid the implementation overhead? (Or perhaps don't have a bitmap if table is less than 16 blocks?) -- Best Regards, Simon Riggs