[Bacula-users] restore performance

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:

Select FileSet resource (1-2): 2

+-------+-------+-----------+----------------+---------------------+----------------------------+

+-------+-------+-----------+----------------+---------------------+----------------------------+

| 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.

------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. SALE $99.99 this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122412

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users