Thread: AutoVacuum on demand?

I would like a way to run the autovacuum daemon on demand periodically.
   Every night at 2 AM, for example.

Anybody know if this is possible?  If not, it's a feature request :-)

-Glen

On Thu, 2006-11-09 at 17:00 -0800, Glen Parker wrote:
> I would like a way to run the autovacuum daemon on demand periodically.
>    Every night at 2 AM, for example.
>
> Anybody know if this is possible?  If not, it's a feature request :-)

use vacuumdb and cron.

Joshua D. Drake


>
> -Glen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
--

      === 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/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> On Thu, 2006-11-09 at 17:00 -0800, Glen Parker wrote:
>> I would like a way to run the autovacuum daemon on demand periodically.
>>    Every night at 2 AM, for example.
>>
>> Anybody know if this is possible?  If not, it's a feature request :-)
>
> use vacuumdb and cron.

Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.

-Glen

> Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.

Just curious, what is auto_vacuum going to give that vacuumdb wont?

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
>> Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.
>
> Just curious, what is auto_vacuum going to give that vacuumdb wont?



Hmm, I thought this would be obvious.  Vacuumdb just issues "VACUUM"
commands.

I would like the get the functionality if autovacuum so that I don't
waste cycles on tables that don't really need vacuuming.  However, we've
had bad luck letting autovacuum run on its own terms, so we've reverted
to the old nightly vacuums.

Since our nightly window of relative inactivity continues to shrink, I'd
like to vacuum/analyze only the tables that autovacuum thinks need it,
all at once, and only when I tell it to (via cron).

Make more sense?

-Glen

> Richard Broersma Jr wrote:
> >> Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.
> > Just curious, what is auto_vacuum going to give that vacuumdb wont?
> Hmm, I thought this would be obvious.  Vacuumdb just issues "VACUUM"
> commands.
> I would like the get the functionality if autovacuum so that I don't
> waste cycles on tables that don't really need vacuuming.  However, we've
> had bad luck letting autovacuum run on its own terms, so we've reverted
> to the old nightly vacuums.
> Since our nightly window of relative inactivity continues to shrink, I'd
> like to vacuum/analyze only the tables that autovacuum thinks need it,
> all at once, and only when I tell it to (via cron).
> Make more sense?

Yes that make sense.  I don't know if this helps, but I remember reading a thread on one of the
lists that discussed adding functionality to auto_vacuum to allow it to ignore specified tables.
I can't remember if this is a newly added feature or if it is slated for 8.2.

Regards,

Richard Broersma Jr.

> Since our nightly window of relative inactivity continues to shrink, I'd
> like to vacuum/analyze only the tables that autovacuum thinks need it,
> all at once, and only when I tell it to (via cron).

I thought as you did and posted a thread, where I proposed a C function
smartvacuum() which you can call to vacuum with the same criteria as
autovacuum on demand.

http://archives.postgresql.org/pgsql-patches/2006-10/msg00131.php

But that was hardly accepted

http://archives.postgresql.org/pgsql-hackers/2006-10/msg01190.php

and the discussion are gone over there:

http://archives.postgresql.org/pgsql-hackers/2006-10/msg01225.php

There're some cases where the whole database is not needed to be vacuumed,
right?
Anyway, though smartvacuum() was not implemented as I could find another
way, you maight be able to implement it by yourself.

Or, any way to catch row level statistic from SQL or pgsql function should
be prepared on future version??

Regards,
Hitoshi Harada

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Glen Parker
> Sent: Friday, November 10, 2006 11:05 AM
> To: Postgres General
> Subject: Re: [GENERAL] AutoVacuum on demand?
>
> Richard Broersma Jr wrote:
> >> Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.
> >
> > Just curious, what is auto_vacuum going to give that vacuumdb wont?
>
>
>
> Hmm, I thought this would be obvious.  Vacuumdb just issues "VACUUM"
> commands.
>
> I would like the get the functionality if autovacuum so that I don't
> waste cycles on tables that don't really need vacuuming.  However, we've
> had bad luck letting autovacuum run on its own terms, so we've reverted
> to the old nightly vacuums.
>
> Since our nightly window of relative inactivity continues to shrink, I'd
> like to vacuum/analyze only the tables that autovacuum thinks need it,
> all at once, and only when I tell it to (via cron).
>
> Make more sense?
>
> -Glen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

