pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Abnormal 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