On Thu, 10 Aug 2006, Jeffrey L. Taylor wrote: > On an unmodified Bacula system, dbcheck can run for days.
Literally. > And it > locks the database so no backups or restores can be done until it > finishes. Look in the archives for this list about adding indexes. > There are two or three indexes that can be added. Add these ones: idx_jobid, for JobId in File idx_pathid, for PathId in File idx_filenameid for FilenameId in File I know there's a 3-way index, but it doesn't seem to work. The queries for orphan files, paths and filenames will run ok once this is done (cut runtime down from 3 days to about 20 minutes for over 100 million entries) BUT: dbcheck generates a lit of entries to delete, then attempts to do them one at a time - this is _extremely_ slow. > These additions cut > the runtime for over two days on my SOHO system with 5 computers to a > few minutes. The queries can be done manually, but you MUST have MySQL newer than 4.1.0 Checking orphan files: SELECT COUNT(*) FROM File WHERE JobId NOT IN ( SELECT JobId FROM Job ) deleting orphan files: DELETE FROM File WHERE JobId NOT IN ( SELECT JobId FROM Job) Checking orphan paths: SELECT COUNT(*) FROM Path WHERE PathId NOT IN ( SELECT PathId FROM File ) Deleting orphan paths: DELETE FROM Path WHERE PathId NOT IN ( SELECT PathId FROM File ) Checking orphaned filenames: SELECT COUNT(*) FROM Filename WHERE FilenameId NOT IN ( SELECT FilenameId FROM File ) Deleting orphaned filenames DELETE FROM Filename WHERE FilenameId NOT IN ( SELECT FilenameId FROM File ) These subselects are slightly slower than LEFT OUTER JOIN, however you can't use a LEFT OUTER JOIN in a DELETE statement. AB ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
