Hi, that looks like the query being run for accurate backup. I wonder why it's slower after the upgrade, as it's one table less for the join. This kind of problem with accurate has also been seen with the Bareos 16.2 DB schema in MySQL.
Please check if the indexes on the File table match the ones defined in /usr/lib/bareos/scripts/ddl/creates/mysql.sql May be do ANALYZE TABLE File; in mysql again. If that does not help, disabling accurate for that job meanwhile may be better than killing the query. Regards, Stephan On 01/22/2018 02:30 PM, jsfre...@ludia.com wrote: > Hi, After the upgrade to 17.2.4 (21 Sep 2017) I noticed the following sql > query: > > SELECT Path.Path, T1.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq , Fhinfo, > Fhnode FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS > PathId, File.Name AS Name, LStat , DeltaSeq, Fhinfo, Fhnode, Job.JobTDate AS > JobTDate FROM Job, File, (SELECT MAX(JobTDate) AS JobTDate, PathId, FileName > FROM (SELECT JobTDate, PathId, File.Name AS FileName FROM File JOIN Job USING > (JobId) WHERE File.JobId IN (2897,2941,2963,3005,3023,3041) UNION ALL SELECT > JobTDate, PathId, File.Name AS FileName FROM BaseFiles JOIN File USING > (FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN > (2897,2941,2963,3005,3023,3041) ) AS tmp GROUP BY PathId, FileName) AS T1 > WHERE (Job.JobId IN (SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN > (2897,2941,2963,3005,3023,3041)) OR Job.JobId IN > (2897,2941,2963,3005,3023,3041)) AND T1.JobTDate = Job.JobTDate AND Job.JobId > = File.JobId AND T1.PathId = File.PathId AND T1.FileName = File.Name ) AS T1 > JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY > T1.JobTDate, FileIndex ASC > > This query is taking a very long time to complete. Now it's been running over > 13 hours and it is still running and taking 100% CPU. > > I'm using mysql 5.5, and the schema was upgraded when upgrading from 16.2 to > 17.2. > > For the last few days I had to kill the queries, but it would be great to > have this fixed. > > Any idea what is wrong here ? > > Thank you. > -- Stephan Dühr stephan.du...@bareos.com Bareos GmbH & Co. KG Phone: +49 221-630693-90 http://www.bareos.com Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646 Komplementär: Bareos Verwaltungs-GmbH Geschäftsführer: S. Dühr, M. Außendorf, J. Steffens, Philipp Storz -- You received this message because you are subscribed to the Google Groups "bareos-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to bareos-users+unsubscr...@googlegroups.com. To post to this group, send email to bareos-users@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
