Thread: Autovacuum firing up during my manual vacuum on same table
Greets, I just noticed something odd: I'm busy with a manual vacuum on a table and an autovacuum keeps firing up as well. Thinking this looks rather weird, I pg_cancel_backend() the autovacuum process: current_query | vacuum analyze page_citation_text; age | 11:34:10.759279 ... current_query | autovacuum: VACUUM ANALYZE public.page_citation_text age | 11:33:15.824014 However, Pg keeps firing up the autovacuum: current_query | vacuum analyze page_citation_text; age | 11:46:57.245568 ... current_query | autovacuum: VACUUM ANALYZE public.page_citation_text age | 00:11:50.571 It seems counter-intuitive to have two vacuum procs running on the same table... how is this possible? I'm trying to vacuum the table as quickly as possible so a manual vacuum seems to be in order as my understanding (and experience) is that the autovac is a hell of a lot slower to mitigate impact on general performance. Anyway, is that autovac duplicating work or locked out and waiting? Thanks Henry
> Anyway, is that autovac duplicating work or locked out and waiting? Impolitely responding to my own post: a quick strace confirms the autovac process is indeed locked out and waiting it's turn to work. Presumably when my manual vacuum finishes, it will then proceed and *hopefully* not re-vacuum the table? Regards Henry
On Samstag, 2. April 2011, Henry C. wrote: > I just noticed something odd: I'm busy with a manual vacuum on a > table and an autovacuum keeps firing up as well. Usually a manual vacuum cancels a running autovacuum task. You should find a notice about the cancelation in th logfile. > current_query | vacuum analyze > age | 11:46:57.245568 Where is the age column from? It's not in pg_stat_activity. Is one of the two processes waiting=t in pg_stat_activity? Shure it's the same Table? Do you have one Table named page_citation_text in public and one in anoter sheme? > I'm trying to vacuum the table as quickly as possible so a manual > vacuum seems to be in order as my understanding (and experience) > is that the autovac is a hell of a lot slower to mitigate impact > on general performance. Tune the autovacuum settings, especially the scale factors of the tables in question and the cost limit. Is autovacuum_vacuum_cost_limit != -1 ? This could be one reason, why manual vacuum is faster. Nevertheless since at least 8.4 IMO there's no need to bother with manual vacuum any more. Regards, Jens
-------- Original-Nachricht -------- > Datum: Sat, 2 Apr 2011 14:17:37 +0200 > Von: Jens Wilke <jens@wilke.org> > An: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table > On Samstag, 2. April 2011, Henry C. wrote: > > > I just noticed something odd: I'm busy with a manual vacuum on a > > table and an autovacuum keeps firing up as well. > > Usually a manual vacuum cancels a running autovacuum task. > You should find a notice about the cancelation in th logfile. > > > current_query | vacuum analyze > > age | 11:46:57.245568 > > Where is the age column from? > It's not in pg_stat_activity. > Is one of the two processes waiting=t in pg_stat_activity? > Shure it's the same Table? Do you have one Table named > page_citation_text in public and one in anoter sheme? > > > I'm trying to vacuum the table as quickly as possible so a manual > > vacuum seems to be in order as my understanding (and experience) > > is that the autovac is a hell of a lot slower to mitigate impact > > on general performance. > > Tune the autovacuum settings, especially the scale factors of the > tables in question and the cost limit. > Is autovacuum_vacuum_cost_limit != -1 ? > This could be one reason, why manual vacuum is faster. > > Nevertheless since at least 8.4 IMO there's no need to bother with > manual vacuum any more. > > Regards, Jens a bit off-topic, but: i'm using pg 9 and always getting messages to vacuum tables in pgadmin. according to your post this souldn't be the case? cheers sven -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
Le 02/04/2011 16:06, Sven Haag a écrit : > > -------- Original-Nachricht -------- >> Datum: Sat, 2 Apr 2011 14:17:37 +0200 >> Von: Jens Wilke <jens@wilke.org> >> An: pgsql-general@postgresql.org >> Betreff: Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table > >> On Samstag, 2. April 2011, Henry C. wrote: >> >>> I just noticed something odd: I'm busy with a manual vacuum on a >>> table and an autovacuum keeps firing up as well. >> >> Usually a manual vacuum cancels a running autovacuum task. >> You should find a notice about the cancelation in th logfile. >> >>> current_query | vacuum analyze >>> age | 11:46:57.245568 >> >> Where is the age column from? >> It's not in pg_stat_activity. >> Is one of the two processes waiting=t in pg_stat_activity? >> Shure it's the same Table? Do you have one Table named >> page_citation_text in public and one in anoter sheme? >> >>> I'm trying to vacuum the table as quickly as possible so a manual >>> vacuum seems to be in order as my understanding (and experience) >>> is that the autovac is a hell of a lot slower to mitigate impact >>> on general performance. >> >> Tune the autovacuum settings, especially the scale factors of the >> tables in question and the cost limit. >> Is autovacuum_vacuum_cost_limit != -1 ? >> This could be one reason, why manual vacuum is faster. >> >> Nevertheless since at least 8.4 IMO there's no need to bother with >> manual vacuum any more. >> >> Regards, Jens > > a bit off-topic, but: > i'm using pg 9 and always getting messages to vacuum tables in pgadmin. according to your post this souldn't be the case? > pgAdmin's hint doesn't take care of your PostgreSQL release. Its way to guess that a table needs to be vacuumed is a bit outdated (that's clearly an understatement :) ). -- Guillaume http://www.postgresql.fr http://dalibo.com
Forgot to mention: I'm using 9.0.3 > Usually a manual vacuum cancels a running autovacuum task. Not in my case - however, the autovac does seem to be in a waiting state. > You should find a notice about the cancelation in th logfile. > > > current_query | vacuum analyze > > age | 11:46:57.245568 > > Where is the age column from? > It's not in pg_stat_activity. age(now(), query_start) >> Is one of the two processes waiting=t in pg_stat_activity? ah, there it is. Yes, the autovac is waiting. >> > I'm trying to vacuum the table as quickly as possible so a manual >> > vacuum seems to be in order as my understanding (and experience) >> > is that the autovac is a hell of a lot slower to mitigate impact >> > on general performance. >> >> Tune the autovacuum settings, especially the scale factors of the >> tables in question and the cost limit. >> Is autovacuum_vacuum_cost_limit != -1 ? >> This could be one reason, why manual vacuum is faster. autovacuum_vacuum_cost_limit is on default (-1).
On Sat, April 2, 2011 14:17, Jens Wilke wrote: > Nevertheless since at least 8.4 IMO there's no need to bother with > manual vacuum any more. Sadly, in my case, the db is so busy that autovac processes run for weeks and never catch up (insufficient h/w for the app quite frankly - the addition of some more SSD drives have already helped). I eventually run up against the wraparound wall and the only way forward is to stop everything and dump/restore (vacuuming the entire db would take an unknown period of N x weeks - dumping/restoring completes in a day or two). I really wish the xid was 64 bits instead of 32, but that's another topic entirely. Cheers Henry
On Sat, Apr 2, 2011 at 11:26 AM, Henry C. <henka@cityweb.co.za> wrote: > On Sat, April 2, 2011 14:17, Jens Wilke wrote: >> Nevertheless since at least 8.4 IMO there's no need to bother with >> manual vacuum any more. > > Sadly, in my case, the db is so busy that autovac processes run for weeks and > never catch up (insufficient h/w for the app quite frankly - the addition of > some more SSD drives have already helped). I eventually run up against the > wraparound wall and the only way forward is to stop everything and > dump/restore (vacuuming the entire db would take an unknown period of N x > weeks - dumping/restoring completes in a day or two). Have you tried upping the aggressiveness of autovacuum?
On Samstag, 2. April 2011, Henry C. wrote: > Sadly, in my case, the db is so busy that autovac processes run > for weeks and never catch up Increase the cost_limit and the HW and/or check your application, if it's possible to reduce the amount of deletes and/or updates. Regards, Jens
On Sat, April 2, 2011 21:26, Scott Marlowe wrote: > On Sat, Apr 2, 2011 at 11:26 AM, Henry C. <henka@cityweb.co.za> wrote: > >> On Sat, April 2, 2011 14:17, Jens Wilke wrote: >> >>> Nevertheless since at least 8.4 IMO there's no need to bother with >>> manual vacuum any more. >> >> Sadly, in my case, the db is so busy that autovac processes run for weeks >> and never catch up (insufficient h/w for the app quite frankly - the >> addition of some more SSD drives have already helped). I eventually run up >> against the wraparound wall and the only way forward is to stop everything >> and dump/restore (vacuuming the entire db would take an unknown period of N >> x weeks - dumping/restoring completes in a day or two). > > Have you tried upping the aggressiveness of autovacuum? Thanks for the suggestion - I'm going to give autovacuum_vacuum_cost_delay=0 a try (instead of the default 20ms, which if I'm reading the docs correctly, means the same aggressiveness as a manual vacuum), and see how things go in terms of the I/O cost/responsiveness and ensuring the damn vacuums finish in a reasonable time before the wraparound tactical nuke hits :)
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Sat, Apr 2, 2011 at 11:26 AM, Henry C. <henka@cityweb.co.za> wrote:
>> Sadly, in my case, the db is so busy that autovac processes run for weeks and
>> never catch up (insufficient h/w for the app quite frankly - the addition of
>> some more SSD drives have already helped).
> Have you tried upping the aggressiveness of autovacuum?
I'm wondering about poor selection of the cost_delay settings in
particular. It's quite easy to slow autovacuum to the point that
it takes forever to do anything.
It's also possible that Henry is getting bit by the bug fixed here:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master [b58c25055] 2010-11-19 22:28:20 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500
Fix leakage of cost_limit when multiple autovacuum workers are active.
When using default autovacuum_vac_cost_limit, autovac_balance_cost relied
on VacuumCostLimit to contain the correct global value ... but after the
first time through in a particular worker process, it didn't, because we'd
trashed it in previous iterations. Depending on the state of other autovac
workers, this could result in a steady reduction of the effective
cost_limit setting as a particular worker processed more and more tables,
causing it to go slower and slower. Spotted by Simon Poole (bug #5759).
Fix by saving and restoring the GUC variables in the loop in do_autovacuum.
regards, tom lane
On Sat, April 2, 2011 22:30, Tom Lane wrote: >> Have you tried upping the aggressiveness of autovacuum? >> > > I'm wondering about poor selection of the cost_delay settings in > particular. It's quite easy to slow autovacuum to the point that it takes > forever to do anything. It's been on the default 20ms. Now giving 0 a try. In our app responsiveness is less of a concern since we don't have human interaction. Reliability is a greater concern. > It's also possible that Henry is getting bit by the bug fixed here: > > > Author: Tom Lane <tgl@sss.pgh.pa.us> > Branch: master [b58c25055] 2010-11-19 22:28:20 -0500 > Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500 > Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500 > Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500 > > > Fix leakage of cost_limit when multiple autovacuum workers are active. I'm using 9.0.3, and typically (when things eventually deteriorate to a impending-wraparound situation) there are at least 2 and sometimes a few more autovac procs running - some of them for weeks). Anyway, time will now tell whether a cost_delay of 0 and some more SSDs will help prevent hitting the wraparound wall. Cheers h
On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C." <henka@cityweb.co.za> wrote: > On Sat, April 2, 2011 14:17, Jens Wilke wrote: >> Nevertheless since at least 8.4 IMO there's no need to bother with >> manual vacuum any more. Uhh, this is entirely untrue. There are plenty of cases where 8.4 autovacuum can't cut it. > > Sadly, in my case, the db is so busy that autovac processes run for weeks > and > never catch up (insufficient h/w for the app quite frankly - the addition > of You can disable autovacuum for the relations that you are manually vacuuming. If pre 8.4 see pg_autovacuum, if post see alter table. JD -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Sun, Apr 3, 2011 at 2:39 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C." <henka@cityweb.co.za> wrote: >> On Sat, April 2, 2011 14:17, Jens Wilke wrote: >>> Nevertheless since at least 8.4 IMO there's no need to bother with >>> manual vacuum any more. > > Uhh, this is entirely untrue. There are plenty of cases where 8.4 > autovacuum can't cut it. This is doubly true if you haven't adjusted any of the default costing parameters of autovacuum. It is not set to the level of aggressiveness needed for big servers under high load, since doing so would slow down / swamp smaller servers.
On 3. April 2011, Joshua D. Drake wrote: > On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C." <henka@cityweb.co.za> wrote: > > On Sat, April 2, 2011 14:17, Jens Wilke wrote: > >> Nevertheless since at least 8.4 IMO there's no need to bother > >> with manual vacuum any more. > > Uhh, this is entirely untrue. There are plenty of cases where 8.4 > autovacuum can't cut it. Which cases? Isn't it more like something else went suboptimal when starting to think about manual vacuum? May be i better had written that since 8.4 there's the opportunity not to bother with manual vacuum any more. Regards, Jens