> Hmm, I thought this would be obvious.  Vacuumdb just issues "VACUUM"
> commands.
>
> I would like the get the functionality if autovacuum so that I don't
> waste cycles on tables that don't really need vacuuming.  However, we've
> had bad luck letting autovacuum run on its own terms, so we've reverted
> to the old nightly vacuums.
>
> Since our nightly window of relative inactivity continues to shrink, I'd
> like to vacuum/analyze only the tables that autovacuum thinks need it,
> all at once, and only when I tell it to (via cron).
>
> Make more sense?

If you could instruct auto_vacuum not to vacuum certain tables would that accomplish what you
wanted in place of having auto_vacuum enable/dis-abled from a cron?

http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM
Besides the base threshold values and scale factors, there are three more parameters that can be
set for each table in pg_autovacuum. The first, pg_autovacuum.enabled, can be set to false to
instruct the autovacuum daemon to skip that particular table entirely. In this case autovacuum
will only touch the table when it vacuums the entire database to prevent transaction ID
wraparound. The other two parameters, the vacuum cost delay (pg_autovacuum.vac_cost_delay) and the
vacuum cost limit (pg_autovacuum.vac_cost_limit), are used to set table-specific values for the
Cost-Based Vacuum Delay feature.

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
> > Richard Broersma Jr wrote:
> > >> Cron yes, vacuumdb no.  I would like to AUTO vacuum periodically.
> > > Just curious, what is auto_vacuum going to give that vacuumdb wont?
> > Hmm, I thought this would be obvious.  Vacuumdb just issues "VACUUM"
> > commands.
> > I would like the get the functionality if autovacuum so that I don't
> > waste cycles on tables that don't really need vacuuming.  However, we've
> > had bad luck letting autovacuum run on its own terms, so we've reverted
> > to the old nightly vacuums.
> > Since our nightly window of relative inactivity continues to shrink, I'd
> > like to vacuum/analyze only the tables that autovacuum thinks need it,
> > all at once, and only when I tell it to (via cron).
> > Make more sense?
>
> Yes that make sense.  I don't know if this helps, but I remember
> reading a thread on one of the lists that discussed adding
> functionality to auto_vacuum to allow it to ignore specified tables.
> I can't remember if this is a newly added feature or if it is slated
> for 8.2.

