Le mardi 23 janvier 2018 16:26:47 UTC-5, Stephan Duehr a écrit :
> 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, [email protected] 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 [email protected]
> 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
Hi here are the indexes from the File table with their Cardinality. I did the
analyze (and optimize just to be sure) but nothing changed.
mysql> show index from File;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| File | 0 | PRIMARY | 1 | FileId | A
| 16781276 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_PathId_Name | 1 | JobId | A
| 19 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_PathId_Name | 2 | PathId | A
| 19311 | NULL | NULL | | BTREE | |
|
| File | 1 | JobId_PathId_Name | 3 | Name | A
| 16781276 | 255 | NULL | | BTREE | |
|
| File | 1 | PathId_JobId_FileIndex | 1 | PathId | A
| 2397325 | NULL | NULL | | BTREE | |
|
| File | 1 | PathId_JobId_FileIndex | 2 | JobId | A
| 3356255 | NULL | NULL | | BTREE | |
|
| File | 1 | PathId_JobId_FileIndex | 3 | FileIndex | A
| 16781276 | NULL | NULL | YES | BTREE | |
|
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
And the create table statement:
CREATE TABLE `File` (
`FileId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`FileIndex` int(10) unsigned DEFAULT '0',
`JobId` int(10) unsigned NOT NULL,
`PathId` int(10) unsigned NOT NULL,
`DeltaSeq` smallint(5) unsigned DEFAULT '0',
`MarkId` int(10) unsigned DEFAULT '0',
`Fhinfo` decimal(20,0) DEFAULT '0',
`Fhnode` decimal(20,0) DEFAULT '0',
`LStat` tinyblob NOT NULL,
`MD5` tinyblob NOT NULL,
`Name` blob NOT NULL,
PRIMARY KEY (`FileId`),
KEY `JobId_PathId_Name` (`JobId`,`PathId`,`Name`(255)),
KEY `PathId_JobId_FileIndex` (`PathId`,`JobId`,`FileIndex`)
) ENGINE=InnoDB AUTO_INCREMENT=50769695 DEFAULT CHARSET=utf8
--
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 [email protected].
To post to this group, send email to [email protected].
For more options, visit https://groups.google.com/d/optout.
