On Mon, 20 Mar 2006 22:31:28 GMT Martin Simmons <[EMAIL PROTECTED]> wrote:
> >>>>> On Mon, 20 Mar 2006 09:35:18 -0700, Michael Morgan <[EMAIL PROTECTED]> > >>>>> said: > > > > I apologize for not posting this resolution sooner, but time has been > > scarce lately. Most of the responses I received pointed to some sort of > > index problem with the bacula database. I talked with my partner and he > > reindexed the database and then set up a cron job to vacuum and reindex > > the database every night. Since then, the problem has been solved. > > > > I can now build a restore directory tree of over 100k files in only a > > second or two. A side benefit is that the backup job itself has been > > reduced from 1.5 hours to just 30 minutes or less. > > > > As a side note, this was not a migrated or upgraded installation, it was > > a new installation. There must be something in the initial tables or > > configuration that does not index properly. Once the indexes were > > cleaned up, everything went much more smoothly. > > That's good hear. It might be the regular vacuum and reindex cron that keeps > it fast. With some versions of PostgreSQL at least, indexes become less > efficient if you delete and insert lots of the rows (like Bacula does with the > File table). On a PostgreSQL database, you should be running frequent vacuums. Depending on how often you backup, you may want to run them nightly after the backup finishes. Doing a "vacuum analyze" should clean up the indexes and keep them nice and fast. (The situation is that PostgreSQL only updates its index statistics when told to. As the database changes, its statistics can get out of date and it can make _really_ bad decisions about fastest way to work with data) On newer versions of PostgreSQL, enable autovacuum in postgresql.conf, and PostgreSQL will monitor database activity and automatically vacuum and analyze when required. This is not always your best bet, however. If you know for a fact that the database is unused during certain periods, it makes sense to manually schedule the vacuum analyze at that time - autovacuum might kick off right in the middle of a backup run. You shouldn't have to reindex. If you find that vacuum analyze isn't enough and you still have to reindex, I suspect the PostgreSQL people would like to hear about it. -- Bill Moran Collaborative Fusion Inc. ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