It's present in 8.1.  Just set "enabled" to false on pg_autovacuum for
the given table.  (If there's no tuple, insert one).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Glen Parker wrote:
> I would like a way to run the autovacuum daemon on demand
> periodically.   Every night at 2 AM, for example.
>
> Anybody know if this is possible?  If not, it's a feature request :-)

Autovacuum can be enabled / disabled on the fly using the GUC settings.
Perhaps you can write a cron script that runs at 2AM that enables
autovac, and at 6AM (just a guess) disables it in the same fashion.  I
think that should work.

Would be curious to hear your results.

Matt

Matthew T. O'Connor wrote:
> Glen Parker wrote:
>> I would like a way to run the autovacuum daemon on demand
>> periodically.   Every night at 2 AM, for example.
>>
>> Anybody know if this is possible?  If not, it's a feature request :-)
>
> Autovacuum can be enabled / disabled on the fly using the GUC settings.
> Perhaps you can write a cron script that runs at 2AM that enables
> autovac, and at 6AM (just a guess) disables it in the same fashion.  I
> think that should work.
>
> Would be curious to hear your results.

I thought it sounded like a good idea, but then...

SET autovacuum = on;
ERROR:  parameter "autovacuum" cannot be changed now

So then I thought maybe I could kind of fool it with...

SET autovacuum_vacuum_threshold = 99999999;
ERROR:  parameter "autovacuum_vacuum_threshold" cannot be changed now

Oh well.

-Glen

Glen Parker wrote:
> Matthew T. O'Connor wrote:
> >Glen Parker wrote:
> >>I would like a way to run the autovacuum daemon on demand
> >>periodically.   Every night at 2 AM, for example.
> >>
> >>Anybody know if this is possible?  If not, it's a feature request :-)
> >
> >Autovacuum can be enabled / disabled on the fly using the GUC settings.
> >Perhaps you can write a cron script that runs at 2AM that enables
> >autovac, and at 6AM (just a guess) disables it in the same fashion.  I
> >think that should work.
> >
> >Would be curious to hear your results.
>
> I thought it sounded like a good idea, but then...
>
> SET autovacuum = on;
> ERROR:  parameter "autovacuum" cannot be changed now

I think the idea is to edit the postgresql.conf file on the fly and send
a SIGHUP to the postmaster.  I haven't ever heard of anyone doing that,
but I don't see any reason why it wouldn't work.

I'm not sure if the version you are running supports "include"
directives in postgresql.conf (heck, I'm not even sure if it's possible
to do in 8.2); if that's the case, then it's considerably easier.

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

On Nov 13, 2006, at 1:05 PM, Glen Parker wrote:

> Matthew T. O'Connor wrote:
>> Glen Parker wrote:
>>> I would like a way to run the autovacuum daemon on demand
>>> periodically.   Every night at 2 AM, for example.
>>>
>>> Anybody know if this is possible?  If not, it's a feature
>>> request :-)
>> Autovacuum can be enabled / disabled on the fly using the GUC
>> settings.  Perhaps you can write a cron script that runs at 2AM
>> that enables autovac, and at 6AM (just a guess) disables it in the
>> same fashion.  I think that should work.
>> Would be curious to hear your results.
>
> I thought it sounded like a good idea, but then...
>
> SET autovacuum = on;
> ERROR:  parameter "autovacuum" cannot be changed now

I've turned it on and off in the postgresql.conf several times
(followed by a postmaster HUP) and it worked as expected. I think
this is a global setting that cannot be toggled in a particular session.

-Casey

[snip]
> I think the idea is to edit the postgresql.conf file on the fly and send
> a SIGHUP to the postmaster.  I haven't ever heard of anyone doing that,
> but I don't see any reason why it wouldn't work.

It works, I did it for a while with the statement_timeout to change it
globally over night when admin tasks were running. For the
statement_timeout thing it wasn't optimal however, finally I had to
ditch the thing... but it can be done.

See below my shell script attached, it did work for me, YMMV.

Cheers,
Csaba.


#!/bin/sh

CONF_FILE=/var/lib/postgres/data-8_1_3p/postgresql.conf
PG_CTL=/usr/local/postgres813p/bin/pg_ctl
PG_DIR=/var/lib/postgres/data-8_1_3p

TIMEOUT=0
if [ -n "$1" ]; then
  TIMEOUT=$1
fi

cat $CONF_FILE | sed "s/^\(statement_timeout =
\)[0123456789]\+/\1$TIMEOUT/" > $CONF_FILE.new
mv $CONF_FILE.new $CONF_FILE
$PG_CTL -D $PG_DIR reload

Csaba Nagy wrote:
> [snip]
>
>> I think the idea is to edit the postgresql.conf file on the fly and send
>> a SIGHUP to the postmaster.  I haven't ever heard of anyone doing that,
>> but I don't see any reason why it wouldn't work.
>>
>
> It works, I did it for a while with the statement_timeout to change it
> globally over night when admin tasks were running. For the
> statement_timeout thing it wasn't optimal however, finally I had to
> ditch the thing... but it can be done.

I'm confused, what does statement timeout have to do with this?  I was
assuming you would edit "autovacuum = off" to "autovacuum = on" wouldn't
that work?

> I'm confused, what does statement timeout have to do with this?  I was
> assuming you would edit "autovacuum = off" to "autovacuum = on" wouldn't
> that work?

Sorry for the confusion, I was thinking about the procedure of changing
programatically the config file and reload it, all this from a crontab.
That is working fine... only I did it for statement_timeout not
autovacuum, so you have to modify the script if you want to use it for
that.

Cheers,
Csaba.