On Mon, Jan 14, 2013 at 01:10:59PM +0000, Antony Mayi wrote: > thanks Uwe, good hint. I run the repair on all tables and restore works like > a charm now! > thanks a lot, > Antony. >
You're welcome, I've fallen into the same trap before ;) Cheers, Uwe > > > >________________________________ > > From: Uwe Schuerkamp <uwe.schuerk...@nionex.net> > >To: Antony Mayi <antonym...@yahoo.com> > >Cc: "bacula-users@lists.sourceforge.net" > ><bacula-users@lists.sourceforge.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 > > > > > > > > > > -- 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
