Thread: How can I tell if I'm autovacuuming?
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
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
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
>> (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,
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
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>
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
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