autovacuum

Noah Freire wrote:

<2008-10-29 11:09:03.453 PDT>DEBUG: 00000: accounts: vac: 16697969
(threshold 6000050), anl: 16697969 (threshold 120000048)
<2008-10-29 11:09:05.610 PDT>DEBUG: 00000: accounts: vac: 16699578
(threshold 6000050), anl: 16699578 (threshold 120000048)
<2008-10-29 11:10:03.563 PDT>DEBUG: 00000: accounts: vac: 16735906
(threshold 6000050), anl: 16735906 (threshold 120000048)

please check the first log message: the vacuum threshold is 6,000,050
rows and the number of dead tuples is 16,697,969. Even though the number
of dead_tuples is greater than the threshold the autovacuum is not being
triggered for this table. So, besides this condition (dead_tuples >
threshold) what else is taken into account by autovacuum?

What version of PostgreSQL? Is the table being excluded? (see the
pg_autovacuum system table settings) Are you sure that it's not getting
processed? Perhaps one worker is / has been churning on this table for a
*LONG* time (that is a fairly big table). What does it say for the
most recent autovacuum?

SELECT relid, schemaname, relname, last_vacuum, last_autovacuum from
pg_stat_all_tables;

On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <matthew@zeut.net>wrote:

Noah Freire wrote:

<2008-10-29 11:09:03.453 PDT>DEBUG: 00000: accounts: vac: 16697969
(threshold 6000050), anl: 16697969 (threshold 120000048)
<2008-10-29 11:09:05.610 PDT>DEBUG: 00000: accounts: vac: 16699578
(threshold 6000050), anl: 16699578 (threshold 120000048)
<2008-10-29 11:10:03.563 PDT>DEBUG: 00000: accounts: vac: 16735906
(threshold 6000050), anl: 16735906 (threshold 120000048)

please check the first log message: the vacuum threshold is 6,000,050 rows
and the number of dead tuples is 16,697,969. Even though the number of
dead_tuples is greater than the threshold the autovacuum is not being
triggered for this table. So, besides this condition (dead_tuples >
threshold) what else is taken into account by autovacuum?

What version of PostgreSQL?

8.3

Is the table being excluded? (see the pg_autovacuum system table settings)

there's an entry for this table on pg_autovacuum, and it's enabled.

Are you sure that it's not getting processed? Perhaps one worker is / has
been churning on this table for a *LONG* time (that is a fairly big
table).

Right. I was wrong :-) the table is being processed by autovacuum (I checked
via pg_stat_activity). However, as you pinpointed, it's already running for
hours (the test workload ended hours ago, basically it is just this
autovacuum worker running on the system).

Is there a way to make a more aggressive autovacuum setting for this table?
it does not matter if it will affect performance, my concern is that it
finishes as soon as possible. I wonder if a manual vacuum wouldn't be
faster.

Thanks,

-Noah

Noah Freire wrote:

On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <matthew@zeut.net
<mailto:matthew@zeut.net>> wrote:

Is the table being excluded? (see the pg_autovacuum system table
settings)

there's an entry for this table on pg_autovacuum, and it's enabled.

Are you sure that it's not getting processed? Perhaps one worker
is / has been churning on this table for a *LONG* time (that is a
fairly big table).

Right. I was wrong :-) the table is being processed by autovacuum (I
checked via pg_stat_activity). However, as you pinpointed, it's
already running for hours (the test workload ended hours ago,
basically it is just this autovacuum worker running on the system).

Is there a way to make a more aggressive autovacuum setting for this
table? it does not matter if it will affect performance, my concern is
that it finishes as soon as possible. I wonder if a manual vacuum
wouldn't be faster.

Yes, in the pg_autovacuum table, you can set per-relation vacuum cost
delay settings etc...

On Thu, Oct 30, 2008 at 8:53 PM, Matthew T. O'Connor <matthew@zeut.net>wrote:

Noah Freire wrote:

On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <matthew@zeut.net<mailto:
matthew@zeut.net>> wrote:
Is the table being excluded? (see the pg_autovacuum system table
settings)
there's an entry for this table on pg_autovacuum, and it's enabled.

Are you sure that it's not getting processed? Perhaps one worker
is / has been churning on this table for a *LONG* time (that is a
fairly big table).
Right. I was wrong :-) the table is being processed by autovacuum (I
checked via pg_stat_activity). However, as you pinpointed, it's already
running for hours (the test workload ended hours ago, basically it is just
this autovacuum worker running on the system). Is there a way to make a
more aggressive autovacuum setting for this table? it does not matter if it
will affect performance, my concern is that it finishes as soon as possible.
I wonder if a manual vacuum wouldn't be faster.

Yes, in the pg_autovacuum table, you can set per-relation vacuum cost
delay settings etc...

Right. cost-delay for this table is already zeroed. Perhaps autovacuum
could have an entry for cpu and/or i/o usage threshold, in a way that when
one of this resources had an activity below a pre-defined threshold,
autovacuum could run more aggressively (using more i/o and/or more cpu).