Thread: Re: controlling autovacuum during the day.

Re: controlling autovacuum during the day.

From
"John Lister"
Date:
I'd like to use autovacuum to clean up the tables rather than schedule a
full vacuum with cron as it will be more selective/intelligent about what
gets cleaned. But is it possible to stop it running during peak/office
hours?

I've seen a post mention using pg_autovacuum.enabled to do this, but this
appears to be on a per table basis... I'd like to do this across the board,
without restarting the db. Is this possible or do i need to insert every
table into pg_autovacuum and run a script that sets the enabled flag as
appropriate?

Thanks
--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/


Re: controlling autovacuum during the day.

From
Tom Lane
Date:
"John Lister" <john.lister-ps@kickstone.com> writes:
> I'd like to use autovacuum to clean up the tables rather than schedule a
> full vacuum with cron as it will be more selective/intelligent about what
> gets cleaned. But is it possible to stop it running during peak/office
> hours?

No.  Instead, set the vacuum cost parameters to make it run slow enough
to not interfere too much with your work.

            regards, tom lane

Re: controlling autovacuum during the day.

From
"John Lister"
Date:
> "John Lister" <john.lister-ps@kickstone.com> writes:
>> I'd like to use autovacuum to clean up the tables rather than schedule a
>> full vacuum with cron as it will be more selective/intelligent about what
>> gets cleaned. But is it possible to stop it running during peak/office
>> hours?
>
> No.  Instead, set the vacuum cost parameters to make it run slow enough
> to not interfere too much with your work.

Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
a few heavily updated tables that i'd like vacuuming to keep them in check.
Unfortunately the autovacuum does a FULL vacuum every so often locking the
tables for quite a long time, i'd like to move these to the evening if
possible.

I guess the only option is to add the big tables to the pg_autovacuum table
and run a script to enable them in the evening?
I guess autovacuum picks up changes to the pg_autovacuum table...

Is a global flag something that could be added to a future release? Or is it
possible to get the autovacuum/db process to reload the config file while
running using pg_reload_conf() for example?



Re: controlling autovacuum during the day.

From
Tom Lane
Date:
"John Lister" <john.lister-ps@kickstone.com> writes:
> Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
> a few heavily updated tables that i'd like vacuuming to keep them in check.
> Unfortunately the autovacuum does a FULL vacuum every so often locking the
> tables for quite a long time, i'd like to move these to the evening if
> possible.

Huh?  Autovacuum *never* does VACUUM FULL.

            regards, tom lane

Re: controlling autovacuum during the day.

From
"John Lister"
Date:
> "John Lister" <john.lister-ps@kickstone.com> writes:
>> Cheers for the quick reply. I've tweaked them quite a bit, but we have
>> quite
>> a few heavily updated tables that i'd like vacuuming to keep them in
>> check.
>> Unfortunately the autovacuum does a FULL vacuum every so often locking
>> the
>> tables for quite a long time, i'd like to move these to the evening if
>> possible.
>
> Huh?  Autovacuum *never* does VACUUM FULL.

bizarre... Its been turned off for a while, but from memory the autovacuum
process was causing the table it was running on to be locked  - I assumed
this was an equivalent to VACUUM FULL - causing all other connections to
wait until it had finished. Could this happen another way, i thought the
other vacuum options acted passively... I'll turn it back on and see what
happens...



Re: controlling autovacuum during the day.

From
Alvaro Herrera
Date:
John Lister wrote:

> bizarre... Its been turned off for a while, but from memory the
> autovacuum process was causing the table it was running on to be locked
> - I assumed this was an equivalent to VACUUM FULL - causing all other
> connections to wait until it had finished. Could this happen another way,
> i thought the other vacuum options acted passively... I'll turn it back
> on and see what happens...

Maybe you're doing ALTER TABLE or something else that is blocked behind
vacuum?  Vacuum doesn't block INSERT, UPDATE or DELETE, but it can block
other operations that want exclusive locks on the table.

What Postgres version is this anyway?

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

Re: controlling autovacuum during the day.

