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 C96BC3A4F60 for ; Sat, 22 Jan 2005 19:00:34 +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 81673-09 for ; Sat, 22 Jan 2005 19:00:27 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id B72443A4DE2 for ; Sat, 22 Jan 2005 19:00:27 +0000 (GMT) Received: from dyn-68-143.tor.dsl.tht.net (dyn-68-143.tor.dsl.tht.net [134.22.68.143]) by tht.net (Postfix) with ESMTP id BA6D376A28; Sat, 22 Jan 2005 14:00:28 -0500 (EST) Subject: Re: PostgreSQL clustering VS MySQL clustering From: Rod Taylor To: Bruno Wolff III Cc: Tatsuo Ishii , marty@outputservices.com, herve@elma.fr, Postgresql Performance In-Reply-To: <20050122184124.GA21889@wolff.to> References: <41F144FE.30902@outputservices.com> <41F14758.4030602@outputservices.com> <20050122.121300.41631131.t-ishii@sra.co.jp> <20050122184124.GA21889@wolff.to> Content-Type: text/plain Date: Sat, 22 Jan 2005 14:00:40 -0500 Message-Id: <1106420440.35299.763.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.0.3 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/412 X-Sequence-Number: 10102 On Sat, 2005-01-22 at 12:41 -0600, Bruno Wolff III wrote: > On Sat, Jan 22, 2005 at 12:13:00 +0900, > Tatsuo Ishii wrote: > > > > 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". > > You are probably vacuuming too often. You want to wait until a significant > fraction of a large table is dead tuples before doing a vacuum. If you are > scanning a large table and only marking a few tuples as deleted, you aren't > getting much bang for your buck. The big problem occurs when you have a small set of hot tuples within a large table. In the time it takes to vacuum a table with 200M tuples one can update a small subset of that table many many times. Some special purpose vacuum which can target hot spots would be great, but I've always assumed this would come in the form of table partitioning and the ability to vacuum different partitions independently of each-other. --