Обсуждение: Autovacuum probably not working?

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

SELECT datname, age(datfrozenxid) FROM pg_database;
   datname    |    age
-------------+------------
template1     | 1157096287
template0     |  171736920
db_h_old     | 1157091762


So, my questions: What can I do to get "clean" db with autovacuum?
I don't want to run vacuum full analyze manually because it takes too
much time!
Is it possible to see which table the autovacuum is working on?

Thanks,
Szabek

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

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.
Theidea with autovacuum is that most databases will exist in a steady state with respect to inserts/deletes/updates,
andregular vacuuming will help you maintain a steady size - autovacuum gives you an easy method of automatically
vacuumingthe tables when necessary. For more information about vacuuming in general, especially concerning vacuum full
andretrieving system space, you'll want to read: 

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

Jason Minion
jason.minion@sigler.com

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Szabolcs BALLA
Sent: Friday, April 28, 2006 7:03 AM
To: Robin Iddon; pgsql-admin@postgresql.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