Thread: auto vacuuming

auto vacuuming

From
"Gourish Singbal"
Date:
 
we are using postgresql 8.1.3 and wanted to enable autovacuuming for only one of the many databases on the same postgresql cluster.
pg_autovacuum seems to allow to ignore only a particular table and not the database since column vacrelid is OID from pg_class table  Are there any suggestions to accomplish this tasks?.
 
 


--
Best,
Gourish Singbal

Re: auto vacuuming

From
"Jim C. Nasby"
Date:
On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote:
> we are using postgresql 8.1.3 and wanted to enable autovacuuming for only
> one of the many databases on the same postgresql cluster.
> pg_autovacuum seems to allow to ignore only a particular table and not the
> database since column vacrelid is OID from pg_class table  Are there any
> suggestions to accomplish this tasks?.

Currently there is no way to exclude entire databases from autovacuum.

I'm curious, why do you want to do this anyway?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: auto vacuuming

From
Alvaro Herrera
Date:
Jim C. Nasby wrote:
> On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote:
> > we are using postgresql 8.1.3 and wanted to enable autovacuuming for only
> > one of the many databases on the same postgresql cluster.
> > pg_autovacuum seems to allow to ignore only a particular table and not the
> > database since column vacrelid is OID from pg_class table  Are there any
> > suggestions to accomplish this tasks?.
>
> Currently there is no way to exclude entire databases from autovacuum.

IIRC in one of the original patches I had added a way to do it.  It was
rejected however; it was argued that there was another mechanism to
disable it.  I don't remember what it was though.  The only possibility
that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't
see why would it work.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: auto vacuuming

From
"Matthew T. O'Connor"
Date:
Alvaro Herrera wrote:
> Jim C. Nasby wrote:
>> On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote:
>>> we are using postgresql 8.1.3 and wanted to enable autovacuuming for only
>>> one of the many databases on the same postgresql cluster.
>>> pg_autovacuum seems to allow to ignore only a particular table and not the
>>> database since column vacrelid is OID from pg_class table  Are there any
>>> suggestions to accomplish this tasks?.
>> Currently there is no way to exclude entire databases from autovacuum.
>
> IIRC in one of the original patches I had added a way to do it.  It was
> rejected however; it was argued that there was another mechanism to
> disable it.  I don't remember what it was though.  The only possibility
> that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't
> see why would it work.


I think the closest approximation of disabling autovacuum on a per
database basis is to connect to the database in question and perform:

update pg_autovacuum set enabled = 'false';

This will prevent autovacuum from vacuuming or analyzing any of the
tables in the database, but will still check for XID wraparound.


Matt

Re: auto vacuuming

From
"Jim C. Nasby"
Date:
On Fri, Mar 31, 2006 at 04:36:10PM -0500, Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Jim C. Nasby wrote:
> >>On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote:
> >>>we are using postgresql 8.1.3 and wanted to enable autovacuuming for only
> >>>one of the many databases on the same postgresql cluster.
> >>>pg_autovacuum seems to allow to ignore only a particular table and not
> >>>the
> >>>database since column vacrelid is OID from pg_class table  Are there any
> >>>suggestions to accomplish this tasks?.
> >>Currently there is no way to exclude entire databases from autovacuum.
> >
> >IIRC in one of the original patches I had added a way to do it.  It was
> >rejected however; it was argued that there was another mechanism to
> >disable it.  I don't remember what it was though.  The only possibility
> >that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't
> >see why would it work.
>
>
> I think the closest approximation of disabling autovacuum on a per
> database basis is to connect to the database in question and perform:
>
> update pg_autovacuum set enabled = 'false';
>
> This will prevent autovacuum from vacuuming or analyzing any of the
> tables in the database, but will still check for XID wraparound.

Problem with that is any time you add a table you'd need to re-run that
command. Wouldn't be so bad if we had DDL triggers, but...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: auto vacuuming

