Quantcast

Incremental accurate backup - slow query

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Incremental accurate backup - slow query

Ulrich Leodolter-2
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 <[hidden email]>
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Incremental accurate backup - slow query

Ulrich Leodolter-2
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 <[hidden email]> 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 <[hidden email]>
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Incremental accurate backup - slow query

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