Re: [Bacula-users] Exceeding realistic expectations for accurate mode?

Hi Martin,

On Wed, 19 Feb 2014, Martin Simmons wrote:

>>>>>> On Wed, 19 Feb 2014 12:57:15 -0500 (EST), rfox  said:
>>
>>                                   The query that hangs everything up
>> during the process takes more than 24 hours to get MySQL to 'explain' and
>> jobs take about 1.7 days to run at this point. (A job is running now and
>> the same query has been executing for over 62000 seconds.) I am using
>> MySQL 5.5 though, so I may be able to benefit from a more recent version.
> 
> It would be useful to know which SQL query that is.

Each job executes one of these that is very similar. This is the one that's 
currently running:

SELECT Path.Path, Filename.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq
   FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, 
File.FilenameId AS FilenameId, LStat , DeltaSeq, Job.JobTDate AS JobTDate 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 
(2,50,54,66,72,76,80,84,88,92,99,103,107,111,115,119,123,127,131,135,139,143,147,151,155,163,165,181)
 
UNION ALL SELECT Jo bTDate, PathId, FilenameId FROM BaseFiles JOIN File USING 
(FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN 
(2,50,54,66,72,7 
6,80,84,88,92,99,103,107,111,115,119,123,127,131,135,139,143,147,151,155,163,165,181)
 
) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT 
DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN 
(2,50,54,66,72,76,80,84,88,92,99,103,107,111,115,119,123,127,131,135,139,143,147,151,15
 
5,163,165,181)) OR Job.JobId IN 
(2,50,54,66,72,76,80,84,88,92,99,103,107,111,115,119,123,127,131,135,139,143,147,151,155,163,165,181))
 
AND T1.JobTDa te = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = 
File.PathId AND T1.FilenameId = File.FilenameId ) AS T1 JOIN Filename ON 
(Filename.File nameId = T1.FilenameId) JOIN Path ON (Path.PathId = T1.PathId) 
WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC

Thanks,
Rich.

-- 
  Rich Fox
  Systems Administrator
  JBPC - Marine Biological Laboratory
  http://www.mbl.edu/jbpc
  508-289-7669 - mbl-at-richfox.org

------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users