[Bacula-users] Very slow interactive restore

Dear bacula users,

I am using bacula 3.0.2 with mysql on a 4 core Xeon machine with 4 GB
RAM. mysql is used locally, bacula database is about 40GB containing
about 180 million files.

First of all: bacula does a great job for over 1 year now! Thanks a lot
to Kern and the community!

Most often I need to restore whole directories (recursive) out of the
last backup or using a backup some time ago. Because there is no bacula
command for restores of directories, I use the interactive restore method.
When selecting a job that contains about 10 Mio. files, building the
directory tree lasts about 30 minutes -- which is very long.

I tracked down the SQL-select statement that bconsole uses to select the
millions of files, leading to

SELECT Path.Path, Filename.Name, File.FileIndex, File.JobId, File.LStat
FROM (
        SELECT max(FileId) as FileId, PathId, FilenameId
          FROM (
                SELECT FileId, PathId, FilenameId
                FROM File
                WHERE JobId IN (jobid1, jobid2, jobid3)
        ) AS F GROUP BY PathId, FilenameId
) AS Temp
JOIN Filename ON (Filename.FilenameId = Temp.FilenameId)
JOIN Path ON (Path.PathId = Temp.PathId)
JOIN File ON (File.FileId = Temp.FileId)
WHERE File.FileIndex > 0 ORDER BY JobId, FileIndex ASC

Sending this statement to mysql lasts about 20 minutes -- it seems that
the database structure or the indexes are not optimal. I tried with the
4 commented additional indexes in make_mysql_tables .. the performance
even got worser.

So I have a few questions:
1.) Any hints how to recover a directory recursive without using
interactive mode?
2.) Any hints concering mysql optimizations for a bacula database as big
as mine?
3.) What are your directory building times when using interactive restores?

-- 
Kind regards
Christoph
________________________________________________________________________
Christoph Litauer                  litauer AT uni-koblenz DOT de
Uni Koblenz, Computing Center,     http://www.uni-koblenz.de/~litauer
Postfach 201602, 56016 Koblenz     Fon: +49 261 287-1311, Fax: -100 1311
PGP-Fingerprint: F39C E314 2650 650D 8092 9514 3A56 FBD8 79E3 27B2


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
trial. Simplify your report design, integration and deployment - and focus on 
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users