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