Re: database bloat, but vacuums are done, and fsm seems to be setup ok

Looks like it's definately an issue with index bloat. Note that it's
normal to have some amount of empty space depending on vacuum and update
frequency, so 15G -> 20G isn't terribly surprising. I would suggest
using pg_autovacuum instead of the continuous vacuum; it's very possible
that some of your tables need more frequent vacuuming than they're
getting now. If you go this route, you might want to change the default
settings a bit to make pg_autovacuum more agressive.

Also, I'd suggest posting to -hackers about the index bloat. Would you
be able to make a filesystem copy (ie: tar -cjf database.tar.bz2
$PGDATA) available? It might also be useful to keep an eye on index size
in pg_class.relpages and see exactly what indexes are bloating.

On Wed, Sep 28, 2005 at 09:07:07AM +0200, hubert depesz lubaczewski wrote:
> hi
> setup:
> postgresql 8.0.3 put on debian on dual xeon, 8GB ram, hardware raid.
>
> database just after recreation from dump takes 15gigabytes.
> after some time (up to 3 weeks) it gets really slow and has to be dump'ed
> and restored.
>
> as for fsm:
> end of vacuum info:
> INFO: free space map: 248 relations, 1359140 pages stored; 1361856 total
> pages needed
> DETAIL: Allocated FSM size: 1000 relations + 10000000 pages = 58659 kB
> shared memory.
>
> so it looks i have plenty of space in fsm.
>
> vacuums run constantly.
> 4 different tasks, 3 of them doing:
> while true
> vacuum table
> sleep 15m
> done
> with different tables (i have chooses the most updated tables in system).
>
> and the fourth vacuum task does the same, but without specifying table - so
> it vacuumes whole database.
>
> after last dump/restore cycle i noticed that doing reindex on all indices in
> database made it drop in side from 40G to about 20G - so it might be that i
> will be using reindex instead of drop/restore.
> anyway - i'm not using any special indices - just some (117 to be exact)
> indices of btree type. we use simple, multi-column, partial and multi-column
> partial indices. we do not have functional indices.
>
> database has quite huge load of updates, but i thought that vacum will guard
> me from database bloat, but from what i observed it means that vacuuming of
> b-tree indices is somewhat faulty.
>
> any suggestions? what else can i supply you with to help you help me?
>
> best regards
>
> depesz

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461