Thread: temporarily stop autovacuum

temporarily stop autovacuum

From
Tatsuo Ishii
Date:
Hi,

Is there any way to stop autovacuum temporarily?(other than edit
postgresql.conf and reload it) Pgpool-II does not want autovacuum
running while doing "onlie recovery".
--
Tatsuo Ishii
SRA OSS, Inc. Japan


Re: temporarily stop autovacuum

From
"Joshua D. Drake"
Date:
On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote:
> Hi,
> 
> Is there any way to stop autovacuum temporarily?(other than edit
> postgresql.conf and reload it) Pgpool-II does not want autovacuum
> running while doing "onlie recovery".

It would be a significant hack but you could update pg_autovacuum to set
all relations to false.

Joshua D. Drake


> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> 
-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: temporarily stop autovacuum

From
Tatsuo Ishii
Date:
> > Is there any way to stop autovacuum temporarily?(other than edit
> > postgresql.conf and reload it) Pgpool-II does not want autovacuum
> > running while doing "onlie recovery".
> 
> It would be a significant hack but you could update pg_autovacuum to set
> all relations to false.

Thanks. Maybe it will be easier to talk to the autovacuum launcher
daemon process directly? I will look in to the code.
--
Tatsuo Ishii
SRA OSS, Inc. Japan


Re: temporarily stop autovacuum

From
Alvaro Herrera
Date:
Tatsuo Ishii wrote:
> Hi,
> 
> Is there any way to stop autovacuum temporarily?(other than edit
> postgresql.conf and reload it)

Hmm, no, that's the only way.

I'm not sure that this calls for a change in autovacuum itself; it seems
to be that whatwe really need is the ability to change postgresql.conf
settings from the SQL interface.  This has been discussed at length
elsewhere, and I think we need to bite the bullet eventually.


Re: temporarily stop autovacuum

From
Peter Eisentraut
Date:
Joshua D. Drake wrote:
> On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote:
>> Hi,
>>
>> Is there any way to stop autovacuum temporarily?(other than edit
>> postgresql.conf and reload it) Pgpool-II does not want autovacuum
>> running while doing "onlie recovery".
> 
> It would be a significant hack but you could update pg_autovacuum to set
> all relations to false.

Which will no longer work in 8.4.

More generally, it was pointed out to me that users apparently do 
updates of pg_autovacuum to change settings on a bunch of tables at 
once.  We might get some complaints if we remove that facility.


Re: temporarily stop autovacuum

From
Robert Haas
Date:
On Tue, Feb 10, 2009 at 8:53 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> I'm not sure that this calls for a change in autovacuum itself; it seems
> to be that whatwe really need is the ability to change postgresql.conf
> settings from the SQL interface.  This has been discussed at length
> elsewhere, and I think we need to bite the bullet eventually.

I'd like to take a crack at identifying the bullet that needs to be
bitten here: comments.

People like to use comments to document old settings that they may
once have had, and why they changed them, and we also ship comments
that document the meaning of many of our settings.  IIRC, much of the
last round of this discussion centered on where new settings would be
inserted into the file (which might involve trying to identify the
commented-out version of that setting), whether to comment out the old
line for a particular setting and insert a new line (or just replace
the old line), what to do about comments on the same line as the GUC,
etc.

Any solution that we attempt to engineer this problem is unlikely to
be able to pass the Turing test, and so it's likely to get some cases
"wrong", as judged by the human intelligence of the person who wrote
the comment that got masticated.

If we resign ourselves to the fact that this will not work very well
unless our postgresql.conf file is intended to be read and written
primarily by machines, and only secondarily by humans when necessary
to recover from a bad situation, we can make some progress.

...Robert


Re: temporarily stop autovacuum

From
Alvaro Herrera
Date:
Peter Eisentraut wrote:
> Joshua D. Drake wrote:

>> It would be a significant hack but you could update pg_autovacuum to set
>> all relations to false.
>
> Which will no longer work in 8.4.
>
> More generally, it was pointed out to me that users apparently do  
> updates of pg_autovacuum to change settings on a bunch of tables at  
> once.  We might get some complaints if we remove that facility.

Hmm, argh.  Maybe we do need the rule on a fake pg_autovacuum that
Itagaki-san was proposing.

There's a problem however; for pg_autovacuum you used to need to insert
some -1 values on columns on which you wanted to keep as defaults.  On
the new code you need to skip the value altogether, and a -1 is rejected
with an error.  Not sure how would we translate that.



Re: temporarily stop autovacuum

From
"Joshua D. Drake"
Date:
On Wed, 2009-02-11 at 14:21 -0300, Alvaro Herrera wrote:
> Peter Eisentraut wrote:
> > Joshua D. Drake wrote:
> 
> >> It would be a significant hack but you could update pg_autovacuum to set
> >> all relations to false.
> >
> > Which will no longer work in 8.4.
> >
> > More generally, it was pointed out to me that users apparently do  
> > updates of pg_autovacuum to change settings on a bunch of tables at  
> > once.  We might get some complaints if we remove that facility.

