Thread: Re: controlling autovacuum during the day.
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/
"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
> "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?
"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
> "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...
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
> 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...
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.
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
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.
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.
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.
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 >
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 >