Re: [Bacula-users] Slow marking of files to be restored

-------- Original Message --------

Subject:	Re: [Bacula-users] Slow marking of files to be restored
Date:	Fri, 01 Jun 2012 10:26:04 +0200
From:	Igor Blazevic <iblazevic AT nimium DOT hr>
To:	Martin Simmons <martin AT lispworks DOT com>

On 31.05.2012 20:49, Martin Simmons wrote:
> What are you comparing here?  The old catalog data reloaded into a fresh
> catalog or new data from a new backup?

Old catalog created with old version of Bacula (v2) and then imported in 
new version of MySQL with new catalog which was created with 5.0.3 
version of Bacula. Both catalogs are now under 5.0.3 version of Bacula. 
So now I have two catalogs, old and new. Old one gives me slow times 
while trying the 'mark *' while the new one works great, same amount of 
files are marked in seconds.


> Can you give an example of the SELECT queries?
>


Here is a quote from the log file:
"""
...
120514  9:31:53     108 Query       SELECT 
ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention FROM Client 
WHERE Client.Name='KEKS-fd'
                    108 Query       SELECT DISTINCT FileSet.FileSet 
FROM Job,Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId AND 
Job.ClientId=3 AND Client.ClientId=3 ORDER BY
 FileSet.FileSet
                    108 Query       SELECT 
FileSetId,FileSet,MD5,CreateTime FROM FileSet WHERE FileSet='KEKS' ORDER 
BY CreateTime DESC LIMIT 1
                    108 Query       INSERT INTO temp1 SELECT 
Job.JobId,JobTdate FROM Client,Job,JobMedia,Media,FileSet WHERE 
Client.ClientId=3 AND Job.ClientId=3 AND Job.StartTime
< '2012-05-14 09:31:04' AND Level='F' AND JobStatus IN ('T','W') AND 
Type='B' AND JobMedia.JobId=Job.JobId AND Media.Enabled=1 AND 
JobMedia.MediaId=Media.MediaId AND Job.FileSetI
d=FileSet.FileSetId AND FileSet.FileSet='KEKS' ORDER BY Job.JobTDate 
DESC LIMIT 1
                    108 Query       INSERT INTO temp SELECT 
Job.JobId,Job.JobTDate,Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes,StartTime,VolumeName,JobMedia.StartFile,VolSess
ionId,VolSessionTime FROM temp1,Job,JobMedia,Media WHERE 
temp1.JobId=Job.JobId AND Level='F' AND JobStatus IN ('T','W') AND 
Type='B' AND Media.Enabled=1 AND JobMedia.JobId=Job.Job
Id AND JobMedia.MediaId=Media.MediaId
                    108 Query       SELECT * FROM temp1
                    108 Query       INSERT INTO temp SELECT 
Job.JobId,Job.JobTDate,Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes,Job.StartTime,Media.VolumeName,JobMedia.StartFi
le,Job.VolSessionId,Job.VolSessionTime FROM Job,JobMedia,Media,FileSet 
WHERE Job.JobTDate>1336349491 AND Job.StartTime<'2012-05-14 09:31:04' 
AND Job.ClientId=3 AND JobMedia.JobId=
Job.JobId AND Media.Enabled=1 AND JobMedia.MediaId=Media.MediaId AND 
Job.Level='D' AND JobStatus IN ('T','W') AND Type='B' AND 
Job.FileSetId=FileSet.FileSetId AND FileSet.FileSet=
'KEKS' ORDER BY Job.JobTDate DESC LIMIT 1
                    108 Query       SELECT * FROM temp
                    108 Query       INSERT INTO temp SELECT 
Job.JobId,Job.JobTDate,Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes,Job.StartTime,Media.VolumeName,JobMedia.StartFi
le,Job.VolSessionId,Job.VolSessionTime FROM Job,JobMedia,Media,FileSet 
WHERE Job.JobTDate>1336943524 AND Job.StartTime<'2012-05-14 09:31:04' 
AND Job.ClientId=3 AND Media.Enabled=1
 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND 
Job.Level='I' AND JobStatus IN ('T','W') AND Type='B' AND 
Job.FileSetId=FileSet.FileSetId AND FileSet.FileSet=
'KEKS'
                    108 Query       SELECT DISTINCT JobId,StartTime 
FROM temp ORDER BY StartTime ASC
                    108 Query       SELECT DISTINCT 
JobId,Level,JobFiles,JobBytes,StartTime,VolumeName FROM temp ORDER BY 
StartTime ASC
                    108 Query       DROP TABLE temp
                    108 Query       DROP TABLE temp1
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24732
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24868
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24878
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24879
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24880
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24881
                    108 Query       SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase 
FROM Job WHERE JobId=24882
                    108 Query       SELECT DISTINCT BaseJobId   FROM 
Job JOIN BaseFiles USING (JobId)  WHERE Job.HasBase = 1    AND Job.JobId 
IN (24732,24868,24878,24879,24880,24881,24882)
108 Query       SELECT JobFiles FROM Job WHERE JobId=24732
                    108 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 
(24732,24868,24878,24879,24880,24881,24882) UNION ALL SELECT JobTDate, 
PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job  
ON    (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN 
(24732,24868,24878,24879,24880,24881,24882) ) AS tmp GROUP BY PathId, 
FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM 
BaseFiles WHERE JobId IN (24732,24868,24878,24879,24880,24881,24882)) OR 
Job.JobId IN (24732,24868,24878,24879,24880,24881,24882)) 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
120514  9:32:49     108 Query       SELECT SUM(PurgedFiles) FROM Job 
WHERE JobId IN (24732,24868,24878,24879,24880,24881,24882)
120514  9:33:17     108 Query       SELECT FilenameId FROM Filename 
WHERE Name='red'
                    108 Query       SELECT PathId FROM Path WHERE 
Path='/bin/'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=5943 AND File.FilenameId=29813
120514  9:33:18     108 Query       SELECT FilenameId FROM Filename 
WHERE Name='vt100-am'
                    108 Query       SELECT PathId FROM Path WHERE 
Path='/lib/terminfo/v/'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=284195 AND File.FilenameId=6729
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='vt200'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=284195 AND File.FilenameId=6739
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='dmevent_tool'
                    108 Query       SELECT PathId FROM Path WHERE 
Path='/sbin/'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=1273679
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='e2fsck'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=50
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='e2label'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=23
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='fsck.ext3'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=12
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='fsck.ext4'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=1729774
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='fsck.ext4dev'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=1729773
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='mke2fs'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=44
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='mkfs.ext2'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=103
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='mkfs.ext4'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=1729746
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='mkfs.ext4dev'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3 AND File.FilenameId=1729760
                    108 Query       SELECT FilenameId FROM Filename 
WHERE Name='aclocal-1.11'
                    108 Query       SELECT PathId FROM Path WHERE 
Path='/usr/bin/'
                    108 Query       SELECT FileId, LStat, MD5 FROM File 
WHERE File.JobId=24732 AND File.PathId=3963 AND File.FilenameId=1796562

... and so on ...

"""

This was from the selection of FD till the 'mark *' command at 9:32:49. 
As mentioned before, building of the tree is done quite fast, in the 
same amount of time as building of a tree from a new catalog. Only the 
marking is taking considerable amount of time.



-- 

Igor Blažević

-- 

Igor Blažević
Nimium d.o.o., Gredička 3, HR-10000 Zagreb, Hrvatska
Tel: +385 1 4852 639
Fax: +385 1 4852 640
Mob: +385 99 820 91 41

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

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