Thread: Autovacuum ideas

I have an idea/request for the autovacuum daemon.

As far as I can tell, there is no way to tell the daemon to only do it's work between certain hours.  Could this be added as an option?  In my situation, I have tables that if vacuumed during the day would cause a noticeable slowdown to my users and has therefore been deemed unacceptable by management.  This is one thing that is preventing me from turning on autovacuun,.  However, due to the activity on them, they really need to be vacuumed daily.  It would be nice to be able to tell autovacuum to run, but have a way to tell it to always sleep during these hours ( i.e. 7am to 5pm daily).  Has there been any thought into a feature like this?

Thanks,

Chris

Hi Chris Hoover,


There is already a method for this! Can i suggest that you set a O/S
schedule to simple run vacuumdb.exe at the desired time, preferable late
evening, as the vacuum of large databases can take all night, based on
my DBA experience. Don’t for get to setup the uses of password files, as
vacuumdb.exe will require a password and user account each end every
time it runs.

Anthony.

Chris Hoover wrote:

> I have an idea/request for the autovacuum daemon.
>
> As far as I can tell, there is no way to tell the daemon to only do
> it's work between certain hours. Could this be added as an option? In
> my situation, I have tables that if vacuumed during the day would
> cause a noticeable slowdown to my users and has therefore been deemed
> unacceptable by management. This is one thing that is preventing me
> from turning on autovacuun,. However, due to the activity on them,
> they really need to be vacuumed daily. It would be nice to be able to
> tell autovacuum to run, but have a way to tell it to always sleep
> during these hours ( i.e. 7am to 5pm daily). Has there been any
> thought into a feature like this?
>
> Thanks,
>
> Chris

>   This is one thing that is preventing me from turning on autovacuun,.
> However, due to the activity on them, they really need to be vacuumed
> daily.  It would be nice to be able to tell autovacuum to run, but
> have a way to tell it to always sleep during these hours ( i.e. 7am to
> 5pm daily).  Has there been any thought into a feature like this?

Well... just turn off autovac and then use vacuumdb during off hours via
cron.

Joshua D. Drake

>
> Thanks,
>
> Chris
--

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

Chris Hoover wrote:

> As far as I can tell, there is no way to tell the daemon to only do it's
> work between certain hours.  Could this be added as an option?  In my
> situation, I have tables that if vacuumed during the day would cause a
> noticeable slowdown to my users and has therefore been deemed unacceptable
> by management.  This is one thing that is preventing me from turning on
> autovacuun,.  However, due to the activity on them, they really need to be
> vacuumed daily.  It would be nice to be able to tell autovacuum to run, but
> have a way to tell it to always sleep during these hours (i.e. 7am to 5pm
> daily).  Has there been any thought into a feature like this?

Well, this is something that is (or should be) in the TODO list, but no
one has come around with a proposal to implement it yet.  I've given it
tiny amounts of thought; a system not unlike Unix's crontab might do it.
You would specify intervals at which you wish autovacuum to operate on
certain groups of tables; or intervals at which you don't want autovac
to run at all, etc.

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

This is what we are doing today (except we are on Linux :) ).  However, since there is talk of making autovacuum on by default and the advantages of having it run, it would be nice to be able to block out hours that it can not run.  It would also be very nice to have the database take care of all of this via the config file and/or tables.  That way, no external programs (cron or what ever) would be needed.

Even nicer would be to have the time exclusion granular enough to be table to exclude only certain tables.  That way autovacuum can do it's work as needed on the majority of the tables, but tables that the DBA knows will cause problems during business hours could be excluded until the slow time.


Any comments on this?

Chris

Chris Hoover wrote:
> This is what we are doing today (except we are on Linux :) ).  However,
> since there is talk of making autovacuum on by default and the advantages of
> having it run, it would be nice to be able to block out hours that it can
> not run.  It would also be very nice to have the database take care of all
> of this via the config file and/or tables.  That way, no external programs
> (cron or what ever) would be needed.
>
> Even nicer would be to have the time exclusion granular enough to be table
> to exclude only certain tables.  That way autovacuum can do it's work as
> needed on the majority of the tables, but tables that the DBA knows will
> cause problems during business hours could be excluded until the slow time.
>
> Any comments on this?

I don't think autovacuum will be on by default until we have a way to
configure rush hours, maintenance windows, etc.

The system I envision has tables grouped in some way, and maintenance
windows would operate on table groups.  For example you will be able to
include the fat table in a (possibly otherwise empty) group, and have
that group not be processed during "work hours"; the rest of the tables
would continue to be vacuumed at all times.

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

On Wednesday 12 April 2006 07:53, Chris Hoover wrote:
> This is what we are doing today (except we are on Linux :) ).  However,
> since there is talk of making autovacuum on by default and the advantages
> of having it run, it would be nice to be able to block out hours that it
> can not run.  It would also be very nice to have the database take care of
> all of this via the config file and/or tables.  That way, no external
> programs (cron or what ever) would be needed.
>
> Even nicer would be to have the time exclusion granular enough to be table
> to exclude only certain tables.  That way autovacuum can do it's work as
> needed on the majority of the tables, but tables that the DBA knows will
> cause problems during business hours could be excluded until the slow time.

What about leaving autovac running, and poke values into the pg_autovacuum
table via cron?

