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
|