Re: [Bacula-devel] Schema patch
Hello Ulrich, I obviously would be interested in seeing the statistics, and unless you take some precautions I would strongly suggest not running it on a production environment. If you do, nevertheless, want to try, at a minimum, I would create a new database (named perhaps TestBacula. Then if you are careful to backup your catalog before experimenting, then run the tests only on the new database, you would be much less likely to mess up your production jobs. Best regards, Kern On 08/06/2017 07:47 PM, Ulrich Leodolter wrote: Hi Phil, Phil Stracchino hat am 6. August 2017 um 17:03 geschrieben: Community request: Does anyone else here have a test environment which DOES have jobs of that size who could perform comparison tests of the same 20-50 million file backup job with and without batch insert? We have one job from disk to LTO6 which has about 10 Million files and 6.6 TB. Runs on CentOS 6.8 machine, mysql-5.1.73 MyISAM storage engine and Bacula Version: 7.4.8 (10 April 2017) How can i disable batch insert, only by configure --disable-batch-inserts and recompile ? Does it make sense to do timing tests on this environment ? Its a production system and i need to be careful. Best regards Ulrich Ulrich Leodolter Oesterreichische Bibliothekenverbund und Service GmbH Raimundgasse 1/3, A-1020 Wien Fax +43 1 4035158-30 Tel +43 1 4035158-21 Web https://www.obvsg.at -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
Re: [Bacula-devel] Schema patch
On 08/06/17 13:47, Ulrich Leodolter wrote: > Hi Phil, > >> Phil Stracchino hat am 6. August 2017 um 17:03 >> geschrieben: >> >> Community request: >> Does anyone else here have a test environment which DOES have jobs of >> that size who could perform comparison tests of the same 20-50 million >> file backup job with and without batch insert? > > We have one job from disk to LTO6 which has about 10 Million files and 6.6 TB. > > Runs on CentOS 6.8 machine, mysql-5.1.73 MyISAM storage engine and Bacula > Version: 7.4.8 (10 April 2017) > > How can i disable batch insert, only by configure --disable-batch-inserts > and recompile ? Hi Ulrich, Yes, you'd have to recompile with --disable-batch-insert. There is no configuration directive to enable/disable it, or command to enable/disable it interactively. I certainly wouldn't ask you to test the change in a production environment. I also wouldn't be testing (or running) against MyISAM in the first place. Honestly, the MyISAM storage engine is *LONG* past its expiration date, and Oracle is actively working towards removing support for it. I fully expect it to be disabled by default in MySQL 8. It is fragile, and its concurrent performance in write-heavy loads is just terrible. A *substantial* number of the customer site-performance issues we see at my employer are directly attributable to MyISAM. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
Re: [Bacula-devel] Schema patch
Hi Phil, > Phil Stracchino hat am 6. August 2017 um 17:03 > geschrieben: > > Community request: > Does anyone else here have a test environment which DOES have jobs of > that size who could perform comparison tests of the same 20-50 million > file backup job with and without batch insert? We have one job from disk to LTO6 which has about 10 Million files and 6.6 TB. Runs on CentOS 6.8 machine, mysql-5.1.73 MyISAM storage engine and Bacula Version: 7.4.8 (10 April 2017) How can i disable batch insert, only by configure --disable-batch-inserts and recompile ? Does it make sense to do timing tests on this environment ? Its a production system and i need to be careful. Best regards Ulrich Ulrich Leodolter Oesterreichische Bibliothekenverbund und Service GmbH Raimundgasse 1/3, A-1020 Wien Fax +43 1 4035158-30 Tel +43 1 4035158-21 Web https://www.obvsg.at -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
Re: [Bacula-devel] Schema patch
On 08/06/17 03:24, Kern Sibbald wrote: > Hello Phil, > > Thank you for your comments, and I am pleased that you have found a > solution for your batch insert problem. Unfortunately, I cannot apply > the patch to a released version two days before I go on vacation. Nor > will I change the default. > > If you can show me solid statistics on a Linux system that for the most > current MySQL inserting between 20 and 50 million records in a single > job is slower with batch insert than without it, I will ask the authors > of the code to take a look at it or at least explain. Otherwise, I > trust their judgment. That said, it may well be for smaller workloads > and a properly tuned MySQL it could be more efficient to do normal inserts. I can't meet that criterion because I don't have a job that large to test against. My largest job has just under three million files. And to be fair I just switched out my main NAS for a more powerful system; that could easily be the reason for the better performance on incremental backups. I can say that large jobs run with steady incremental writes to the DB throughout the job instead of the massive choke-a-hippo attribute despool at the end of each job. Community request: Does anyone else here have a test environment which DOES have jobs of that size who could perform comparison tests of the same 20-50 million file backup job with and without batch insert? -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
Re: [Bacula-devel] Schema patch
Phil, Here is my response concerning the mysql_tables_change (see your email below). I cannot make changes of the kind that you suggest where some change items that are not broken or items where I cannot reproduce a problem. This is especially since this is a production release, which I cannot risk creating new problems, and the fact that I am leaving for three weeks in the next couple of days. At least I am happy that you now have a solution that fixes your problem. When I get back from vacation, I will look at the code you have that might reduce problems when upgrading from older databases. Best regards, Kern On 08/05/2017 04:50 PM, Phil Stracchino wrote: Kern, Rather than re-opening the ticket again - the schema patch doesn't do NEARLY as much as it looks as though it does. The make_mysql_tables script currently creates a new Bacula schema in which all DATETIME fields *except one* are left as unadorned DATETIME with no NOT NULL or DEFAULT. The one exception is the DATETIME field in Snapshot, and we talked about that and you agreed the NOT NULL was unnecessary. So the patch removes it, so that the column is not declared NOT NULL without a default. The Snapshot table also contains one BIGINT declared NOT NULL without a DEFAULT, which is invalid. The patch adds a DEFAULT 0 to that column. Those are the only changes made to make_mysql_tables.in. One NOT NULL removed, one DEFAULT added. The change to update_mysql_tables.in does only one thing: It makes all the DATETIME columns in an *existing* Bacula catalog database match the DATETIME columns in a brand new one created by make_mysql_tables. (It should also fix the missing DEFAULT on Snapshot.CreateTDate, but I just realized this morning I missed that.) -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
Re: [Bacula-devel] Schema patch
Hello Phil, Thank you for your comments, and I am pleased that you have found a solution for your batch insert problem. Unfortunately, I cannot apply the patch to a released version two days before I go on vacation. Nor will I change the default. If you can show me solid statistics on a Linux system that for the most current MySQL inserting between 20 and 50 million records in a single job is slower with batch insert than without it, I will ask the authors of the code to take a look at it or at least explain. Otherwise, I trust their judgment. That said, it may well be for smaller workloads and a properly tuned MySQL it could be more efficient to do normal inserts. Best regards, Kern On 08/05/2017 04:50 PM, Phil Stracchino wrote: Kern, Rather than re-opening the ticket again - the schema patch doesn't do NEARLY as much as it looks as though it does. The make_mysql_tables script currently creates a new Bacula schema in which all DATETIME fields *except one* are left as unadorned DATETIME with no NOT NULL or DEFAULT. The one exception is the DATETIME field in Snapshot, and we talked about that and you agreed the NOT NULL was unnecessary. So the patch removes it, so that the column is not declared NOT NULL without a default. The Snapshot table also contains one BIGINT declared NOT NULL without a DEFAULT, which is invalid. The patch adds a DEFAULT 0 to that column. Those are the only changes made to make_mysql_tables.in. One NOT NULL removed, one DEFAULT added. The change to update_mysql_tables.in does only one thing: It makes all the DATETIME columns in an *existing* Bacula catalog database match the DATETIME columns in a brand new one created by make_mysql_tables. (It should also fix the missing DEFAULT on Snapshot.CreateTDate, but I just realized this morning I missed that.) -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot ___ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