From
"John Lister"
Date:
> John Lister wrote:
>
>> bizarre... Its been turned off for a while, but from memory the
>> autovacuum process was causing the table it was running on to be locked
>> - I assumed this was an equivalent to VACUUM FULL - causing all other
>> connections to wait until it had finished. Could this happen another way,
>> i thought the other vacuum options acted passively... I'll turn it back
>> on and see what happens...
>
> Maybe you're doing ALTER TABLE or something else that is blocked behind
> vacuum?  Vacuum doesn't block INSERT, UPDATE or DELETE, but it can block
> other operations that want exclusive locks on the table.
>
> What Postgres version is this anyway?


Now running 8.3, but this was on 8.2.

This was just with standard updates, etc

I guess i need to turn autovacuum back on to see what happens...


Re: controlling autovacuum during the day.

From
Alvaro Herrera
Date:
John Lister wrote:
>> John Lister wrote:
>>
>>> bizarre... Its been turned off for a while, but from memory the
>>> autovacuum process was causing the table it was running on to be locked
>>> - I assumed this was an equivalent to VACUUM FULL - causing all other
>>> connections to wait until it had finished. Could this happen another way,
>>> i thought the other vacuum options acted passively... I'll turn it back
>>> on and see what happens...
>>
>> Maybe you're doing ALTER TABLE or something else that is blocked behind
>> vacuum?  Vacuum doesn't block INSERT, UPDATE or DELETE, but it can block
>> other operations that want exclusive locks on the table.
>>
>> What Postgres version is this anyway?
>
> Now running 8.3, but this was on 8.2.

That doesn't help isolating the problem; 8.3 autovacuum behavior is very
different from 8.2.  Please enable it, see how it goes, and report back
if anything seems amiss.

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

Re: controlling autovacuum during the day.

From
Michael Fuhr
Date:
On Wed, Dec 17, 2008 at 09:47:23AM -0500, Tom Lane wrote:
> "John Lister" <john.lister-ps@kickstone.com> writes:
> > Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
> > a few heavily updated tables that i'd like vacuuming to keep them in check.
> > Unfortunately the autovacuum does a FULL vacuum every so often locking the
> > tables for quite a long time, i'd like to move these to the evening if
> > possible.
>
> Huh?  Autovacuum *never* does VACUUM FULL.

Perhaps autovacuum is shrinking the table after finding lots of empty
pages at the end, as in what VACUUM VERBOSE is logging here:

INFO:  "foo": truncated 11944384 to 8877366 pages

I think this acquires an AccessExclusiveLock.  I've seen this take
hours in the case of a table with a lot of empty pages.

--
Michael Fuhr

Re: controlling autovacuum during the day.

From
Alvaro Herrera
Date:
Michael Fuhr wrote:
> On Wed, Dec 17, 2008 at 09:47:23AM -0500, Tom Lane wrote:
> > "John Lister" <john.lister-ps@kickstone.com> writes:
> > > Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
> > > a few heavily updated tables that i'd like vacuuming to keep them in check.
> > > Unfortunately the autovacuum does a FULL vacuum every so often locking the
> > > tables for quite a long time, i'd like to move these to the evening if
> > > possible.
> >
> > Huh?  Autovacuum *never* does VACUUM FULL.
>
> Perhaps autovacuum is shrinking the table after finding lots of empty
> pages at the end, as in what VACUUM VERBOSE is logging here:
>
> INFO:  "foo": truncated 11944384 to 8877366 pages
>
> I think this acquires an AccessExclusiveLock.  I've seen this take
> hours in the case of a table with a lot of empty pages.

There was a bug in earlier versions which caused lazy vacuum to do
cost-based delays during this phase, which caused the lock to be held
for ridiculous lengths of time.  This was fixed in 8.2.something; it
shouldn't sleep anymore, but it does need to scan those pages in order
to truncate, so it can still take a while.

I think it was fixed in 8.2.4:

revision 1.81.2.1
date: 2007-09-10 13:58:50 -0400;  author: alvherre;  state: Exp;  lines: +6 -2;
Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
an exclusive lock on the table at this point, which we want to release as soon
as possible.  This is called in the phase of lazy vacuum where we truncate the
empty pages at the end of the table.

An alternative solution would be to lower the vacuum delay settings before
starting the truncating phase, but this doesn't work very well in autovacuum
due to the autobalancing code (which can cause other processes to change our
cost delay settings).  This case could be considered in the balancing code, but
it is simpler this way.


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

