Re: [Bacula-users] Bacula Catalog Server - MySQL

Hello, 

actually I debugged some further. I ran ONE backup job, turned on query logging 
on the MariaDB server and the output is for ONE : 

  75 INSERTS (Log and batch and DelCandidates)
  29249 SELECTS
  18 UPDATES

The selects miss some indexes: 

  create index ix_job_jobtdate on Job(JobTDate);
  create index ix_jobmedia_mediaid on JobMedia(MediaId);

After that it all run a little faster, however I still wonder why there are so 
many selects like this: 

---
This loop 576 times: 

SELECT 
PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,ActionOnPurge
 FROM Pool WHERE Pool.Name='POOLNAME'
SELECT count(*) from Media WHERE PoolId=1
SELECT 
MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge
 FROM Media WHERE PoolId=1 AND MediaType='File' AND Enabled=1 AND 
VolStatus='Append'  ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId LIMIT 
1
SELECT 
MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge
 FROM Media WHERE PoolId=1 AND MediaType='File' AND Enabled=1 AND 
VolStatus='Recycle'  AND Recycle=1 ORDER BY LastWritten ASC,MediaId LIMIT 1
SELECT 
MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge
 FROM Media WHERE PoolId=1 AND MediaType='File' AND Enabled=1 AND 
VolStatus='Purged'  AND Recycle=1 ORDER BY LastWritten ASC,MediaId LIMIT 1
SELECT 
MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge
 FROM Media WHERE PoolId=1 AND MediaType='File' AND Enabled=1 AND 
VolStatus='Purged'  AND Recycle=1 ORDER BY LastWritten ASC,MediaId LIMIT 1

Then thousands of lines: 

SELECT 
MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge
 FROM Media WHERE MediaId=78490
SELECT DISTINCT JobMedia.JobId FROM JobMedia,Job WHERE MediaId=78490 AND 
Job.JobId=JobMedia.JobId AND Job.JobTDate<1425817396
SELECT 1 FROM JobMedia WHERE MediaId=78490 LIMIT 1

Background: 

We have 1 Job per File. This means: 

MariaDB [bacula]> select count(*) from JobMedia;
+----------+
| count(*) |
+----------+
|    24929 |
+----------+
1 row in set (0.00 sec)

MariaDB [bacula]> select count(*) from Job;
+----------+
| count(*) |
+----------+
|     9064 |
+----------+
1 row in set (0.00 sec)

Regards, 
Robert 

------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
