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