Re: [GENERAL] database bloat, but vacuums are done, and fsm seems to be setup ok
On 9/30/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: Looks like it's definately an issue with index bloat. Note that it'snormal to have some amount of empty space depending on vacuum and updatefrequency, so 15G -> 20G isn't terribly surprising. I would suggest using pg_autovacuum instead of the continuous vacuum; it's very possiblethat some of your tables need more frequent vacuuming than they'regetting now. If you go this route, you might want to change the default settings a bit to make pg_autovacuum more agressive. actually i have a very bad experience with autovaccum - of course it is because i dont know how to setup it correctly, but for me it's just easier to setup continuos vacuums. and i know which tables are frequently updated, so i setup additional vacuums on them. Also, I'd suggest posting to -hackers about the index bloat. Would yoube 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 sizein pg_class.relpages and see exactly what indexes are bloating. i'm watching it right now (which indices are bloating), but i cannot send copy of pgdata - it contains very sensitive information. depesz
Re: [GENERAL] 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 + 1000 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] database bloat, but vacuums are done, and fsm seems to be setup ok
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 + 1000 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
