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.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
> >
> >
> >
> >
> >
--
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|