Hi,
i have now created the index, the select now takes only a few seconds instead
of more than a minute.
please check and maybe include the index into next bacula version, its a cheap
index, but it helps if you have many jobs (>100000) and use accurate backups.
MariaDB [bacula]> CREATE INDEX jobtdate_idx on Job (JobTDate);
MariaDB [bacula]> explain 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
(1141112,1141820,1142374,1143005) UNION ALL SELECT JobTDate, PathId, FilenameId
FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId)
WHERE BaseFiles.JobId IN (1141112,1141820,1142374,1143005) ) AS tmp GROUP BY
PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM
BaseFiles WHERE JobId IN (1141112,1141820,1142374,1143005)) OR Job.JobId IN
(1141112,1141820,1142374,1143005)) AND T1.JobTDate = Job.JobTDate AND Job.JobId
= File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId )
AS T1 JOIN Filename O
N (Filename.FilenameId = T1.FilenameId) JOIN Path ON (Path.PathId = T1.PathId)
WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC;
+------+--------------+------------+--------+----------------------+--------------+---------+------------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+------+--------------+------------+--------+----------------------+--------------+---------+------------------------------------------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | Job | index | PRIMARY,jobtdate_idx |
jobtdate_idx | 9 | NULL | 193606 |
Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | <derived3> | ref | key0 | key0
| 9 | bacula.Job.JobTDate | 10 |
|
| 1 | SIMPLE | Path | eq_ref | PRIMARY | PRIMARY
| 4 | T1.PathId | 1 |
|
| 1 | SIMPLE | Filename | eq_ref | PRIMARY | PRIMARY
| 4 | T1.FilenameId | 1 |
|
| 1 | SIMPLE | File | ref | JobId,JobId_2 | JobId_2
| 12 | bacula.Job.JobId,T1.PathId,T1.FilenameId | 1 | Using where
|
| 6 | MATERIALIZED | BaseFiles | ALL | basefiles_jobid_idx | NULL
| NULL | NULL | 1 | Using where
|
| 3 | DERIVED | <derived4> | ALL | NULL | NULL
| NULL | NULL | 83357 | Using
temporary; Using filesort |
| 4 | DERIVED | Job | range | PRIMARY | PRIMARY
| 4 | NULL | 4 | Using where
|
| 4 | DERIVED | File | ref | JobId,JobId_2 | JobId_2
| 4 | bacula.Job.JobId | 20839 | Using index
|
| 5 | UNION | BaseFiles | ALL | basefiles_jobid_idx | NULL
| NULL | NULL | 1 | Using where
|
| 5 | UNION | Job | eq_ref | PRIMARY | PRIMARY
| 4 | bacula.BaseFiles.BaseJobId | 1 |
|
| 5 | UNION | File | eq_ref | PRIMARY | PRIMARY
| 8 | bacula.BaseFiles.FileId | 1 |
|
| NULL | UNION RESULT | <union4,5> | ALL | NULL | NULL
| NULL | NULL | NULL |
|
+------+--------------+------------+--------+----------------------+--------------+---------+------------------------------------------+--------+-----------------------------------------------------------+
Best regards
Ulrich
> Ulrich Leodolter <ulrich.leodol...@obvsg.at> hat am 27. April 2017 um 08:37
> geschrieben:
>
>
> Hi,
>
> Every night we see new entries in mysql slow-query log which look like this:
>
> # Query_time: 72.057146 Lock_time: 0.000240 Rows_sent: 1454850
> Rows_examined: 17178331
> SET timestamp=1493248276;
> 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 (1141112,1141820,1142374,1143005) UNION ALL SELECT JobTDate,
> PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON
> (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN
> (1141112,1141820,1142374,1143005) ) AS tmp GROUP BY PathId, FilenameId ) AS
> T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId
> IN (1141112,1141820,1142374,1143005)) OR Job.JobId IN
> (1141112,1141820,1142374,1143005)) AND T1.JobTDate = Job.JobTDate AND
> Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId =
> File.FilenameId ) AS T1 JOIN Filename ON (Filename.FilenameId =
T
> 1.FilenameId) JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0
> ORDER BY T1.JobTDate, FileIndex ASC;
>
> We run Full backup on Saturday followed by 6 Incremental to Disk followed by
> Copy jobs to Tape. Below are a few numbers about our database, the server
> runs Bacula 7.4.7 on CentOS 7.3, 128GB Ram, Intel(R) Xeon(R) CPU E5-2620 v3 @
> 2.40GHz. Database index is almost in memory, InnoDB is used as storage
> engine (one file per table) and i don't see any disk activity while the above
> SELECT is running.
>
> Is the query really slow related to database size, or is it just slow on
> mariadb 5.5.52, or would a new index or modified help?
>
> Maybe an index on Job.JobTDate would help, JobHisto.JobTDate is indexed ?
>
>
> $ du -h -s /var/lib/mysql/bacula/
> 316G /var/lib/mysql/bacula/
>
> MariaDB [bacula]> select count(*) from File;
> +------------+
> | count(*) |
> +------------+
> | 2005237279 |
> +------------+
> MariaDB [bacula]> select count(*) from Filename;
> select cou+----------+
> | count(*) |
> +----------+
> | 50002655 |
> +----------+
> 1 row in set (7.00 sec)
>
> MariaDB [bacula]> select count(*) from Job;
> +----------+
> | count(*) |
> +----------+
> | 189213 |
> +----------+
>
> MariaDB [bacula]> explain 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
> (1141112,1141820,1142374,1143005) UNION ALL SELECT JobTDate, PathId,
> FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId
> = Job.JobId) WHERE BaseFiles.JobId IN (1141112,1141820,1142374,1143005) ) AS
> tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT
> BaseJobId FROM BaseFiles WHERE JobId IN (1141112,1141820,1142374,1143005)) OR
> Job.JobId IN (1141112,1141820,1142374,1143005)) AND T1.JobTDate =
> Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND
> T1.FilenameId = File.FilenameId ) AS T1 JOIN Filename
O
> N (Filename.FilenameId = T1.FilenameId) JOIN Path ON (Path.PathId =
> T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC;
> +------+--------------+------------+--------+---------------------+---------+---------+------------------------------------------+--------+----------------------------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra
> |
> +------+--------------+------------+--------+---------------------+---------+---------+------------------------------------------+--------+----------------------------------------------+
> | 1 | SIMPLE | Job | ALL | PRIMARY | NULL |
> NULL | NULL | 193606 | Using where;
> Using temporary; Using filesort |
> | 1 | SIMPLE | <derived3> | ref | key0 | key0 |
> 9 | bacula.Job.JobTDate | 10 |
> |
> | 1 | SIMPLE | Path | eq_ref | PRIMARY | PRIMARY |
> 4 | T1.PathId | 1 |
> |
> | 1 | SIMPLE | Filename | eq_ref | PRIMARY | PRIMARY |
> 4 | T1.FilenameId | 1 |
> |
> | 1 | SIMPLE | File | ref | JobId,JobId_2 | JobId_2 |
> 12 | bacula.Job.JobId,T1.PathId,T1.FilenameId | 1 | Using where
> |
> | 6 | MATERIALIZED | BaseFiles | ALL | basefiles_jobid_idx | NULL |
> NULL | NULL | 1 | Using where
> |
> | 3 | DERIVED | <derived4> | ALL | NULL | NULL |
> NULL | NULL | 83357 | Using
> temporary; Using filesort |
> | 4 | DERIVED | Job | range | PRIMARY | PRIMARY |
> 4 | NULL | 4 | Using where
> |
> | 4 | DERIVED | File | ref | JobId,JobId_2 | JobId_2 |
> 4 | bacula.Job.JobId | 20839 | Using index
> |
> | 5 | UNION | BaseFiles | ALL | basefiles_jobid_idx | NULL |
> NULL | NULL | 1 | Using where
> |
> | 5 | UNION | Job | eq_ref | PRIMARY | PRIMARY |
> 4 | bacula.BaseFiles.BaseJobId | 1 |
> |
> | 5 | UNION | File | eq_ref | PRIMARY | PRIMARY |
> 8 | bacula.BaseFiles.FileId | 1 |
> |
> | NULL | UNION RESULT | <union4,5> | ALL | NULL | NULL |
> NULL | NULL | NULL |
> |
> +------+--------------+------------+--------+---------------------+---------+---------+------------------------------------------+--------+----------------------------------------------+
> 13 rows in set (0.00 sec)
>
>
> Best regards
> Ulrich
Ulrich Leodolter <ulrich.leodol...@obvsg.at>
Oesterreichische Bibliothekenverbund und Service GmbH
Raimundgasse 1/3, A-1020 Wien
Fax +43 1 4035158-30
Tel +43 1 4035158-21
Web http://www.obvsg.at
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
