Skip site navigation (1) Skip section navigation (2)

Re: autovacumm not working ?

From: Tomasz Rakowski <mourawi(at)yahoo(dot)com>
To: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: autovacumm not working ?
Date: 2007-06-27 21:21:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

  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 ?


----- Original Message ----
From: Matthew T. O'Connor <matthew(at)zeut(dot)net>
To: Tomasz Rakowski <mourawi(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
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.

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links.


pgsql-general by date

Next:From: Vincenzo RomanoDate: 2007-06-27 21:27:17
Subject: Execution variability
Previous:From: Bruce McAlisterDate: 2007-06-27 21:18:37
Subject: Re: AutoVacuum Behaviour Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group