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 <[email protected]>
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
