Thread: How can I tell if I'm autovacuuming?

How can I tell if I'm autovacuuming?

From
Doug Gorley
Date:
From what I see in the docs, these three settings in postgresql.conf
should be enough for PostgreSQL (8.2) to autovacuum with the default
settings:

autovacuum = on
stats_start_collector = on
stats_row_level = on

So, two questions:

(a) Is this correct?  Should autovacuum be functional now?
(b) How can I verify that my databases are being vacuumed by autovacuum?

Thanks,

--
Doug Gorley | doug@gorley.ca


Re: How can I tell if I'm autovacuuming?

From
"Joshua D. Drake"
Date:
On Sat, 2010-05-22 at 09:51 -0400, Doug Gorley wrote:
> From what I see in the docs, these three settings in postgresql.conf
> should be enough for PostgreSQL (8.2) to autovacuum with the default
> settings:
>
> autovacuum = on
> stats_start_collector = on
> stats_row_level = on
>
> So, two questions:
>
> (a) Is this correct?  Should autovacuum be functional now?

If you restarted.

> (b) How can I verify that my databases are being vacuumed by autovacuum?

pg_stat_user_tables should have a last_autovac column.

>
> Thanks,
>
> --
> Doug Gorley | doug@gorley.ca
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Re: How can I tell if I'm autovacuuming?

From
"Joshua D. Drake"
Date:
On Sat, 2010-05-22 at 09:51 -0400, Doug Gorley wrote:
> From what I see in the docs, these three settings in postgresql.conf
> should be enough for PostgreSQL (8.2) to autovacuum with the default
> settings:
>
> autovacuum = on
> stats_start_collector = on
> stats_row_level = on
>
> So, two questions:
>
> (a) Is this correct?  Should autovacuum be functional now?

If you restarted.

> (b) How can I verify that my databases are being vacuumed by autovacuum?

pg_stat_user_tables should have a last_autovac column.

>
> Thanks,
>
> --
> Doug Gorley | doug@gorley.ca
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



Re: How can I tell if I'm autovacuuming?

From
Ireneusz Pluta
Date:
>> (b) How can I verify that my databases are being vacuumed by autovacuum?
>>
>
> pg_stat_user_tables should have a last_autovac column.
>
Also, you can see autovacuum related messages like:

DEBUG:  autovacuum: processing database "postgres"
DEBUG:  autovacuum: processing database "template1"

in your server log, by setting log_min_messages to 'debug1' in your
postgresql.conf.
As a side effect you may also see some other messages enabled with the
same debug level,


Re: How can I tell if I'm autovacuuming?

From
Mitch Collinsworth
Date:
On Sat, 22 May 2010, Joshua D. Drake wrote:

> On Sat, 2010-05-22 at 09:51 -0400, Doug Gorley wrote:
>> From what I see in the docs, these three settings in postgresql.conf
>> should be enough for PostgreSQL (8.2) to autovacuum with the default
>> settings:
>>
>> autovacuum = on
>> stats_start_collector = on
>> stats_row_level = on
>>
>> So, two questions:
>>
>> (a) Is this correct?  Should autovacuum be functional now?
>
> If you restarted.
>
>> (b) How can I verify that my databases are being vacuumed by autovacuum?
>
> pg_stat_user_tables should have a last_autovac column.


I'm wondering about the same question.  I did the above and restarted,
and now my pg_stat_user_tables looks like this:

postgres=# \d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
     Column     |  Type  | Modifiers
---------------+--------+-----------
  relid         | oid    |
  schemaname    | name   |
  relname       | name   |
  seq_scan      | bigint |
  seq_tup_read  | bigint |
  idx_scan      | bigint |
  idx_tup_fetch | bigint |
  n_tup_ins     | bigint |
  n_tup_upd     | bigint |
  n_tup_del     | bigint |
View definition:
  SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname,
pg_stat_all_tables.seq_scan,pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan,
pg_stat_all_tables.idx_tup_fetch,pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd,
pg_stat_all_tables.n_tup_del
    FROM pg_stat_all_tables
   WHERE pg_stat_all_tables.schemaname <> 'pg_catalog'::name AND pg_stat_all_tables.schemaname <> 'pg_toast'::name AND
pg_stat_all_tables.schemaname<> 'information_schema'::name; 


I don't see a last_autovac column, but I also don't see it in the
View definition.

So is this saying:
a) autovacuum is not running?
b) Something needs to update the view definition?
c) I need to wait [insert some period of time or number of transactions]
    before autovacuum runs and something updates the view definition?
d) none of the above?

-Mitch

Re: How can I tell if I'm autovacuuming?

From
Alvaro Herrera
Date:
Excerpts from Mitch Collinsworth's message of mar may 25 14:40:04 -0400 2010:

> I'm wondering about the same question.  I did the above and restarted,
> and now my pg_stat_user_tables looks like this:
>
> postgres=# \d pg_stat_user_tables
> View "pg_catalog.pg_stat_user_tables"
>      Column     |  Type  | Modifiers
> ---------------+--------+-----------
>   relid         | oid    |
>   schemaname    | name   |
>   relname       | name   |
>   seq_scan      | bigint |
>   seq_tup_read  | bigint |
>   idx_scan      | bigint |
>   idx_tup_fetch | bigint |
>   n_tup_ins     | bigint |
>   n_tup_upd     | bigint |
>   n_tup_del     | bigint |

I'd say you're running 8.1, not 8.2 as initially stated.

--
Álvaro Herrera <alvherre@alvh.no-ip.org>

Re: How can I tell if I'm autovacuuming?

From
Mitch Collinsworth
Date:


On Tue, 25 May 2010, Alvaro Herrera wrote:

> Excerpts from Mitch Collinsworth's message of mar may 25 14:40:04 -0400 2010:
>
>> I'm wondering about the same question.  I did the above and restarted,
>> and now my pg_stat_user_tables looks like this:
>>
>> postgres=# \d pg_stat_user_tables
>> View "pg_catalog.pg_stat_user_tables"
>>      Column     |  Type  | Modifiers
>> ---------------+--------+-----------
>>   relid         | oid    |
>>   schemaname    | name   |
>>   relname       | name   |
>>   seq_scan      | bigint |
>>   seq_tup_read  | bigint |
>>   idx_scan      | bigint |
>>   idx_tup_fetch | bigint |
>>   n_tup_ins     | bigint |
>>   n_tup_upd     | bigint |
>>   n_tup_del     | bigint |
>
> I'd say you're running 8.1, not 8.2 as initially stated.

Well...  yes.  Sorry.    I take it the answer is different then...

So...  How can I tell if I'm autovacuuming in 8.1.21?

-Mitch

Re: How can I tell if I'm autovacuuming?

From
Alvaro Herrera
Date:
Excerpts from Mitch Collinsworth's message of mar may 25 15:38:38 -0400 2010:

> On Tue, 25 May 2010, Alvaro Herrera wrote:

> > I'd say you're running 8.1, not 8.2 as initially stated.
>
> Well...  yes.  Sorry.    I take it the answer is different then...
>
> So...  How can I tell if I'm autovacuuming in 8.1.21?

Crank log_min_messages down to debug2 and check the log for DEBUG
messages talking about databases and tables processed by autovacuum.
(I think debug1 produces enough data, I can't recall precisely).

8.1 was the first release to sport integrated autovacuum.  It was very
rough around the edges.  If you're depending on its behavior, I suggest
you upgrade.

--
Álvaro Herrera <alvherre@commandprompt.com>
Command Prompt, Inc.  -- The PostgreSQL Company