Re: Autovacuum probably not working?

The only way to recover a large amount of space like that is to issue a vacuum full. Autovacuum will not issue a vacuum full.

Vacuuming your tables without vacuum full does not recover space on the system. It marks recoverable space as usable. The idea with autovacuum is that most databases will exist in a steady state with respect to inserts/deletes/updates, and regular vacuuming will help you maintain a steady size - autovacuum gives you an easy method of automatically vacuuming the tables when necessary. For more information about vacuuming in general, especially concerning vacuum full and retrieving system space, you'll want to read:

http://www.postgresql.org/docs/8.0/interactive/maintenance.html#VACUUM-FOR-SPACE-RECOVERY

Jason Minion
jason(dot)minion(at)sigler(dot)com

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Szabolcs BALLA
Sent: Friday, April 28, 2006 7:03 AM
To: Robin Iddon; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Autovacuum probably not working?

Robin Iddon írta:
> Szabolcs BALLA wrote:
>
>> Hi,
>>
>> I delete 30M record from the db (8.1.0). I made query about size of
>> the tables, but after 1 day the size of the tables not changed.
>> (Autovacuum settings was original) So, I change the autovacuum
>> setting but after another day the size not changed.
>>
>> autovacuum_naptime = 10
>> autovacuum_vacuum_threshold = 1000
>> autovacuum_analyze_threshold = 500
>> autovacuum_vacuum_scale_factor = 0.1
>> autovacuum_analyze_scale_factor = 0.1 autovacuum_vacuum_cost_delay =
>> -1 autovacuum_vacuum_cost_limit = -1
>>
>>
>
> Check your FSM sizes in postgresql.conf ...
>
> Robin
>
>
shared_buffers = 153600
work_mem = 524288
maintenance_work_mem = 786432
max_fsm_pages = 510000
max_fsm_relations = 10000

Cost-Based Vacuum Delay is on default settings.
So, i thing my settings are ok. What do you think?

Thanks,
Szabek

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings