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
step...@seismo.berkeley.edu 215 McCone Hall # 4760
404.538.7077 (phone) University of California, Berkeley
510.643.5811 (fax) Berkeley, CA 94720-4760
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
