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 788C53A5285 for ; Mon, 24 Jan 2005 00:21:58 +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 00154-04 for ; Mon, 24 Jan 2005 00:21:51 +0000 (GMT) Received: from mail.esphion.com (mail.esphion.com [202.6.75.178]) by svr1.postgresql.org (Postfix) with ESMTP id 3ACF03A5244 for ; Mon, 24 Jan 2005 00:21:52 +0000 (GMT) Received: (qmail 29379 invoked from network); 24 Jan 2005 00:20:21 -0000 Received: from unknown (HELO conker.?none?) (10.0.1.137) by mail.esphion.com with SMTP; 24 Jan 2005 00:20:21 -0000 Received: (nullmailer pid 11695 invoked by uid 10001); Mon, 24 Jan 2005 00:21:50 -0000 Date: Mon, 24 Jan 2005 13:21:50 +1300 From: Guy Thornley To: Tom Lane Cc: Simon Riggs , Tatsuo Ishii , marty@outputservices.com, herve@elma.fr, pgsql-performance@postgresql.org Subject: Re: PostgreSQL clustering VS MySQL clustering Message-ID: <20050124002150.GA19730@conker.esphion.com> Reply-To: Guy Thornley 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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6346.1106512803@sss.pgh.pa.us> User-Agent: Mutt/1.5.5.1+cvs20040105i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/429 X-Sequence-Number: 10119 > 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. As a rather naive user, I'd consider modifying the FSM so that it has pages with 'possibly freeable' space on them, as well as those with free space. This way when the pages of actually free space is depleted, the list of 'possibly freeable' pages could be vacuumed (as a batch for that relation) then placed on the actually-free list like vacuum currently does Since there is concern about critical path performance, there could be an extra backend process that would wake up perodically (or on a signal) and vacuum the pages, so theyre not processed inline with some transaction. Then grabbing a page with free space is the same as it is currently. Actually I was hoping to find some time to investigate this myself, but my employer is keeping me busy with other tasks ;/. Our particular data management problems could be mitigated much better with a data partitioning approach, anyway. On another note, is anybody investigating backing up the FSM with disk files so when the FSM size exceeds memory allocated, the appropriate data is swapped to disk? At least since 7.4 you no longer need a VACUUM when postgres starts, to learn about free space ;) - Guy Thornley