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