From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: autovacuum
Date: Mon, 06 Feb 2006 12:37:53 -0600
Message-ID: <1139251073.22740.78.camel@state.g2switchworks.com> (raw)
In-Reply-To: <m3fyn2o5hc.fsf@mobile.int.cbbrowne.com>
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>
<m3fyn2o5hc.fsf@mobile.int.cbbrowne.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: smarlowe@g2switchworks.com, cbbrowne@acm.org
Subject: Re: autovacuum
In-Reply-To: <1139251073.22740.78.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox