From: Matthew O'Connor <matthew@zeut.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim C. Nasby <jim@nasby.net>
Cc: Hitoshi Harada <hitoshi_harada@forcia.com>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] smartvacuum() instead of autovacuum
Date: Mon, 23 Oct 2006 18:39:23 -0400
Message-ID: <453D449B.50404@zeut.net> (raw)
In-Reply-To: <27345.1161630483@sss.pgh.pa.us>
References: <10306.1161569392@sss.pgh.pa.us>
<200610230220.k9N2KUeZ051790@mbox31.po.2iij.net>
<20061023183628.GR26892@nasby.net>
<27345.1161630483@sss.pgh.pa.us>
Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
>> The only case I can think of where autovac might not work as well as
>> smartvacuum would be if you had a lot of databases in the cluster, since
>> autovacuum will only vacuum one database at a time.
>
> It's conceivable that it'd make sense to allow multiple autovac
> processes running in parallel. (The infrastructure part of this is easy
> enough, the hard part is keeping them from all deciding to vacuum the
> same table.)
>
> One reason we have not done that already is the thought that multiple
> vacuum processes would suck too much I/O to be reasonable. Now you
> could dial back their resource demands with the cost-delay settings,
> but it's not clear that ten autovacs running at one-tenth speed are
> better than one autovac using all the cycles you can spare. Usually
> I think it's best if a vacuum transaction finishes as fast as it can.
I think this is one of the reasons table specific delay settings were
designed in from the beginning. I think the main use cases for multiple
vacuums at once are:
1) Vacuum per table space assuming each table space is on a different
drive with it's own I/O.
2) the frequently updated table that can't wait to be vacuumed while a
large table is being vacuumed. In this case if you set a system default
delay setting and set a more aggressive table specific delay setting for
your hot spot tables then multiple vacuums become a clear win. This is
an important case that I hope we handle soon. At this point it's one of
the main failings of the current autovacuum system.
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-hackers@postgresql.org
Cc: matthew@zeut.net, tgl@sss.pgh.pa.us, jim@nasby.net, hitoshi_harada@forcia.com, peter_e@gmx.net
Subject: Re: [PATCHES] smartvacuum() instead of autovacuum
In-Reply-To: <453D449B.50404@zeut.net>
* 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