From
"Matthew T. O'Connor"
Date:
>> I think the closest approximation of disabling autovacuum on a per
>> database basis is to connect to the database in question and perform:
>>
>> update pg_autovacuum set enabled = 'false';
>>
>> This will prevent autovacuum from vacuuming or analyzing any of the
>> tables in the database, but will still check for XID wraparound.
>>
>
> Problem with that is any time you add a table you'd need to re-run that
> command. Wouldn't be so bad if we had DDL triggers, but...
>

Probably a dumb comment, but can you set a default value for the enabled
column?


Re: auto vacuuming

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Jim C. Nasby wrote:
>> Currently there is no way to exclude entire databases from autovacuum.

> IIRC in one of the original patches I had added a way to do it.  It was
> rejected however; it was argued that there was another mechanism to
> disable it.  I don't remember what it was though.

I can't think of one either.  Real question is, why would you want to?
Disabled autovac would for instance be unable to protect you against XID
wraparound, which IMHO is one of the major advantages of running it at
all.

The handwriting on the wall says that autovac will soon be on by
default, and perhaps become not-disablable some day after that
(like the second or third time we hear from someone who's lost
their data to XID wraparound after disabling it).

So if there's a really convincing use-case for locking autovac out
of specific databases, we'd better hear it soon.

            regards, tom lane

Re: auto vacuuming

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I think the closest approximation of disabling autovacuum on a per
> database basis is to connect to the database in question and perform:
> update pg_autovacuum set enabled = 'false';

Not really gonna help unless you insert a row into pg_autovacuum for
each table in the database.

As I just commented in another reply, I don't actually believe in the
value of disabling autovac entirely --- it should at least be able to
fire when you are risking XID wraparound.  What could make sense is to
push the thresholds up to very large values, such that autovac won't
fire until you've forgotten manual vacuums for a very long time.
And that you can already do on a per-database basis, using ALTER
DATABASE SET.  (Or at least, it *should* work to do that; if the autovac
process fails to absorb per-db values for its GUC variables, then we
ought to fix it.  I'm too lazy to test it right now...)

            regards, tom lane

Re: auto vacuuming

From
"Joshua D. Drake"
Date:
>
> The handwriting on the wall says that autovac will soon be on by
> default, and perhaps become not-disablable some day after that
> (like the second or third time we hear from someone who's lost
> their data to XID wraparound after disabling it).
>
> So if there's a really convincing use-case for locking autovac out
> of specific databases, we'd better hear it soon.

Sure.. a database that has over 2500 connections per a directly
connected Java rich client... I don't want autovac running on it from
8-8 but after that I will run my own vacuum process.

I am smart enough to manage my data. If I want autovac I will turn it on.

Joshua D. Drake


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/



Re: auto vacuuming

From
Alvaro Herrera
Date:
Tom Lane wrote:

> The handwriting on the wall says that autovac will soon be on by
> default, and perhaps become not-disablable some day after that
> (like the second or third time we hear from someone who's lost
> their data to XID wraparound after disabling it).

I don't think we will see much people losing data to Xid wraparound
anymore, now that the server refuses to work when the wraparound is
imminent.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: auto vacuuming

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> The handwriting on the wall says that autovac will soon be on by
>> default, and perhaps become not-disablable some day after that
>> (like the second or third time we hear from someone who's lost
>> their data to XID wraparound after disabling it).

> I don't think we will see much people losing data to Xid wraparound
> anymore, now that the server refuses to work when the wraparound is
> imminent.

Well, how about the second or third time we get sued by someone whose
"must be up 24x7" database shuts down for lack of proper vacuuming?
I do think autovac is the wave of the future.  The only reason it's
disablable now is that we don't think we've got all the bugs out.

If you read the old Berkeley Postgres papers, you'll see that a
"vacuum daemon" was always part of the system's basic design.  (Hey
Elein, or anyone else who was there then --- was there ever a working
vacuum daemon, or was it just on paper?)

            regards, tom lane

Re: auto vacuuming

