I just ran into this. After a Google search, I turned up an article that says
the indices that used to make Bacula run faster now cause a performance problem
with recent versions of Bacula and recent versions of MySQL (it's on the Bacula
wiki, the address for which I don't have handy). I removed all of the indices I
had on the file table and restores only take a few minutes now.
From: Antony Mayi [mailto:antonym...@yahoo.com]
Sent: Monday, January 14, 2013 06:38 AM
To: bacula-users@lists.sourceforge.net <bacula-users@lists.sourceforge.net>
Subject: [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:
<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.
------------------------------------------------------------------------------
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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
