The ebuild specifically disables batch-insert with MySQL. Why? It causes major performance problems. Without batch-insert each File is inserted with a single insert statement in a separate transactions which leads to horrible disk-thrashing and it takes forever to despool the metadata. I have enabled batch-insert locally and this speeds up the processes enormously! This is with mysql-5.7.23 The installation guide [1] specifically says that this should be enabled by default and that it is always safe to do so with MySQL. [1] http://www.bacula.org/9.2.x-manuals/en/main/Installing_Bacula.html So this whole if/else construct should be replaced by simply always adding "--enable-batch-insert": if use mysql; then myconf="${myconf} \ --disable-batch-insert" else myconf="${myconf} \ --enable-batch-insert" fi
(In reply to Ortwin Glueck from comment #0) > The ebuild specifically disables batch-insert with MySQL. Why? It causes > major performance problems. Without batch-insert each File is inserted with > a single insert statement in a separate transactions which leads to horrible > disk-thrashing and it takes forever to despool the metadata. There was quite some discussion about it on the bacula-devel mailing list in August 2017. Just look for the threads titled "bacula-9.0.2 release" and " Regarding the batch size and attribute spooling". It turned out, that using batch-insert failed on some installations for large backup sets. The solution was to disable it. Further tests on the system in question did NOT show relevant slow down by disabling batch insert. But that may be different on various installations.
I have read that mailing list thread now. It seems its one guy with an exotic setup that has a problem with batches. But this is certainly not the normal case. And using non-batched insert DOES have a considerable performance cost! Little example from our production backup: 18-Nov 09:20 backup-sd JobId 24275: Sending spooled attrs to the Director. Despooling 220,144,305 bytes ... 18-Nov 16:16 backup-dir JobId 24275: Bacula backup-dir 9.0.8 (28May18): 7 hours to insert 220MB of data. With batches it's a few minutes. Normal MySQL 5.7 DB with no fancy tricks apart from the usual tuning. In my opinion batches should definitely be the default (as per the bacula recommendations) and make a USE flag for people who want to disable them.
(In reply to Ortwin Glueck from comment #2) > I have read that mailing list thread now. It seems its one guy with an > exotic setup that has a problem with batches. But this is certainly not the > normal case. And using non-batched insert DOES have a considerable > performance cost! > > Little example from our production backup: > 18-Nov 09:20 backup-sd JobId 24275: Sending spooled attrs to the Director. > Despooling 220,144,305 bytes ... > 18-Nov 16:16 backup-dir JobId 24275: Bacula backup-dir 9.0.8 (28May18): > > 7 hours to insert 220MB of data. With batches it's a few minutes. Normal > MySQL 5.7 DB with no fancy tricks apart from the usual tuning. > Thanks for the feedback and teh helpful numbers. Until now the only one giving feedback was Phil, so I had to take his considerations into account. > In my opinion batches should definitely be the default (as per the bacula > recommendations) and make a USE flag for people who want to disable them. That is what I had planned to do after your report. Be aware that I will be away most of this week, so it will take a little time to do it.
(In reply to Thomas Beierlein from comment #3) > That is what I had planned to do after your report. Be aware that I will be > away most of this week, so it will take a little time to do it. Great to hear! No time pressure, we are patching locally :)
I still owe you performance data for the batch-insert enabled case: > Little example from our production backup: > 18-Nov 09:20 backup-sd JobId 24275: Sending spooled attrs to the Director. > Despooling 220,144,305 bytes ... > 18-Nov 16:16 backup-dir JobId 24275: Bacula backup-dir 9.0.8 (28May18): Same Job with batches, takes 5 minutes: 25-Nov 03:35 backup-sd JobId 24346: Sending spooled attrs to the Director. Despooling 222,709,349 bytes ... 25-Nov 03:40 backup-dir JobId 24346: Bacula backup-dir 9.0.8 (28May18):
(In reply to Ortwin Glueck from comment #5) > I still owe you performance data for the batch-insert enabled case: > > > Little example from our production backup: > > 18-Nov 09:20 backup-sd JobId 24275: Sending spooled attrs to the Director. > > Despooling 220,144,305 bytes ... > > 18-Nov 16:16 backup-dir JobId 24275: Bacula backup-dir 9.0.8 (28May18): > > Same Job with batches, takes 5 minutes: > > 25-Nov 03:35 backup-sd JobId 24346: Sending spooled attrs to the Director. > Despooling 222,709,349 bytes ... > 25-Nov 03:40 backup-dir JobId 24346: Bacula backup-dir 9.0.8 (28May18): Thanks Ortwin. I can confirm that behaviour here. Fixed ebuild will be in tree in next minutes.
The bug has been closed via the following commit(s): https://gitweb.gentoo.org/repo/gentoo.git/commit/?id=b7c9719f45e95defe57d1c24ef6902aa0f913d32 commit b7c9719f45e95defe57d1c24ef6902aa0f913d32 Author: Thomas Beierlein <tomjbe@gentoo.org> AuthorDate: 2018-12-07 15:40:49 +0000 Commit: Thomas Beierlein <tomjbe@gentoo.org> CommitDate: 2018-12-07 15:42:42 +0000 app-backup/bacula: Reenable batch insert into mysql data base. Using batch insert can now be controlled by an USE flag. Suggested-by: Ortwin Glueck <odi@odi.ch> Closes: https://bugs.gentoo.org/671222 Package-Manager: Portage-2.3.52, Repoman-2.3.12 Signed-off-by: Thomas Beierlein <tomjbe@gentoo.org> app-backup/bacula/bacula-9.2.2-r1.ebuild | 434 +++++++++++++++++++++++++++++++ app-backup/bacula/metadata.xml | 1 + 2 files changed, 435 insertions(+)
