> > > > This sounds like either table or index bloat. Typical > > > reasons for tihs > > > > are not doing vacuum (which obviously isn't your problem), > > > or having > > > > too few FSM pages. This can also be caused by not > running vacuum > > > > earlier, but doing it now - if you got far enough away from > > > the good > > > > path you'll need a VACUUM FULL to recover. > > > > > > I get crazy index bloat with PostgreSQL 7.3.4 but running VACUUM > > > FULL ANALYZE once a week keeps it mostly under control. At least > > > I'm assuming it is index bloat, because running VACUUM > ANALYZE once > > > a week didn't fix it but droping and recreating the indexes does. > > > > In a properly configured database, you should never have to do a > > VACUUM FULL. It can be needed if you do one-time operations (say > > delete 80% of a huge table), but never in normal operation. > > I don't claim to have a properly configured database :-)
:-) > OTOH, at least 15% of the 9 million rows in the File table > are deleted (by > pruning) and reinserted (by backup) every weekend. Within 2 > months, almost 100% of the rows will have been deleted and reinserted. So run a VACUUM after each of those. Or just run a nightly VACUUM (or maybe daily if you run the backups at night, but you get the idea). 15% is fairly much, but that just means you need higher max_fsm_pages (maybe, depends on db size). > > Also, you really shouldn't be running 7.3.4. If you for some reason > > absolutely need to stick with 7.3, you should absolutely be > on 7.3.13. > > But if you can do anything about it, move up to 8.1 - or at > least 8.0. > > (which of course means 8.1.2 or 8.0.6, *always* go for the latest > > release in a stable series) > > > > There are plenty of improvements in those, and quite a lot around > > VACUUM. And if you are having problems with index bloat, > most (if not > > all) of those were fixded in 7.4. So you really want to look at > > upgrading. > > It is mainly inertia and DFIIIAB syndrome, though I'm sure > you know lots of reasons why it is broken! The db is only > used for Bacula. Heh. Well, for the 7.3 branch that's just plain bugfixes, so it's certainly broken in some ways. As for the newer versions, well, they solve your index bloat proble most likely but it's in general just new features and optimisations that you're missing out on. Consider Bacula is a very simple user of the database, the upgrade really shouldn't be hard. I would definitly suggest it, there are plenty of improvements that will help you. //Magnus ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
