[Bacula-users] mysql innodb plan selection for Jobs Run query changes over time

[Stephen T. <st...@se...>]

Hello all,

I've noticed recently (don't know when this started) that when using BAT 
the response time for the Jobs Run feature are slower over time and I 
see that the query is changing plans in MySQL InnoDB.

What I'm not sure is why and if I can correct this.

As an example, if my database is recently started, a day's worth of 
activity (even 1000+ entries) will return in less than a second, whereas 
a few days later, the same query will take 2-3 minutes to complete.

Any ideas?



Here is my explain query output.  The plan changes in the 2nd row from 
the bottom, where 'type' changes from 'ref' to 'index' and 'Extra' 
changes from 'Using where; Using index; Using filesort' to 'Using 
where'.  I gather this is the database trying to improve things, but 
it's making the results significantly slower.


Fast results...

EXPLAIN SELECT Job.JobId AS JobId, Job.Name AS JobName,  Client.Name AS 
Client, Job.Starttime AS JobStart, Job.Type AS JobType, Job.Level AS 
BackupLevel, Job.Jobfiles AS FileCount, Job.JobBytes AS Bytes, 
Job.JobStatus AS Status, Job.PurgedFiles AS Purged, FileSet.FileSet, 
Pool.Name AS Pool, (SELECT Media.VolumeName FROM JobMedia JOIN Media ON 
JobMedia.MediaId=Media.MediaId WHERE JobMedia.JobId=Job.JobId ORDER BY 
JobMediaId LIMIT 1) AS FirstVolume, (SELECT count(DISTINCT MediaId) FROM 
JobMedia WHERE JobMedia.JobId=Job.JobId) AS Volumes FROM Job JOIN Client 
ON (Client.ClientId=Job.ClientId) LEFT OUTER JOIN FileSet ON 
(FileSet.FileSetId=Job.FileSetId)  LEFT OUTER JOIN Pool ON Job.PoolId = 
Pool.PoolId  WHERE Job.Starttime > '2012-06-13T10:44:32' ORDER BY 
Job.JobId DESC;
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+------------------------------------------+
| id | select_type        | table    | type   | possible_keys | key 
| key_len | ref                     | rows  | Extra 
                |
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+------------------------------------------+
|  1 | PRIMARY            | Job      | index  | NULL          | PRIMARY 
| 4       | NULL                    | 40715 | Using where 
                |
|  1 | PRIMARY            | Client   | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.ClientId     |     1 | Using where 
                |
|  1 | PRIMARY            | FileSet  | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.FileSetId    |     1 | 
                |
|  1 | PRIMARY            | Pool     | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.PoolId       |     1 | 
                |
|  3 | DEPENDENT SUBQUERY | JobMedia | ref    | JobId         | JobId 
| 4       | bacula.Job.JobId        |     4 | Using index 
                |
|  2 | DEPENDENT SUBQUERY | JobMedia | ref    | JobId         | JobId 
| 4       | bacula.Job.JobId        |     4 | Using where; Using index; 
Using filesort |
|  2 | DEPENDENT SUBQUERY | Media    | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.JobMedia.MediaId |     1 | 
                |
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+------------------------------------------+


Slow results...


EXPLAIN SELECT Job.JobId AS JobId, Job.Name AS JobName,  Client.Name AS 
Client, Job.Starttime AS JobStart, Job.Type AS JobType, Job.Level AS 
BackupLevel, Job.Jobfiles AS FileCount, Job.JobBytes AS Bytes, 
Job.JobStatus AS Status, Job.PurgedFiles AS Purged, FileSet.FileSet, 
Pool.Name AS Pool, (SELECT Media.VolumeName FROM JobMedia JOIN Media ON 
JobMedia.MediaId=Media.MediaId WHERE JobMedia.JobId=Job.JobId ORDER BY 
JobMediaId LIMIT 1) AS FirstVolume, (SELECT count(DISTINCT MediaId) FROM 
JobMedia WHERE JobMedia.JobId=Job.JobId) AS Volumes FROM Job JOIN Client 
ON (Client.ClientId=Job.ClientId) LEFT OUTER JOIN FileSet ON 
(FileSet.FileSetId=Job.FileSetId)  LEFT OUTER JOIN Pool ON Job.PoolId = 
Pool.PoolId  WHERE Job.Starttime > '2012-06-13T10:44:32' ORDER BY 
Job.JobId DESC;
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| id | select_type        | table    | type   | possible_keys | key 
| key_len | ref                     | rows  | Extra       |
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+-------------+
|  1 | PRIMARY            | Job      | index  | NULL          | PRIMARY 
| 4       | NULL                    | 47306 | Using where |
|  1 | PRIMARY            | Client   | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.ClientId     |     1 | Using where |
|  1 | PRIMARY            | FileSet  | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.FileSetId    |     1 |             |
|  1 | PRIMARY            | Pool     | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.Job.PoolId       |     1 |             |
|  3 | DEPENDENT SUBQUERY | JobMedia | ref    | JobId         | JobId 
| 4       | bacula.Job.JobId        |    57 | Using index |
|  2 | DEPENDENT SUBQUERY | JobMedia | index  | JobId         | PRIMARY 
| 4       | NULL                    |     1 | Using where |
|  2 | DEPENDENT SUBQUERY | Media    | eq_ref | PRIMARY       | PRIMARY 
| 4       | bacula.JobMedia.MediaId |     1 |             |
+----+--------------------+----------+--------+---------------+---------+---------+-------------------------+-------+-------------+



thanks,
Stephen
-- 
Stephen Thompson               Berkeley Seismological Laboratory
st...@se...    215 McCone Hall # 4760
404.538.7077 (phone)           University of California, Berkeley
510.643.5811 (fax)             Berkeley, CA 94720-4760