Matthew,
Thank you for reply.
I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
Then waited for autovacuum to be activated for given table
(watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long).
When autovacuum was done I started VACUUM VERBOSE manually (to be more precise 6 sec later).
I expected that VACUUM VERBOSE will show me just few records to be removed (maybe around hundred), but in fact it reported 3200 record to be removed. It seems that autovacuum was started (anyway I expected it to be activated little bit earlier) but haven't do anything.
Any suggestions why ?
Do you know what is a difference between acivated autovacuum and VACUUM for given table ?
Tomasz
Thank you for reply.
I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
Then waited for autovacuum to be activated for given table
(watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long).
When autovacuum was done I started VACUUM VERBOSE manually (to be more precise 6 sec later).
I expected that VACUUM VERBOSE will show me just few records to be removed (maybe around hundred), but in fact it reported 3200 record to be removed. It seems that autovacuum was started (anyway I expected it to be activated little bit earlier) but haven't do anything.
Any suggestions why ?
Do you know what is a difference between acivated autovacuum and VACUUM for given table ?
Tomasz
----- Original Message ----
From: Matthew T. O'Connor <matthew@xxxxxxxx>
To: Tomasz Rakowski <mourawi@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
From: Matthew T. O'Connor <matthew@xxxxxxxx>
To: Tomasz Rakowski <mourawi@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
