Hello Ulrich, This is just to let you know that I have added the Job index on JobTDate that you suggested in the next major version of Bacula (9.0.0) due to be released in June 2017. Thanks for the good idea.
For others who read this. Please be very careful about adding indexes particularly on the File table. In this case, the new index has no adverse interactions. In other cases, adding a new index can speed up a particular search or insert, but will often have very negative consequences for other operations. Best regards, Kern On 04/27/2017 08:55 AM, Ulrich Leodolter wrote: > 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 Filenam e > 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 > ------------------------------------------------------------------------------ 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
