---

From: Uwe Schuerkamp <uwe.schuerkamp AT nionex DOT net>
To: Antony Mayi <antonymayi AT yahoo DOT com>
Cc: "bacula-users AT lists.sourceforge DOT net" <bacula-users AT lists.sourceforge DOT net>
Sent: Monday, 14 January 2013, 12:45
Subject: Re: [Bacula-users] restore performance

On Mon, Jan 14, 2013 at 11:38:04AM +0000, Antony Mayi wrote:
> Hi guys,
>
> I seem to have major performance problem with the director/catalog when trying restoration. Using MySQL, the catalog has about 200MB, mysql is using innodb with large enough buffer pool to keep all data in memory. there is no iowait. when I attempt to do a restore using bconsole I get:
>
> <snippet>
> Select FileSet resource (1-2): 2
> +-------+-------+-----------+----------------+---------------------+----------------------------+
> | JobId | Level | JobFiles  | JobBytes       | StartTime           | VolumeName                 |
> +-------+-------+-----------+----------------+---------------------+----------------------------+
> |   341 | F     | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1212000-full.vol03 |
> |   341 | F     | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol00 |
> |   341 | F     | 1,325,536 | 10,264,188,487 | 2013-01-06 23:32:14 | Offsite-1301000-full.vol01 |
> |   352 | I     |     1,142 |     12,445,396 | 2013-01-07 23:42:58 | Offsite-1212502-incr.vol00 |
> |   363 | I     |     1,127 |     12,098,952 | 2013-01-08 23:38:43 | Offsite-1212503-incr.vol00 |
> |   374 | I     |     1,779 |     10,246,830 | 2013-01-09 23:44:54 | Offsite-1212504-incr.vol00 |
> |   385 | I     |     1,520 |     16,334,830 | 2013-01-10 23:27:38 | Offsite-1212494-incr.vol00 |
> |   396 | I     |     1,882 |     12,371,311 | 2013-01-11 23:27:28 | Offsite-1212495-incr.vol00 |
> +-------+-------+-----------+----------------+---------------------+----------------------------+
> To select the JobIds, you have the following choices:
> ...
>      3: Enter list of comma separated JobIds to select
> ...
> Select item:  (1-13): 3
>
> Enter JobId(s), comma separated, to restore: 341
> You have selected the following JobId: 341
>
> Building directory tree for JobId(s) 341 ...  
>
> </snippet>
>
>
> this "building directory tree" runs already 2 hours. mysql is taking 100% CPU (on single core of 4 available - Intel Xeon 2.4GHz). it is running following query:
>
> SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN (341) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job  ON    (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (341) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (341)) OR Job.JobId IN (341)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE FileIndex > 0 ORDER BY Temp.JobId, FileIndex ASC
>
>
> what can I do to make the restore function useful (ie. working in reasonable time)? I don't see many options in boosting the HW.
>
> thanks,
> Antony.

Have you tried "repair table File" and checked for broken indices &
the like?

All the best, Uwe


--
NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA