From: | Mitch Collinsworth <mitch(at)ccmr(dot)cornell(dot)edu> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | Doug Gorley <doug(dot)gorley(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: How can I tell if I'm autovacuuming? |
Date: | 2010-05-25 18:40:04 |
Message-ID: | Pine.LNX.4.62.1005251433330.11199@mirror.ccmr.cornell.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josi Perez (3T Systems) | 2010-05-25 18:54:04 | Re: Trigger with dynamic SQL |
Previous Message | Chirag Dave | 2010-05-25 18:22:36 | Re: How to find if a SELECT is reading from buffer or disk ? |