>
>
> Any comments on this?
>
> Chris
>
> On 4/11/06, Anthony Ransley <anthonyr@aurema.com> wrote:
> > Hi Chris Hoover,
> >
> >
> > There is already a method for this! Can i suggest that you set a O/S
> > schedule to simple run vacuumdb.exe at the desired time, preferable late
> > evening, as the vacuum of large databases can take all night, based on
> > my DBA experience. Don't for get to setup the uses of password files, as
> > vacuumdb.exe will require a password and user account each end every
> > time it runs.
> >
> > Anthony.
> >
> > Chris Hoover wrote:
> > > I have an idea/request for the autovacuum daemon.
> > >
> > > As far as I can tell, there is no way to tell the daemon to only do
> > > it's work between certain hours. Could this be added as an option? In
> > > my situation, I have tables that if vacuumed during the day would
> > > cause a noticeable slowdown to my users and has therefore been deemed
> > > unacceptable by management. This is one thing that is preventing me
> > > from turning on autovacuun,. However, due to the activity on them,
> > > they really need to be vacuumed daily. It would be nice to be able to
> > > tell autovacuum to run, but have a way to tell it to always sleep
> > > during these hours ( i.e. 7am to 5pm daily). Has there been any
> > > thought into a feature like this?
> > >
> > > Thanks,
> > >
> > > Chris

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

I love this idea.  I think on initial consideration it might have a lot of merit.  You could have a setup something like:

pg_av_grp -- table of group names
avgrpname varchar(50) -- group name

pg_av_grp_window -- time windows when not to vacuum the group of tables
avgrpwinrelid = pg_av_group.oid
no_run_start time - starting of exclusion block
no_run_stop time - stopping of exclusion block
no_run_day <not sure of format> - day(s) of this exclusion

pg_av_grp_rel -- the list of tables in a group
avgrprelid = pg_av_grp.oid
avgrptabid = pg_class.oid

Something like this structure would allow you a lot of flexibility in building your auto vacuum groups.  You can define your groups with the ability to have multiple windows per group.

This would allow you to basically have rules like this:
big_group - table_a, table_b, table_f
no_run_start 08:00
no_run_stop 17:00
no_run_day (mon-fri)
-- don't vacuum the big group tables during the M-F work hours

load_group - table_c, table_d, table_e
no_run_start 06:00
no_run_stop 08:00
no_run_day (mon-sun)

no_run_start 18:00
no_run_stop 20:00
no_run_day (mon-sun)

-- don't vacuum the main tables we load during the 2 load windows.

Tables not in a group would be in a default group that could be vacuumed at any time as needed.

I'm not really a C programmer, so I don't know what the internals would take to accomplish this, but maybe it can spur some thought and development.

Chris

What I'd like to see is a table exclusion list. I have a few very large history tables that are never updated or deleted, only inserts and selects.

On 13.04.2006, at 7:56 Uhr, Brendan Duddridge wrote:

> What I'd like to see is a table exclusion list. I have a few very
> large history tables that are never updated or deleted, only
> inserts and selects.

But if you do selects regularly you may want to have the analyse part
of autovacuum for this tables. Or you can analyse on a nightly basis
- perhaps it's good enough.

cug

Brendan Duddridge wrote:
> What I'd like to see is a table exclusion list. I have a few very
> large history tables that are never updated or deleted, only inserts
> and selects.

Such a table will never trigger the vacuum rules as I understand them
(vacuum only happens on table that have obsolete tuples, which means
update or delete).

It will correctly trigger the analyze rules now and then, but analyze is
cheap compared to vacuum and is desirable because it will help the
planner do it's job (assuming that querying the table is important to
you).  Remember analyze only requires a read-lock on the table so it can
run in parallel with other queries quite happily.

If you really need to disable autovac on a table you can disable
pg_autovacuum from running on a specific table by creating a row for
your table in pg_autovacuum table and setting the pg_autovacuum.enabled
to false.



Robin

The problem is the PG team are looking at making autovac compulsory (ie. You
can't turn it off).  I don't know when, might be in 8.2, but I know the
issue has been discussed on the lists before.

Andy

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Joshua D. Drake
Sent: Wednesday, 12 April, 2006 12:51 am
To: Chris Hoover
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Autovacuum ideas

>   This is one thing that is preventing me from turning on autovacuun,.
> However, due to the activity on them, they really need to be vacuumed
> daily.  It would be nice to be able to tell autovacuum to run, but
> have a way to tell it to always sleep during these hours ( i.e. 7am to
> 5pm daily).  Has there been any thought into a feature like this?

Well... just turn off autovac and then use vacuumdb during off hours via
cron.

Joshua D. Drake

>
> Thanks,
>
> Chris
--

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





---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

!DSPAM:14,443c41f433694226768114!

I guess I was thinking that if you do a vacuum analyze verbose from
within psql, it does vacuum the big 'insert only' tables. Of course
it never finds any dead rows, but it does take a long time to get
past those tables. I didn't know that autovacuum would be any different.

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

On Apr 13, 2006, at 1:36 AM, Robin Iddon wrote:

> Brendan Duddridge wrote:
>> What I'd like to see is a table exclusion list. I have a few very
>> large history tables that are never updated or deleted, only
>> inserts and selects.
>
> Such a table will never trigger the vacuum rules as I understand
> them (vacuum only happens on table that have obsolete tuples, which
> means update or delete).
>
> It will correctly trigger the analyze rules now and then, but
> analyze is cheap compared to vacuum and is desirable because it
> will help the planner do it's job (assuming that querying the table
> is important to you).  Remember analyze only requires a read-lock
> on the table so it can run in parallel with other queries quite
> happily.
>
> If you really need to disable autovac on a table you can disable
> pg_autovacuum from running on a specific table by creating a row
> for your table in pg_autovacuum table and setting the
> pg_autovacuum.enabled to false.
>
>
>
> Robin
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

If autovacuum is to be provided with facilities for individual table and time window controls, that can be done currently using VACUUM TABLE FULL. Currently this must be scheduled in crontab - a task scheduler in PGAdmin would be very helpful to do this instead of crontab that usually requires sysadmin access, or is heavily controlled.