[Bacula-bugs] [bacula 0001614]: Bacula hangs when accessing Job List - MySQL Query takes forever to complete

[Mantis B. T. <no...@bu...>]

A NOTE has been added to this issue. 
====================================================================== 
http://bugs.bacula.org/view.php?id=1614 
====================================================================== 
Reported By:                chibisuke
Assigned To:                
====================================================================== 
Project:                    bacula
Issue ID:                   1614
Category:                   bat
Reproducibility:            always
Severity:                   major
Priority:                   normal
Status:                     new
====================================================================== 
Date Submitted:             2010-07-27 08:06 BST
Last Modified:              2010-07-27 08:56 BST
====================================================================== 
Summary:                    Bacula hangs when accessing Job List - MySQL Query
takes forever to complete
Description: 
I'm using bacula in "bigger" enviornment (about 150 servers), and noticed
that now after we're using it for 4 months now, bat takes forever to load
the joblist.

Bat seams to send a DB Query to the database, that takes now 20 minutes to
complete for it to display the joblist. 

I tested modifying the query using a hex editor and succeded in so far,
that it now loads the list fast, but this seams to cause other bad
sideffects.
In fact it looks like the 2 subquerys are the problem. if I replace them
with "1"
the problem is gone.

I'm running bacula-dir itself on MySQL 5.1.43 on opensolaris.

Also I tried finding the bottleneck of the query, but beside removing that
subquerys I wouldn't know what to do else. Anyone else experienced this
problem already maybe? What else can I do helping me to figure out where
this problem comes from?
====================================================================== 

---------------------------------------------------------------------- 
 (0005524) chibisuke (reporter) - 2010-07-27 08:56
 http://bugs.bacula.org/view.php?id=1614#c5524 
---------------------------------------------------------------------- 
The query executed is:
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 Volumed 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 ORDER BY Job.JobId DESC LIMIT 150;

Query Exectution Plan:
|  1 | PRIMARY            | Client   | ALL    | PRIMARY       | NULL     |
NULL    | NULL                    |  117 | Using temporary; Using filesort
|
|  1 | PRIMARY            | Job      | ref    | ClientId      | ClientId |
5       | bacula.Client.ClientId  |   34 | 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        | 4520 | 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 |                                
| 

Issue History 
Date Modified    Username       Field                    Change               
====================================================================== 
2010-07-27 08:06 chibisuke      New Issue                                    
2010-07-27 08:56 chibisuke      Note Added: 0005524                          
======================================================================