Thread: Abnormal storage consumption with autovacuum enabled

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

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

> 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

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