From
Jim Nasby
Date:
On Mar 31, 2006, at 5:45 PM, Matthew T. O'Connor wrote:
>>> I think the closest approximation of disabling autovacuum on a
>>> per database basis is to connect to the database in question and
>>> perform:
>>>
>>> update pg_autovacuum set enabled = 'false';
>>>
>>> This will prevent autovacuum from vacuuming or analyzing any of
>>> the tables in the database, but will still check for XID wraparound.
>>>
>>
>> Problem with that is any time you add a table you'd need to re-run
>> that
>> command. Wouldn't be so bad if we had DDL triggers, but...
>>
>
> Probably a dumb comment, but can you set a default value for the
> enabled column?

As Tom pointed out, that wouldn't help because rows aren't
automatically added to pg_autovacuum.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



Re: auto vacuuming

From
Jim Nasby
Date:
On Mar 31, 2006, at 9:29 PM, Tom Lane wrote:

> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>> I think the closest approximation of disabling autovacuum on a per
>> database basis is to connect to the database in question and perform:
>> update pg_autovacuum set enabled = 'false';
>
> Not really gonna help unless you insert a row into pg_autovacuum for
> each table in the database.

True.

> As I just commented in another reply, I don't actually believe in the
> value of disabling autovac entirely --- it should at least be able to
> fire when you are risking XID wraparound.  What could make sense is to
> push the thresholds up to very large values, such that autovac won't
> fire until you've forgotten manual vacuums for a very long time.
> And that you can already do on a per-database basis, using ALTER
> DATABASE SET.  (Or at least, it *should* work to do that; if the
> autovac
> process fails to absorb per-db values for its GUC variables, then we
> ought to fix it.  I'm too lazy to test it right now...)

The problem with that is unless I missed a change in 8.1, autovac
knows absolutely nothing about when manual vacuums have been run. To
do that I'm pretty sure we'd need a catalog table that captured the
statistic counts on each table when vacuum ran (and ideally an XID
and a timestamp, too). If we end up with some kind of dirty page
bitmap that might remove the need for that.

Even if tweaking the thresholds did work you still can't do it at a
per-database level. It doesn't seem unreasonable to support different
autovac settings at the database level.
--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"




--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



Re: auto vacuuming

From
John DeSoi
Date:
On Apr 2, 2006, at 11:39 PM, Tom Lane wrote:

>> I don't think we will see much people losing data to Xid wraparound
>> anymore, now that the server refuses to work when the wraparound is
>> imminent.
>
> Well, how about the second or third time we get sued by someone whose
> "must be up 24x7" database shuts down for lack of proper vacuuming?
> I do think autovac is the wave of the future.  The only reason it's
> disablable now is that we don't think we've got all the bugs out.

Hopefully the could not sue if it is on by default but they turned it
off. But who knows :).

My reason for wanting to turn it off is that every time it would run
the fans on my development system would rev up. Very annoying in a
quiet environment where you are working on throw away tests that
don't need to eat CPU for vacuuming.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: auto vacuuming

From
Tom Lane
Date:
Jim Nasby <jnasby@pervasive.com> writes:
> The problem with that is unless I missed a change in 8.1, autovac
> knows absolutely nothing about when manual vacuums have been run.

It knows perfectly well about full-database vacuums (because those
set pg_database.datvacuumxid), and by the end of the week or so it'll
know about single-table vacuums too:
http://archives.postgresql.org/pgsql-patches/2006-04/msg00009.php

            regards, tom lane

Re: auto vacuuming

From
Tom Lane
Date:
John DeSoi <desoi@pgedit.com> writes:
> My reason for wanting to turn it off is that every time it would run
> the fans on my development system would rev up.

Well, I have an equally lame reason for wanting to not run autovac:
it'll interfere with profiling because the autovac daemon will drop a
gmon.out file once a minute or so, and maybe overwrite a profiling run's
results before you can print them out.

But AFAICS both of these wishes can be served pretty well just by
increasing autovacuum_naptime to some large value, once a day maybe.
They don't strike me as compelling reasons why autovac should be turned
off entirely.

            regards, tom lane