Re: controlling autovacuum during the day.

From
Brad Nicholson
Date:
On Wed, 2008-12-17 at 13:31 -0300, Alvaro Herrera wrote:
> Michael Fuhr wrote:
> > On Wed, Dec 17, 2008 at 09:47:23AM -0500, Tom Lane wrote:
> > > "John Lister" <john.lister-ps@kickstone.com> writes:
> > > > Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
> > > > a few heavily updated tables that i'd like vacuuming to keep them in check.
> > > > Unfortunately the autovacuum does a FULL vacuum every so often locking the
> > > > tables for quite a long time, i'd like to move these to the evening if
> > > > possible.
> > >
> > > Huh?  Autovacuum *never* does VACUUM FULL.
> >
> > Perhaps autovacuum is shrinking the table after finding lots of empty
> > pages at the end, as in what VACUUM VERBOSE is logging here:
> >
> > INFO:  "foo": truncated 11944384 to 8877366 pages
> >
> > I think this acquires an AccessExclusiveLock.  I've seen this take
> > hours in the case of a table with a lot of empty pages.
>
> There was a bug in earlier versions which caused lazy vacuum to do
> cost-based delays during this phase, which caused the lock to be held
> for ridiculous lengths of time.  This was fixed in 8.2.something; it
> shouldn't sleep anymore, but it does need to scan those pages in order
> to truncate, so it can still take a while.

Does this mean that if you are using autovac on 8.1, you should not use
the cost delay feature?
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.


Re: controlling autovacuum during the day.

From
Alvaro Herrera
Date:
Brad Nicholson wrote:

> Does this mean that if you are using autovac on 8.1, you should not use
> the cost delay feature?

No, because the fix was also applied to 8.1.10.

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

Re: controlling autovacuum during the day.

From
Rajesh Kumar Mallah
Date:
On Wed, Dec 17, 2008 at 7:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "John Lister" <john.lister-ps@kickstone.com> writes:
>> I'd like to use autovacuum to clean up the tables rather than schedule a
>> full vacuum with cron as it will be more selective/intelligent about what
>> gets cleaned. But is it possible to stop it running during peak/office
>> hours?
>
> No.  Instead, set the vacuum cost parameters to make it run slow enough
> to not interfere too much with your work.

is it a bad idea to have such a feature ?
i was going through ./src/backend/postmaster/autovacuum.c
looks like  subroutine autovac_start can be modfied to
for the said feature. In case it does not have negative implication
can i submit a patch ?

regds
-- mallah.


>
>                        regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Re: controlling autovacuum during the day.

From
"John Lister"
Date:
Sounds like a good idea and i think i think more control of the autovacuum
process has been discussed here recently.

As to my initial problem, i eventually solved it. One of my colleagues was
using a full table lock despite denying it originally, this was causing the
problem. Took some tracking down as it the culprit statement always looked
like an update hence my original questions..

Autovacuum running again fine now and i've even tuned it down a bit as
suggested which was a new feature for me

Thanks for all your help.

JOHN
----- Original Message -----
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "John Lister" <john.lister-ps@kickstone.com>;
<pgsql-admin@postgresql.org>
Sent: Wednesday, February 11, 2009 3:37 PM
Subject: Re: [ADMIN] controlling autovacuum during the day.


> On Wed, Dec 17, 2008 at 7:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "John Lister" <john.lister-ps@kickstone.com> writes:
>>> I'd like to use autovacuum to clean up the tables rather than schedule a
>>> full vacuum with cron as it will be more selective/intelligent about
>>> what
>>> gets cleaned. But is it possible to stop it running during peak/office
>>> hours?
>>
>> No.  Instead, set the vacuum cost parameters to make it run slow enough
>> to not interfere too much with your work.
>
> is it a bad idea to have such a feature ?
> i was going through ./src/backend/postmaster/autovacuum.c
> looks like  subroutine autovac_start can be modfied to
> for the said feature. In case it does not have negative implication
> can i submit a patch ?
>
> regds
> -- mallah.
>
>
>>
>>                        regards, tom lane
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>