Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
> we have a rather large Bacula setup - ~600 Clients, database for catalog is > 19GB large. File table is ~65 million records. > > Right now, bacula director can not run backups as scheduled and delays the > jobs for up to 2 hours, as it seems the catalog is not keeping up. > > CPU usage on SD is: 25% (0,5 Core) > > CPU usage on Dir is: 10% (0,2 Core) > > CPU usage on Catalog is: 25% (1 Core) > > SD has not hit it’s storage IO limit, as I/O Wait is almost zero (24x 10k > spindles in Hardware Raid, doing ~50MB/s avg). > > Also we have defined 20 parallel backup drives doing backups, so drives is > not a blocker I guess. > > It seems that the bacula catalog is CPU bound and that only a single core is > used (of 4). > > How can I parallelize database access with Bacula Director to speed up the > backup process. > If you do not have attribute spooling enabled enable that. Also tune your mysql database. The defaults in a lot of systems assume you have only 64MB of ram or similar. John -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
Hello, >If you do not have attribute spooling enabled enable that. Also tune your >mysql database. The defaults in a lot of systems assume you have only 64MB of >ram or similar. Database already is on innodb and fast BBU supported storage and has "innodb_buffer_size" > dataset size, so everything comes from memory. Also I don't see many IO's. Also the catalog has < 1% IO wait during the heavy job times at night, while we have 25% (1 core) of user CPU. How much space do I need in the working directory of the SD to do attribute spooling ? Regards, Robert -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
> How much space do I need in the working directory of the SD to do attribute > spooling ? I expect for your dataset less than 1GB. John -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
One more question for clarification: Attribute spooling only solves "insert and update issues" and not select issues right ? Our monitoring shows that we only have 10 -20 inserts/s for the DB but the selects are capped at ~370 selects/second during peak times. Regards, Robert -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
> One more question for clarification: > > Attribute spooling only solves "insert and update issues" and not select > issues right ? > Correct. > > Our monitoring shows that we only have 10 -20 inserts/s for the DB but the > selects are capped at ~370 selects/second during peak times. > I would expect very few selects during a backup and very many inserts. Every single file that is backed up should be an insert. With attribute spooling this changes into a batch insert after the job has finished. John -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
On 3/24/2015 12:57 PM, Robert Heinzmann wrote: > One more question for clarification: > > Attribute spooling only solves "insert and update issues" and not select > issues right ? > > Our monitoring shows that we only have 10 -20 inserts/s for the DB but the > selects are capped at ~370 selects/second during peak times. Look into enabling innodb_file_per_table setting. InnoDB uses 4 threads as i/o threads in addition to the connection manager thread and one thread per connection. Each table in a separate file means that indices on those tables are also separated and may allow for more parallel i/o. But still, attribute spooling is needed to optimize DB transactions. -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
IMHO I know that there are several MySQL tunings that can be made, but one that I tested that really improves the writing times in a factor of more than 1000 is the enabling of synchronous mode transactions: innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT Regards, == Heitor Medrado de Faria - LPIC-III | ITIL-F 01 a 13 de Abril - Novo Treinamento Telepresencial Bacula: http://www.bacula.com.br/?p=2174 61 8268-4220 Site: www.bacula.com.br | Facebook: heitor.faria | Gtalk: heitorfa...@gmail.com === - Mensagem original - > De: "Josh Fisher" > Para: bacula-users@lists.sourceforge.net > Enviadas: Terça-feira, 24 de março de 2015 14:16:06 > Assunto: Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ? > > > > On 3/24/2015 12:57 PM, Robert Heinzmann wrote: > > One more question for clarification: > > > > Attribute spooling only solves "insert and update issues" and not select > > issues right ? > > > > Our monitoring shows that we only have 10 -20 inserts/s for the DB but the > > selects are capped at ~370 selects/second during peak times. > > Look into enabling innodb_file_per_table setting. InnoDB uses 4 threads > as i/o threads in addition to the connection manager thread and one > thread per connection. Each table in a separate file means that indices > on those tables are also separated and may allow for more parallel i/o. > But still, attribute spooling is needed to optimize DB transactions. > > > -- > Dive into the World of Parallel Programming The Go Parallel Website, > sponsored > by Intel and developed in partnership with Slashdot Media, is your hub for > all > things parallel software development, from weekly thought leadership blogs to > news, videos, case studies, tutorials and more. Take a look and join the > conversation now. http://goparallel.sourceforge.net/ > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
On 03/24/15 11:47, Robert Heinzmann wrote: > Hello, > > we have a rather large Bacula setup - ~600 Clients, database for catalog > is 19GB large. File table is ~65 million records. > > Right now, bacula director can not run backups as scheduled and delays > the jobs for up to 2 hours, as it seems the catalog is not keeping up. > > SD has not hit it’s storage IO limit, as I/O Wait is almost zero (24x > 10k spindles in Hardware Raid, doing ~50MB/s avg). > > Also we have defined 20 parallel backup drives doing backups, so drives > is not a blocker I guess. > > It seems that the bacula catalog is CPU bound and that only a single > core is used (of 4). > > How can I parallelize database access with Bacula Director to speed up > the backup process. If you have queries being executed serially by only a single connection, then they cannot be parallelized across multiple cores, because MySQL can use only a single core on a single query (trying to parallelize below the query level doesn't work out well). I don't know whether the Bacula director's DB accesses *could* be parallelized, but I suspect many of them cannot. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: 603.293.8485 -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
Hello, If you have Attribute Spooling turned off, you will essentially insert records into the database one at a time. The database is locked around each insert so even though multiple threads are running they cannot run at the same time. If you have a lot of files that are being backed up by each job, the DB insertions of attributes will be very slow. If you have Attribute Spooling turned on, multiple simultaneous threads will be used to insert attributes (metadata) into the database, and that will be done in big batches. Providing the Jobs are not referencing the same data (most likely) there will be no global locks so multiple DB inserts can run at the same time and they can be quite efficient. For best performance the DB must be tuned as the default MySQL values are not optimal for Bacula. Best regards, Kern On 15-03-24 11:18 AM, Phil Stracchino wrote: > On 03/24/15 11:47, Robert Heinzmann wrote: >> Hello, >> >> we have a rather large Bacula setup - ~600 Clients, database for catalog >> is 19GB large. File table is ~65 million records. >> >> Right now, bacula director can not run backups as scheduled and delays >> the jobs for up to 2 hours, as it seems the catalog is not keeping up. >> >> SD has not hit it’s storage IO limit, as I/O Wait is almost zero (24x >> 10k spindles in Hardware Raid, doing ~50MB/s avg). >> >> Also we have defined 20 parallel backup drives doing backups, so drives >> is not a blocker I guess. >> >> It seems that the bacula catalog is CPU bound and that only a single >> core is used (of 4). >> >> How can I parallelize database access with Bacula Director to speed up >> the backup process. > If you have queries being executed serially by only a single connection, > then they cannot be parallelized across multiple cores, because MySQL > can use only a single core on a single query (trying to parallelize > below the query level doesn't work out well). I don't know whether the > Bacula director's DB accesses *could* be parallelized, but I suspect > many of them cannot. > > -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
On 03/25/15 02:46, Kern Sibbald wrote: > For > best performance the DB must be tuned as the default MySQL values are > not optimal for Bacula. Honestly, compiled-in MySQL defaults are far from optimal for almost anything. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: 603.293.8485 -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
And there is always the my.cnf configurator from the nice folks at Percona. No that isn't a plug for them, I don't work for them, but use the tool. https://tools.percona.com/wizard On 03/25/2015 04:21 AM, Phil Stracchino wrote: > On 03/25/15 02:46, Kern Sibbald wrote: >> For >> best performance the DB must be tuned as the default MySQL values are >> not optimal for Bacula. > > Honestly, compiled-in MySQL defaults are far from optimal for almost > anything. > > -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Bacula Catalog Server - MySQL - 1 Core Used Max ?
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
