pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAbnormal storage consumption with autovacuum enabled
4+ messages / 2 participants
[nested] [flat]
* Abnormal storage consumption with autovacuum enabled
@ 2007-05-24 15:56 Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 1 reply; 4+ messages in thread
From: Nicola Mauri @ 2007-05-24 15:56 UTC (permalink / raw)
To: pgsql-admin
I'm using 8.1.4 on linux.
Autovacuum is enable with these settings:
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 500
Data stored in the database didn't groove very much in the last months
(pg_dump still generates a 4GB compressed file), but data files now
occupies 20 GB on disk.
I know that a vacuum full or a full dump/restore is needed to reclaim
filesystem space. However, my question is: how this could have been
happened?
I'm quite sure that data in tables never reached that size, during
normal operations.
Could be autovacuum not working properly?
How to diagnose or make sure that autovacuum is doing its job?
Thanks in advance!
Nicola
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Abnormal storage consumption with autovacuum enabled
@ 2007-05-24 16:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-05-24 16:40 UTC (permalink / raw)
To: Nicola Mauri <nicola.mauri@saga.it>; +Cc: pgsql-admin
Nicola Mauri <nicola.mauri@saga.it> writes:
> Data stored in the database didn't groove very much in the last months
> (pg_dump still generates a 4GB compressed file), but data files now
> occupies 20 GB on disk.
> I know that a vacuum full or a full dump/restore is needed to reclaim
> filesystem space. However, my question is: how this could have been
> happened?
What do you have max_fsm_pages set to? If it's too small, you'll get
bloat regardless of whether autovac is doing its thing.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Abnormal storage consumption with autovacuum enabled
@ 2007-05-25 08:31 Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 1 reply; 4+ messages in thread
From: Nicola Mauri @ 2007-05-25 08:31 UTC (permalink / raw)
To: pgsql-admin
> What do you have max_fsm_pages set to?
Currently, max_fsm_pages=20000
Running VACUUM VERBOSE reports:
NOTICE: number of page slots needed (237312) exceeds max_fsm_pages (20000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 237312.
Is this measurement reliable?
We are considering to set: max_fsm_pages=400000
Could this have some sort of impact?
thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Abnormal storage consumption with autovacuum enabled
@ 2007-05-25 13:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-05-25 13:57 UTC (permalink / raw)
To: Nicola Mauri <nicola.mauri@saga.it>; +Cc: pgsql-admin
Nicola Mauri <nicola.mauri@saga.it> writes:
>> What do you have max_fsm_pages set to?
> Currently, max_fsm_pages=20000
> Running VACUUM VERBOSE reports:
> NOTICE: number of page slots needed (237312) exceeds max_fsm_pages (20000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 237312.
Yup, there's your problem (or a huge chunk of it anyway).
> Is this measurement reliable?
> We are considering to set: max_fsm_pages=400000
> Could this have some sort of impact?
It will require a slightly larger shared-memory segment than you have
now.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-25 13:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-24 15:56 Abnormal storage consumption with autovacuum enabled Nicola Mauri <nicola.mauri@saga.it>
2007-05-24 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 08:31 Re: Abnormal storage consumption with autovacuum enabled Nicola Mauri <nicola.mauri@saga.it>
2007-05-25 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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