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 D97653A3F27 for ; Sun, 23 Jan 2005 20:21:33 +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 35045-03 for ; Sun, 23 Jan 2005 20:21:27 +0000 (GMT) Received: from cmailg2.svr.pol.co.uk (cmailg2.svr.pol.co.uk [195.92.195.172]) by svr1.postgresql.org (Postfix) with ESMTP id 13EA53A4CF1 for ; Sun, 23 Jan 2005 20:21:23 +0000 (GMT) Received: from modem-624.llama.dialup.pol.co.uk ([217.135.178.112] helo=192.168.0.102) by cmailg2.svr.pol.co.uk with esmtp (Exim 4.14) id 1CsoEY-00043M-6G; Sun, 23 Jan 2005 20:21:18 +0000 Subject: Re: PostgreSQL clustering VS MySQL clustering From: Simon Riggs To: Tatsuo Ishii Cc: Tom Lane , marty@outputservices.com, herve@elma.fr, pgsql-performance@postgresql.org In-Reply-To: <25335.1106428223@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> Content-Type: text/plain Organization: 2nd Quadrant Date: Sun, 23 Jan 2005 20:15:52 +0000 Message-Id: <1106511352.31592.112.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/422 X-Sequence-Number: 10112 On Sat, 2005-01-22 at 16:10 -0500, Tom Lane wrote: > Tatsuo Ishii writes: > > Probably VACUUM works well for small to medium size tables, but not > > for huge ones. I'm considering about to implement "on the spot > > salvaging dead tuples". > > That's impossible on its face, except for the special case where the > same transaction inserts and deletes a tuple. In all other cases, the > transaction deleting a tuple cannot know whether it will commit. Perhaps Tatsuo has an idea... As Tom says, if you have only a single row version and then you update that row to create a second version, then we must not remove the first version, since it is effectively the Undo copy. However, if there were already 2+ row versions, then as Tatsuo suggests, it might be possible to use on the spot salvaging of dead tuples. It might be worth checking the Xid of the earlier row version(s), to see if they are now expired and could be removed immediately. However, if you had a high number of concurrent updaters, this extra effort would not be that useful, since the other row versions might still be transaction-in-progress versions. That would mean implementing this idea would be useful often, but not in the case of repeatedly updated rows. 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. That way we wouldn't do the row every time, just when it becomes a problem. I'm suggesting putting a call to vacuum_page() into heap_update(), immediately before any call to RelationGetBufferForTuple(). We already know that page splitting is an expensive operation, so doing some work to try to avoid that could frequently pay off. This would be isolated to updating. This wouldn't remove the need for vacuuming, but it would act to prevent severe performance degradation caused by frequent re-updating. What do you think? -- Best Regards, Simon Riggs