X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 02C609DC8E0 for ; Mon, 6 Feb 2006 14:37:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16079-04 for ; Mon, 6 Feb 2006 14:37:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id A642F9DC860 for ; Mon, 6 Feb 2006 14:37:53 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Mon, 6 Feb 2006 18:37:53 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 06 Feb 2006 12:37:53 -0600 Subject: Re: autovacuum From: Scott Marlowe To: Christopher Browne Cc: pgsql-admin@postgresql.org In-Reply-To: References: <1137992841.28304.48.camel@localhost.localdomain> <5001.71.40.140.99.1138286468.squirrel@mail.tocr.com> <60bqxqls6m.fsf@dba2.int.libertyrms.com> <20060201212116.GN95850@pervasive.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1139251073.22740.78.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 06 Feb 2006 12:37:53 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.161 required=5 tests=[AWL=0.160, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.161 X-Spam-Level: X-Archive-Number: 200602/62 X-Sequence-Number: 20483 On Wed, 2006-02-01 at 20:32, Christopher Browne wrote: > > This seems maybe a bit overkill to me. I think what would be more useful > > is if autovacuum could execute more than one vacuum at a time, and you > > could specify tables that are high priority (or possibly just say that > > all tables with less than X live tuples in them are high priority). That > > way a longer-running vacuum on a large table wouldn't prevent more > > vacuum-sensative tables (such as queues) from being vacuumed frequently > > enough. > > Actually, I can think of a case for much the opposite, namely to want > to concurrently vacuum some LARGE tables... > > Suppose you have 2 rather big tables that get updates on similar > schedules such that both will have a lot of dead tuples at similar > times. > > And suppose both of these tables are Way Large, so that they take > six hours to vacuum. > > I could argue for kicking off vacuums on both, at the same moment; > they'll both be occupying transactions for 1/4 of a day, and, with > possibly related patterns of updates, doing them one after the other > *wouldn't* forcibly get you more tuples cleaned than doing them > concurrently. > > I'm not sure that's a case to push for, either, as something > pg_autovacuum is smart enough to handle; I'm just putting out some > ideas that got enough internal discussion to suggest they were > interesting enough to let others consider... This could be a big win on databases where those two tables were on different table spaces, since vacuum now wouldn't be fighting for the same thin I/O stream twice. If the autovacuum daemon scheduled vacuums so that each tablespace had a list of vacuums to run, but then ran those sets in parallel (i.e. tablespace1 has one single vacuum running though a list while tablespace2 has its own single vacuum.) Maybe even a setting that told it the max number to run in parallel for each tablespace. After all, a tablespace running on 30 hard drives in a RAID-10 could handly several concurrent vacuums, while another tablespace running on a single drive would be well limited to one vacuum at a time.