pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum probably not working?
3+ messages / 2 participants
[nested] [flat]
* Autovacuum probably not working?
@ 2006-04-28 09:07 Szabolcs BALLA <szballa@confinsystems.com>
2006-04-28 12:03 ` Re: Autovacuum probably not working? Szabolcs BALLA <szballa@confinsystems.com>
0 siblings, 1 reply; 3+ messages in thread
From: Szabolcs BALLA @ 2006-04-28 09:07 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovacuum probably not working?
2006-04-28 09:07 Autovacuum probably not working? Szabolcs BALLA <szballa@confinsystems.com>
@ 2006-04-28 12:03 ` Szabolcs BALLA <szballa@confinsystems.com>
0 siblings, 0 replies; 3+ messages in thread
From: Szabolcs BALLA @ 2006-04-28 12:03 UTC (permalink / raw)
To: Robin Iddon <robin@edesix.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovacuum probably not working?
@ 2006-04-28 12:49 Jason Minion <jason.minion@sigler.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jason Minion @ 2006-04-28 12:49 UTC (permalink / raw)
To: Szabolcs BALLA <szballa@confinsystems.com>; Robin Iddon <robin@edesix.com>; pgsql-admin
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.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-28 12:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-28 09:07 Autovacuum probably not working? Szabolcs BALLA <szballa@confinsystems.com>
2006-04-28 12:03 ` Szabolcs BALLA <szballa@confinsystems.com>
2006-04-28 12:49 Re: Autovacuum probably not working? Jason Minion <jason.minion@sigler.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox