Autovacuum firing up during my manual vacuum on same table

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