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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