I got plenty of complaints that aren't being fixed :). pg_dump doesn't
even know how to deal with pg_autovacuum, changing scripts to handle
their autovacuum modifications won't take much.

> Hmm, argh.  Maybe we do need the rule on a fake pg_autovacuum that
> Itagaki-san was proposing.
> 

I don't think so. A clean cut is the way to go.

Sincerely,

Joshua D. Drake
-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: temporarily stop autovacuum

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Peter Eisentraut wrote:
>> More generally, it was pointed out to me that users apparently do  
>> updates of pg_autovacuum to change settings on a bunch of tables at  
>> once.  We might get some complaints if we remove that facility.

> Hmm, argh.  Maybe we do need the rule on a fake pg_autovacuum that
> Itagaki-san was proposing.

AFAIR we pointed out from day one that pg_autovacuum was a temporary
API that we were not promising to keep around.  Anybody who was coding
against it with the expectation that they'd not have to change that code
later was willfully ignoring the warning label.

> There's a problem however; for pg_autovacuum you used to need to insert
> some -1 values on columns on which you wanted to keep as defaults.  On
> the new code you need to skip the value altogether, and a -1 is rejected
> with an error.  Not sure how would we translate that.

Maybe use a real table with an ON INSERT trigger that could contain some
actual logic?  But it'd probably still have to be custom-tailored to
whatever application code was inserting things into pg_autovacuum,
so it's not clear there's much point to writing that instead of fixing
the application.
        regards, tom lane


Re: temporarily stop autovacuum

From
Robert Haas
Date:
On Wed, Feb 11, 2009 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Peter Eisentraut wrote:
>>> More generally, it was pointed out to me that users apparently do
>>> updates of pg_autovacuum to change settings on a bunch of tables at
>>> once.  We might get some complaints if we remove that facility.
>
>> Hmm, argh.  Maybe we do need the rule on a fake pg_autovacuum that
>> Itagaki-san was proposing.
>
> AFAIR we pointed out from day one that pg_autovacuum was a temporary
> API that we were not promising to keep around.  Anybody who was coding
> against it with the expectation that they'd not have to change that code
> later was willfully ignoring the warning label.
>
>> There's a problem however; for pg_autovacuum you used to need to insert
>> some -1 values on columns on which you wanted to keep as defaults.  On
>> the new code you need to skip the value altogether, and a -1 is rejected
>> with an error.  Not sure how would we translate that.
>
> Maybe use a real table with an ON INSERT trigger that could contain some
> actual logic?  But it'd probably still have to be custom-tailored to
> whatever application code was inserting things into pg_autovacuum,
> so it's not clear there's much point to writing that instead of fixing
> the application.

In any case it's not difficult to write a script that loops over all
of your tables with ALTER TABLE.  It's probably not as fast as a
single UPDATE statement, but I suspect you'd need to have an enormous
number of tables for that to matter much.

...Robert


Re: temporarily stop autovacuum

From
ITAGAKI Takahiro
Date:
Alvaro Herrera <alvherre@commandprompt.com> wrote:

> I'm not sure that this calls for a change in autovacuum itself; it seems
> to be that whatwe really need is the ability to change postgresql.conf
> settings from the SQL interface.

Sure. 'SET GLOBAL autovacuum = off' is a TODO item.


I have another idea that autovacuum will use 'autovacuum role'
to process tables. We don't need to add syntax because we already
have per-database and per-role settings.

Something like:   ALTER ROLE autovacuum SET autovacuum = off;

We also need to adjust those variable can be set on-the-fly, though.
The current version of postgres doesn't allow to set them.   ERROR:  parameter "..." cannot be changed now

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center




Re: temporarily stop autovacuum

From
Peter Eisentraut
Date:
On Wednesday 11 February 2009 20:10:46 Tom Lane wrote:
> AFAIR we pointed out from day one that pg_autovacuum was a temporary
> API that we were not promising to keep around.  Anybody who was coding
> against it with the expectation that they'd not have to change that code
> later was willfully ignoring the warning label.

Indeed.  I'm just saying, there is now no way to conveniently change the
settings for many tables at once.

This is perhaps the same kind of issue as GRANT SELECT ON ALL TABLES etc. that
people occassionally ask for.  Doing DDL on a group of tables at once.


Re: temporarily stop autovacuum

From
Bernd Helmle
Date:
--On Mittwoch, Februar 11, 2009 13:18:11 -0500 Robert Haas 
<robertmhaas@gmail.com> wrote:

> In any case it's not difficult to write a script that loops over all
> of your tables with ALTER TABLE.  It's probably not as fast as a
> single UPDATE statement, but I suspect you'd need to have an enormous
> number of tables for that to matter much.

Agreed, we often recommend this for all kinds of GRANTs, REVOKEs and so on. 
But while we don't have (yet) any facility to achieve this behavior with 
these commands, for autovacuum, a possible solution exists, and although a 
crude temporarily one, i know people seeing pg_autovacuum as a feature to 
do exactly this kind of maintenance.


--  Thanks
                   Bernd