pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: controlling autovacuum during the day.
14+ messages / 6 participants
[nested] [flat]
* Re: controlling autovacuum during the day.
@ 2008-12-17 12:56 John Lister <john.lister-ps@kickstone.com>
0 siblings, 1 reply; 14+ messages in thread
From: John Lister @ 2008-12-17 12:56 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2008-12-17 13:47 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 14:28 John Lister <john.lister-ps@kickstone.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: John Lister @ 2008-12-17 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> "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?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 14:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2008-12-17 14:47 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 15:20 John Lister <john.lister-ps@kickstone.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: John Lister @ 2008-12-17 15:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> "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...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 15:28 Alvaro Herrera <alvherre@commandprompt.com>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 1 reply; 14+ messages in thread
From: Alvaro Herrera @ 2008-12-17 15:28 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 15:29 Michael Fuhr <mike@fuhr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Fuhr @ 2008-12-17 15:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 15:40 John Lister <john.lister-ps@kickstone.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 14+ messages in thread
From: John Lister @ 2008-12-17 15:40 UTC (permalink / raw)
To: pgsql-admin
> 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...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 15:42 Alvaro Herrera <alvherre@commandprompt.com>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 0 replies; 14+ messages in thread
From: Alvaro Herrera @ 2008-12-17 15:42 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 16:31 Alvaro Herrera <alvherre@commandprompt.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Alvaro Herrera @ 2008-12-17 16:31 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 19:35 Brad Nicholson <bnichols@ca.afilias.info>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 14+ messages in thread
From: Brad Nicholson @ 2008-12-17 19:35 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Michael Fuhr <mike@fuhr.org>; Tom Lane <tgl@sss.pgh.pa.us>; John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2008-12-17 19:46 Alvaro Herrera <alvherre@commandprompt.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 0 replies; 14+ messages in thread
From: Alvaro Herrera @ 2008-12-17 19:46 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Michael Fuhr <mike@fuhr.org>; Tom Lane <tgl@sss.pgh.pa.us>; John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2009-02-11 15:37 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Rajesh Kumar Mallah @ 2009-02-11 15:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: controlling autovacuum during the day.
@ 2009-02-11 16:31 John Lister <john.lister-ps@kickstone.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: John Lister @ 2009-02-11 16:31 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2009-02-11 16:31 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-17 12:56 Re: controlling autovacuum during the day. John Lister <john.lister-ps@kickstone.com>
2008-12-17 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-17 14:28 ` John Lister <john.lister-ps@kickstone.com>
2008-12-17 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-17 15:20 ` John Lister <john.lister-ps@kickstone.com>
2008-12-17 15:28 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-12-17 15:40 ` John Lister <john.lister-ps@kickstone.com>
2008-12-17 15:42 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-12-17 15:29 ` Michael Fuhr <mike@fuhr.org>
2008-12-17 16:31 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-12-17 19:35 ` Brad Nicholson <bnichols@ca.afilias.info>
2008-12-17 19:46 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-02-11 15:37 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2009-02-11 16:31 ` John Lister <john.lister-ps@kickstone.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox