From: Stephen T. <st...@se...> - 2015-09-25 16:02:59
|
So far so good. Minor snafu on my part when updating database, but I'm running 7.2 now. Looking good so far. Will find out more when hundreds of jobs run tonight. Stephen On 09/24/2015 08:40 AM, Stephen Thompson wrote: > > All, > > I typically patch bacula pretty frequently, but I saw the somewhat > unusual notice on the latest release notes that warns it may not be > ready for use in production. How stable is it? I don't really have the > resources to test this out, but rather would have to go straight to > production with it. I could always roll back, but that might entail the > recovery from dump of a 900GB database. Opinions? > > thanks, > Stephen > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Stephen T. <st...@se...> - 2015-10-02 22:00:41
|
All, I believe I'm having mysql database issues since upgrading to 7.2 (from 7.0.2). I run mysql innodb with 900Gb database that's largely the File table. Since upgrading, I lose a few jobs a night due to database locking timeouts, which I have set to 3600. I also log slow queries. It appears that typically during a months I have about 90-100 queries that take longer than 15 minutes to run. Already this month (upgraded earlier this week), I have 32 queries that take longer than 15 minutes. At this rate (after 2 days) that will up my regular average of 90-100 to 480! Something is wrong and the coincidence is pretty strong that it's related to the upgrade. Ideas? thanks, Stephen On 09/25/2015 09:02 AM, Stephen Thompson wrote: > > > So far so good. Minor snafu on my part when updating database, but I'm > running 7.2 now. Looking good so far. Will find out more when hundreds > of jobs run tonight. > > Stephen > > > > On 09/24/2015 08:40 AM, Stephen Thompson wrote: >> >> All, >> >> I typically patch bacula pretty frequently, but I saw the somewhat >> unusual notice on the latest release notes that warns it may not be >> ready for use in production. How stable is it? I don't really have the >> resources to test this out, but rather would have to go straight to >> production with it. I could always roll back, but that might entail the >> recovery from dump of a 900GB database. Opinions? >> >> thanks, >> Stephen >> > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Eric B. <eri...@ba...> - 2015-10-03 07:02:40
|
Hello Stephen, On 10/03/2015 12:00 AM, Stephen Thompson wrote: > > > All, > > I believe I'm having mysql database issues since upgrading to 7.2 (from > 7.0.2). I run mysql innodb with 900Gb database that's largely the File > table. For large catalog, we usually advise to use PostgreSQL where we have multi-terabytes databases in production. > Since upgrading, I lose a few jobs a night due to database locking > timeouts, which I have set to 3600. I also log slow queries. Can you get some information about these locks? On which table? Can you give some statistics on your catalog like the size and the number of records of the File, Filename and Path table? Would be nice also if you can give the number of Filename per Client (from the job table). You might have many orphan Filenames, and MySQL is not always very good to join large tables (it uses nested loops, and cannot use the index on the Text column in all queries). > It appears that typically during a months I have about 90-100 queries > that take longer than 15 minutes to run. Already this month (upgraded > earlier this week), I have 32 queries that take longer than 15 minutes. > At this rate (after 2 days) that will up my regular average of 90-100 > to 480! > > Something is wrong and the coincidence is pretty strong that it's > related to the upgrade. Maybe, but I'm not sure, we did not change a lot of thing in this area, we did mostly refactoring. Best Regards, Eric |
From: Phil S. <ph...@ca...> - 2015-10-03 18:32:12
|
On 10/03/15 03:02, Eric Bollengier wrote: > Hello Stephen, > > On 10/03/2015 12:00 AM, Stephen Thompson wrote: >> It appears that typically during a months I have about 90-100 queries >> that take longer than 15 minutes to run. Already this month (upgraded >> earlier this week), I have 32 queries that take longer than 15 minutes. >> At this rate (after 2 days) that will up my regular average of 90-100 >> to 480! >> >> Something is wrong and the coincidence is pretty strong that it's >> related to the upgrade. > > Maybe, but I'm not sure, we did not change a lot of thing in this area, > we did mostly refactoring. > > Best Regards, > Eric For what it's worth, I am running Bacula 7.2.0 against MySQL 5.6.27 and I am experiencing no Bacula performance problems. -- Phil Stracchino Babylon Communications ph...@ca... ph...@co... Landline: 603.293.8485 |
From: Josip D. <djo...@li...> - 2015-10-05 08:37:17
|
On Saturday 2015-10-03 14:32:04 Phil Stracchino wrote: > For what it's worth, I am running Bacula 7.2.0 against MySQL 5.6.27 and > I am experiencing no Bacula performance problems. Phil, what storage engine are you using, myisam or innodb? Stephen is using innodb. This might be the important difference. -- Josip Deanovic |
From: Phil S. <ph...@ca...> - 2015-10-05 10:54:54
|
On 10/05/15 04:37, Josip Deanovic wrote: > On Saturday 2015-10-03 14:32:04 Phil Stracchino wrote: >> For what it's worth, I am running Bacula 7.2.0 against MySQL 5.6.27 and >> I am experiencing no Bacula performance problems. > > Phil, what storage engine are you using, myisam or innodb? > Stephen is using innodb. This might be the important difference. I am using 100% InnoDB. MyISAM should not be used in production any more unless it is absolutely unavoidable. Expect it to be deprecated in the not too distant future, possibly as soon as MySQL 5.8. -- Phil Stracchino Babylon Communications ph...@ca... ph...@co... Landline: 603.293.8485 |
From: Phil S. <ph...@ca...> - 2015-10-05 11:20:29
|
On 10/05/15 06:54, Phil Stracchino wrote: > On 10/05/15 04:37, Josip Deanovic wrote: >> On Saturday 2015-10-03 14:32:04 Phil Stracchino wrote: >>> For what it's worth, I am running Bacula 7.2.0 against MySQL 5.6.27 and >>> I am experiencing no Bacula performance problems. >> >> Phil, what storage engine are you using, myisam or innodb? >> Stephen is using innodb. This might be the important difference. > > I am using 100% InnoDB. MyISAM should not be used in production any > more unless it is absolutely unavoidable. Expect it to be deprecated in > the not too distant future, possibly as soon as MySQL 5.8. > > -- Phil Stracchino Babylon Communications ph...@ca... ph...@co... Landline: 603.293.8485 |
From: Stephen T. <st...@se...> - 2015-10-05 17:17:25
|
Eric, Thanks for the reply. I've heard the postgres recommendation a fair number of times. A couple years back, we setup a parallel instance but even after tuning still wound up with _worse_ performance than with mysql. I could not figure out what to attribute this to (because it was in such contrast to all the pro-postgres recommendations) except possibly our memory-poor server - 8Gb RAM. At any rate, the only thing that's changed was the upgrade from 7.0.5 to 7.2.0. The table involved is definitely the File table. We do have jobs with 20-30 million records, so those jobs can be slow when it comes time for attribute insertion into the database (or to read out a file list for Accurate backups). This why we've historically had innodb lock timeout of 3600. However, it's only last week after the upgrade that we've ever had queries extend beyond that hour mark. We also went through a database cleaning process last month due to nearly reaching 1Tb and I can pretty authoritatively claim that we don't have orphan records. The database content and schema all appear to be appropriate. I was worried that queries had been rewritten that made it more efficient for other databases, but less so for mysql. More info... example from slow query logfile: # Time: 151001 1:28:14 # User@Host: bacula[bacula] @ localhost [] # Query_time: 3675.052083 Lock_time: 73.719795 Rows_sent: 0 Rows_examined: 3 SET timestamp=1443688094; INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = Filename.Name); mysqld: mysql-5.1.73-5.el6_6.x86_64 record counts per table: File 4,315,675,600 Filename 154,748,787 Path 28,534,411 innodb file sizes: 847708500 File.ibd 19488772 Filename.ibd 8216580 Path.ibd 106500 PathHierarchy.ibd 57344 JobMedia.ibd 40960 PathVisibility.ibd 27648 Job.ibd 512 Media.ibd 176 FileSet.ibd 144 JobHisto.ibd 144 Client.ibd 112 RestoreObject.ibd 112 Pool.ibd 112 Log.ibd 112 BaseFiles.ibd 96 Version.ibd 96 UnsavedFiles.ibd 96 Storage.ibd 96 Status.ibd 96 MediaType.ibd 96 LocationLog.ibd 96 Location.ibd 96 Device.ibd 96 Counters.ibd 96 CDImages.ibd 4 Snapshot.MYI 0 Snapshot.MYD Not related, but I just noticed that somehow the new Snapshot table is MyISAM format. How did that happen? Regarding: > Would be nice also if you can give the number of Filename per Client (from the job table). Do you have a sample SQL to retrieve this stat? thanks, Stephen On 10/03/2015 12:02 AM, Eric Bollengier wrote: > Hello Stephen, > > On 10/03/2015 12:00 AM, Stephen Thompson wrote: >> >> >> All, >> >> I believe I'm having mysql database issues since upgrading to 7.2 (from >> 7.0.2). I run mysql innodb with 900Gb database that's largely the File >> table. > > For large catalog, we usually advise to use PostgreSQL where we have > multi-terabytes databases in production. > >> Since upgrading, I lose a few jobs a night due to database locking >> timeouts, which I have set to 3600. I also log slow queries. > > Can you get some information about these locks? On which table? Can you > give some statistics on your catalog like the size and the number of > records of the File, Filename and Path table? Would be nice also if you > can give the number of Filename per Client (from the job table). > > You might have many orphan Filenames, and MySQL is not always very good > to join large tables (it uses nested loops, and cannot use the index on > the Text column in all queries). > >> It appears that typically during a months I have about 90-100 queries >> that take longer than 15 minutes to run. Already this month (upgraded >> earlier this week), I have 32 queries that take longer than 15 minutes. >> At this rate (after 2 days) that will up my regular average of 90-100 >> to 480! >> >> Something is wrong and the coincidence is pretty strong that it's >> related to the upgrade. > > Maybe, but I'm not sure, we did not change a lot of thing in this area, > we did mostly refactoring. > > Best Regards, > Eric > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Phil S. <ph...@ca...> - 2015-10-05 17:28:41
|
On 10/05/15 13:17, Stephen Thompson wrote: > At any rate, the only thing that's changed was the upgrade from 7.0.5 to > 7.2.0. The table involved is definitely the File table. We do have > jobs with 20-30 million records, so those jobs can be slow when it comes > time for attribute insertion into the database (or to read out a file > list for Accurate backups). This why we've historically had innodb lock > timeout of 3600. However, it's only last week after the upgrade that > we've ever had queries extend beyond that hour mark. Stephen, Just as a thought, there have been a number of threads on this mailing list recommending additional or modified indexes on the File table. Have you added the suggested additional indexes? -- Phil Stracchino Babylon Communications ph...@ca... ph...@co... Landline: 603.293.8485 |
From: Stephen T. <st...@se...> - 2015-10-05 17:30:45
|
Phil, Good question. I vaguely recollect doing that a few years back, but I don't immediately see any additional indexing. Where can I reference what the default indexes are supposed to be? thanks, Stephen On 10/05/2015 10:28 AM, Phil Stracchino wrote: > On 10/05/15 13:17, Stephen Thompson wrote: >> At any rate, the only thing that's changed was the upgrade from 7.0.5 to >> 7.2.0. The table involved is definitely the File table. We do have >> jobs with 20-30 million records, so those jobs can be slow when it comes >> time for attribute insertion into the database (or to read out a file >> list for Accurate backups). This why we've historically had innodb lock >> timeout of 3600. However, it's only last week after the upgrade that >> we've ever had queries extend beyond that hour mark. > > Stephen, > Just as a thought, there have been a number of threads on this mailing > list recommending additional or modified indexes on the File table. > Have you added the suggested additional indexes? > > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Stephen T. <st...@se...> - 2015-10-05 17:29:13
|
Nevermind about question concerning Snapshot table. I see what happened there. On 10/05/2015 10:17 AM, Stephen Thompson wrote: > > Eric, > > Thanks for the reply. > > I've heard the postgres recommendation a fair number of times. A couple > years back, we setup a parallel instance but even after tuning still > wound up with _worse_ performance than with mysql. I could not figure > out what to attribute this to (because it was in such contrast to all > the pro-postgres recommendations) except possibly our memory-poor server > - 8Gb RAM. > > At any rate, the only thing that's changed was the upgrade from 7.0.5 to > 7.2.0. The table involved is definitely the File table. We do have > jobs with 20-30 million records, so those jobs can be slow when it comes > time for attribute insertion into the database (or to read out a file > list for Accurate backups). This why we've historically had innodb lock > timeout of 3600. However, it's only last week after the upgrade that > we've ever had queries extend beyond that hour mark. > > We also went through a database cleaning process last month due to > nearly reaching 1Tb and I can pretty authoritatively claim that we don't > have orphan records. The database content and schema all appear to be > appropriate. I was worried that queries had been rewritten that made it > more efficient for other databases, but less so for mysql. > > > More info... > > example from slow query logfile: > # Time: 151001 1:28:14 > # User@Host: bacula[bacula] @ localhost [] > # Query_time: 3675.052083 Lock_time: 73.719795 Rows_sent: 0 > Rows_examined: 3 > SET timestamp=1443688094; > INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, > DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, > Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch > JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = > Filename.Name); > > mysqld: > mysql-5.1.73-5.el6_6.x86_64 > > record counts per table: > File 4,315,675,600 > Filename 154,748,787 > Path 28,534,411 > > innodb file sizes: > 847708500 File.ibd > 19488772 Filename.ibd > 8216580 Path.ibd > 106500 PathHierarchy.ibd > 57344 JobMedia.ibd > 40960 PathVisibility.ibd > 27648 Job.ibd > 512 Media.ibd > 176 FileSet.ibd > 144 JobHisto.ibd > 144 Client.ibd > 112 RestoreObject.ibd > 112 Pool.ibd > 112 Log.ibd > 112 BaseFiles.ibd > 96 Version.ibd > 96 UnsavedFiles.ibd > 96 Storage.ibd > 96 Status.ibd > 96 MediaType.ibd > 96 LocationLog.ibd > 96 Location.ibd > 96 Device.ibd > 96 Counters.ibd > 96 CDImages.ibd > 4 Snapshot.MYI > 0 Snapshot.MYD > > > > Not related, but I just noticed that somehow the new Snapshot table is > MyISAM format. How did that happen? > > Regarding: > > Would be nice also if you can give the number of Filename per Client > (from the job table). > > Do you have a sample SQL to retrieve this stat? > > > thanks, > Stephen > > > > > > > > On 10/03/2015 12:02 AM, Eric Bollengier wrote: >> Hello Stephen, >> >> On 10/03/2015 12:00 AM, Stephen Thompson wrote: >>> >>> >>> All, >>> >>> I believe I'm having mysql database issues since upgrading to 7.2 (from >>> 7.0.2). I run mysql innodb with 900Gb database that's largely the File >>> table. >> >> For large catalog, we usually advise to use PostgreSQL where we have >> multi-terabytes databases in production. >> >>> Since upgrading, I lose a few jobs a night due to database locking >>> timeouts, which I have set to 3600. I also log slow queries. >> >> Can you get some information about these locks? On which table? Can you >> give some statistics on your catalog like the size and the number of >> records of the File, Filename and Path table? Would be nice also if you >> can give the number of Filename per Client (from the job table). >> >> You might have many orphan Filenames, and MySQL is not always very good >> to join large tables (it uses nested loops, and cannot use the index on >> the Text column in all queries). >> >>> It appears that typically during a months I have about 90-100 queries >>> that take longer than 15 minutes to run. Already this month (upgraded >>> earlier this week), I have 32 queries that take longer than 15 minutes. >>> At this rate (after 2 days) that will up my regular average of 90-100 >>> to 480! >>> >>> Something is wrong and the coincidence is pretty strong that it's >>> related to the upgrade. >> >> Maybe, but I'm not sure, we did not change a lot of thing in this area, >> we did mostly refactoring. >> >> Best Regards, >> Eric >> > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Stephen T. <st...@se...> - 2015-10-05 17:32:35
|
mysql> show indexes from File; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | File | 0 | PRIMARY | 1 | FileId | A | 4494348205 | NULL | NULL | | BTREE | | | File | 1 | JobId | 1 | JobId | A | 19 | NULL | NULL | | BTREE | | | File | 1 | JobId | 2 | PathId | A | 408577109 | NULL | NULL | | BTREE | | | File | 1 | JobId | 3 | FilenameId | A | 4494348205 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ On 10/05/2015 10:30 AM, Stephen Thompson wrote: > > > Phil, > > Good question. I vaguely recollect doing that a few years back, but I > don't immediately see any additional indexing. Where can I reference > what the default indexes are supposed to be? > > thanks, > Stephen > > > > On 10/05/2015 10:28 AM, Phil Stracchino wrote: >> On 10/05/15 13:17, Stephen Thompson wrote: >>> At any rate, the only thing that's changed was the upgrade from 7.0.5 to >>> 7.2.0. The table involved is definitely the File table. We do have >>> jobs with 20-30 million records, so those jobs can be slow when it comes >>> time for attribute insertion into the database (or to read out a file >>> list for Accurate backups). This why we've historically had innodb lock >>> timeout of 3600. However, it's only last week after the upgrade that >>> we've ever had queries extend beyond that hour mark. >> >> Stephen, >> Just as a thought, there have been a number of threads on this mailing >> list recommending additional or modified indexes on the File table. >> Have you added the suggested additional indexes? >> >> > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Phil S. <ph...@ca...> - 2015-10-05 18:39:49
|
On 10/05/15 13:32, Stephen Thompson wrote: > show indexes from File; OK, you have all of the important indexes I have. I actually have one more but it's redundant and should probably be dropped. -- Phil Stracchino Babylon Communications ph...@ca... ph...@co... Landline: 603.293.8485 |
From: Ana E. M. A. <emi...@gm...> - 2015-10-07 13:23:58
|
Hello Stephen, On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson < st...@se...> wrote: > > Regarding: > > Would be nice also if you can give the number of Filename per Client > (from the job table). > > Do you have a sample SQL to retrieve this stat? > select Client.Name, count(distinct Filename.FilenameId) from Client, Filename, File, Job where Filename.FilenameId=File.FilenameId and File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by Client.ClientId; The above query should work. Best regards, Ana > > > thanks, > Stephen > > > > > > > > On 10/03/2015 12:02 AM, Eric Bollengier wrote: > > Hello Stephen, > > > > On 10/03/2015 12:00 AM, Stephen Thompson wrote: > >> > >> > >> All, > >> > >> I believe I'm having mysql database issues since upgrading to 7.2 (from > >> 7.0.2). I run mysql innodb with 900Gb database that's largely the File > >> table. > > > > For large catalog, we usually advise to use PostgreSQL where we have > > multi-terabytes databases in production. > > > >> Since upgrading, I lose a few jobs a night due to database locking > >> timeouts, which I have set to 3600. I also log slow queries. > > > > Can you get some information about these locks? On which table? Can you > > give some statistics on your catalog like the size and the number of > > records of the File, Filename and Path table? Would be nice also if you > > can give the number of Filename per Client (from the job table). > > > > You might have many orphan Filenames, and MySQL is not always very good > > to join large tables (it uses nested loops, and cannot use the index on > > the Text column in all queries). > > > >> It appears that typically during a months I have about 90-100 queries > >> that take longer than 15 minutes to run. Already this month (upgraded > >> earlier this week), I have 32 queries that take longer than 15 minutes. > >> At this rate (after 2 days) that will up my regular average of 90-100 > >> to 480! > >> > >> Something is wrong and the coincidence is pretty strong that it's > >> related to the upgrade. > > > > Maybe, but I'm not sure, we did not change a lot of thing in this area, > > we did mostly refactoring. > > > > Best Regards, > > Eric > > > > -- > Stephen Thompson Berkeley Seismological Laboratory > st...@se... 215 McCone Hall # 4760 > Office: 510.664.9177 University of California, Berkeley > Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 > > > ------------------------------------------------------------------------------ > _______________________________________________ > Bacula-users mailing list > Bac...@li... > https://lists.sourceforge.net/lists/listinfo/bacula-users > |
From: Stephen T. <st...@se...> - 2015-10-07 15:13:35
|
Thanks for the help. Though, this is giving me a syntax error. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select Client.Name, count(distinct Filename.FilenameId) from Client, Filen' at line 1 On 10/7/15 6:23 AM, Ana Emília M. Arruda wrote: > select Client.Name, count(distinct Filename.FilenameId) from Client, > Filename, File, Job where Filename.FilenameId=File.FilenameId and > File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by > Client.ClientId; -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall #4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Ana E. M. A. <emi...@gm...> - 2015-10-07 18:22:07
|
Hello Stephen, I cheked that "select count(distinct) is available in MySQL 5.X versions. Could you try the bellow? mysql> select count(distinct MediaId) from Media; +-------------------------+ | count(distinct MediaId) | +-------------------------+ | 60 | +-------------------------+ 1 row in set (0.00 sec) The complete query worked for me (the server has 22 clients): mysql> select Client.Name, count(distinct Filename.FilenameId) from Client, Filename, File, Job where Filename.FilenameId=File.FilenameId and File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by Client.ClientId; +------------------+-------------------------------------+ | Name | count(distinct Filename.FilenameId) | +------------------+-------------------------------------+ | xxxxxx-fd | 6590 | | yyyyyy-fd | 222632 | | zzzzzzz-fd | 441730 | ... +------------------+-------------------------------------+ 22 rows in set (16 min 42.63 sec) Best regards, Ana On Wed, Oct 7, 2015 at 12:13 PM, Stephen Thompson < st...@se...> wrote: > > > Thanks for the help. Though, this is giving me a syntax error. > > ERROR 1064 (42000): You have an error in your SQL syntax; check the manual > that corresponds to your MySQL server version for the right syntax to use > near 'select Client.Name, count(distinct Filename.FilenameId) from > Client, Filen' at line 1 > > On 10/7/15 6:23 AM, Ana Emília M. Arruda wrote: > >> select Client.Name, count(distinct Filename.FilenameId) from Client, >> Filename, File, Job where Filename.FilenameId=File.FilenameId and >> File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by >> Client.ClientId; >> > > -- > Stephen Thompson Berkeley Seismological Laboratory > st...@se... 215 McCone Hall #4760 > Office: 510.664.9177 University of California, Berkeley > Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 > |
From: Eric B. <eri...@ba...> - 2015-10-09 05:58:25
|
Hello Stephen, Le 05. 10. 15 19:17, Stephen Thompson a écrit : > > Eric, > > Thanks for the reply. > > I've heard the postgres recommendation a fair number of times. A couple > years back, we setup a parallel instance but even after tuning still > wound up with _worse_ performance than with mysql. I could not figure > out what to attribute this to (because it was in such contrast to all > the pro-postgres recommendations) except possibly our memory-poor server > - 8Gb RAM. > > At any rate, the only thing that's changed was the upgrade from 7.0.5 to > 7.2.0. The table involved is definitely the File table. We do have > jobs with 20-30 million records, so those jobs can be slow when it comes > time for attribute insertion into the database (or to read out a file > list for Accurate backups). This why we've historically had innodb lock > timeout of 3600. However, it's only last week after the upgrade that > we've ever had queries extend beyond that hour mark. > > We also went through a database cleaning process last month due to > nearly reaching 1Tb and I can pretty authoritatively claim that we don't > have orphan records. The database content and schema all appear to be > appropriate. A 1TB database (running either Postgresql, MySQL or whatever other kind of product) should be carefully tuned and monitored. My guess would be that your my.cnf settings are not suitable for such database size. You can run a tool such as MySQLtuner to check that everything is ok on MySQL side, increase the size of the memory of your server or try to cleanup orphan filename records. The size of the File table should not impact performances on Backup, but other tables such as Path or Filename are important (and they are pretty big on your site). > I was worried that queries had been rewritten that made it > more efficient for other databases, but less so for mysql. We didn't wrote database query specifically for PostgreSQL or MySQL but we optimize them when it's possible, some SQLite queries were optimized by a contributor 2 or 3 years ago, and it was way faster for some parts of Bacula afterward. If you look the database world from outside, you might think that everything is nice and smooth because all products seem to talk the same language (SQL), but they all have a different way to handle the work and the SQL specifications (and the lack of specifications). For myself, I'm a PostgreSQL user for a quite long time, I have good relationships with the PostgreSQL community, and we got huge help when we wrote the "Batch Mode" few years ago. I know that it works well and we can analyze problems quite easily, doing so I always advise strongly to use PostgreSQL for all large setup. For other products, developers uses MySQL and the PostgreSQL driver is not good at all. With the time, I found that you can do "more" with "less" hardware when using the PostgreSQL catalog. In your case (a fairly big database), it might be the time to spend a bit of money to get more RAM and/or make sure that your Path/Filename indexes stay in RAM. Hope it helps. Best Regards, Eric > > > More info... > > example from slow query logfile: > # Time: 151001 1:28:14 > # User@Host: bacula[bacula] @ localhost [] > # Query_time: 3675.052083 Lock_time: 73.719795 Rows_sent: 0 > Rows_examined: 3 > SET timestamp=1443688094; > INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, > DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, > Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch > JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = > Filename.Name); > > mysqld: > mysql-5.1.73-5.el6_6.x86_64 > > record counts per table: > File 4,315,675,600 > Filename 154,748,787 > Path 28,534,411 > > innodb file sizes: > 847708500 File.ibd > 19488772 Filename.ibd > 8216580 Path.ibd > 106500 PathHierarchy.ibd > 57344 JobMedia.ibd > 40960 PathVisibility.ibd > 27648 Job.ibd > 512 Media.ibd > 176 FileSet.ibd > 144 JobHisto.ibd > 144 Client.ibd > 112 RestoreObject.ibd > 112 Pool.ibd > 112 Log.ibd > 112 BaseFiles.ibd > 96 Version.ibd > 96 UnsavedFiles.ibd > 96 Storage.ibd > 96 Status.ibd > 96 MediaType.ibd > 96 LocationLog.ibd > 96 Location.ibd > 96 Device.ibd > 96 Counters.ibd > 96 CDImages.ibd > 4 Snapshot.MYI > 0 Snapshot.MYD > > > > Not related, but I just noticed that somehow the new Snapshot table is > MyISAM format. How did that happen? > > Regarding: > > Would be nice also if you can give the number of Filename per Client > (from the job table). > > Do you have a sample SQL to retrieve this stat? > > > thanks, > Stephen > > > > > > > > On 10/03/2015 12:02 AM, Eric Bollengier wrote: >> Hello Stephen, >> >> On 10/03/2015 12:00 AM, Stephen Thompson wrote: >>> >>> >>> All, >>> >>> I believe I'm having mysql database issues since upgrading to 7.2 (from >>> 7.0.2). I run mysql innodb with 900Gb database that's largely the File >>> table. >> >> For large catalog, we usually advise to use PostgreSQL where we have >> multi-terabytes databases in production. >> >>> Since upgrading, I lose a few jobs a night due to database locking >>> timeouts, which I have set to 3600. I also log slow queries. >> >> Can you get some information about these locks? On which table? Can you >> give some statistics on your catalog like the size and the number of >> records of the File, Filename and Path table? Would be nice also if you >> can give the number of Filename per Client (from the job table). >> >> You might have many orphan Filenames, and MySQL is not always very good >> to join large tables (it uses nested loops, and cannot use the index on >> the Text column in all queries). >> >>> It appears that typically during a months I have about 90-100 queries >>> that take longer than 15 minutes to run. Already this month (upgraded >>> earlier this week), I have 32 queries that take longer than 15 minutes. >>> At this rate (after 2 days) that will up my regular average of 90-100 >>> to 480! >>> >>> Something is wrong and the coincidence is pretty strong that it's >>> related to the upgrade. >> >> Maybe, but I'm not sure, we did not change a lot of thing in this area, >> we did mostly refactoring. >> >> Best Regards, >> Eric >> > |
From: Stephen T. <st...@se...> - 2015-10-09 21:09:14
|
Eric, I appreciate all the feedback. We went through a few iterations of tuning awhile back and have not generally had any significant issues over the years with database responsiveness. Back to the original post, it's only been since our upgrade that we started having database lock timeout issues. Otherwise we've run for years (6 or so) without issue. We also went through an orphan record cleanout earlier this year. Stat wise, it looks like our slow queries are still happening at twice the rate compared to recent months, but half as often as they were when I first reported the issue a week ago, so I am equally nonplussed about the improvement as I was about the lockouts. I did get a chance to double the ram from 8 to 16GB today though unfortunately we don't have the ready resources to do many hardware upgrades, though I quite understand why that's a recommendation. Stephen On 10/08/2015 10:58 PM, Eric Bollengier wrote: > Hello Stephen, > > > Le 05. 10. 15 19:17, Stephen Thompson a écrit : >> >> Eric, >> >> Thanks for the reply. >> >> I've heard the postgres recommendation a fair number of times. A couple >> years back, we setup a parallel instance but even after tuning still >> wound up with _worse_ performance than with mysql. I could not figure >> out what to attribute this to (because it was in such contrast to all >> the pro-postgres recommendations) except possibly our memory-poor server >> - 8Gb RAM. >> >> At any rate, the only thing that's changed was the upgrade from 7.0.5 to >> 7.2.0. The table involved is definitely the File table. We do have >> jobs with 20-30 million records, so those jobs can be slow when it comes >> time for attribute insertion into the database (or to read out a file >> list for Accurate backups). This why we've historically had innodb lock >> timeout of 3600. However, it's only last week after the upgrade that >> we've ever had queries extend beyond that hour mark. >> >> We also went through a database cleaning process last month due to >> nearly reaching 1Tb and I can pretty authoritatively claim that we don't >> have orphan records. The database content and schema all appear to be >> appropriate. > > A 1TB database (running either Postgresql, MySQL or whatever other kind > of product) should be carefully tuned and monitored. My guess would be > that your my.cnf settings are not suitable for such database size. You > can run a tool such as MySQLtuner to check that everything is ok on > MySQL side, increase the size of the memory of your server or try to > cleanup orphan filename records. > > The size of the File table should not impact performances on Backup, but > other tables such as Path or Filename are important (and they are pretty > big on your site). > > > I was worried that queries had been rewritten that made it > > more efficient for other databases, but less so for mysql. > > We didn't wrote database query specifically for PostgreSQL or MySQL but > we optimize them when it's possible, some SQLite queries were optimized > by a contributor 2 or 3 years ago, and it was way faster for some parts > of Bacula afterward. > > If you look the database world from outside, you might think that > everything is nice and smooth because all products seem to talk the > same language (SQL), but they all have a different way to handle the > work and the SQL specifications (and the lack of specifications). > For myself, I'm a PostgreSQL user for a quite long time, I have good > relationships with the PostgreSQL community, and we got huge help when > we wrote the "Batch Mode" few years ago. I know that it works well and > we can analyze problems quite easily, doing so I always advise strongly > to use PostgreSQL for all large setup. For other products, developers > uses MySQL and the PostgreSQL driver is not good at all. > > With the time, I found that you can do "more" with "less" hardware when > using the PostgreSQL catalog. In your case (a fairly big database), it > might be the time to spend a bit of money to get more RAM and/or make > sure that your Path/Filename indexes stay in RAM. > > > Hope it helps. > > Best Regards, > Eric > >> >> >> More info... >> >> example from slow query logfile: >> # Time: 151001 1:28:14 >> # User@Host: bacula[bacula] @ localhost [] >> # Query_time: 3675.052083 Lock_time: 73.719795 Rows_sent: 0 >> Rows_examined: 3 >> SET timestamp=1443688094; >> INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, >> DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, >> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch >> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = >> Filename.Name); >> >> mysqld: >> mysql-5.1.73-5.el6_6.x86_64 >> >> record counts per table: >> File 4,315,675,600 >> Filename 154,748,787 >> Path 28,534,411 >> >> innodb file sizes: >> 847708500 File.ibd >> 19488772 Filename.ibd >> 8216580 Path.ibd >> 106500 PathHierarchy.ibd >> 57344 JobMedia.ibd >> 40960 PathVisibility.ibd >> 27648 Job.ibd >> 512 Media.ibd >> 176 FileSet.ibd >> 144 JobHisto.ibd >> 144 Client.ibd >> 112 RestoreObject.ibd >> 112 Pool.ibd >> 112 Log.ibd >> 112 BaseFiles.ibd >> 96 Version.ibd >> 96 UnsavedFiles.ibd >> 96 Storage.ibd >> 96 Status.ibd >> 96 MediaType.ibd >> 96 LocationLog.ibd >> 96 Location.ibd >> 96 Device.ibd >> 96 Counters.ibd >> 96 CDImages.ibd >> 4 Snapshot.MYI >> 0 Snapshot.MYD >> >> >> >> Not related, but I just noticed that somehow the new Snapshot table is >> MyISAM format. How did that happen? >> >> Regarding: >> > Would be nice also if you can give the number of Filename per Client >> (from the job table). >> >> Do you have a sample SQL to retrieve this stat? >> >> >> thanks, >> Stephen >> >> >> >> >> >> >> >> On 10/03/2015 12:02 AM, Eric Bollengier wrote: >>> Hello Stephen, >>> >>> On 10/03/2015 12:00 AM, Stephen Thompson wrote: >>>> >>>> >>>> All, >>>> >>>> I believe I'm having mysql database issues since upgrading to 7.2 (from >>>> 7.0.2). I run mysql innodb with 900Gb database that's largely the File >>>> table. >>> >>> For large catalog, we usually advise to use PostgreSQL where we have >>> multi-terabytes databases in production. >>> >>>> Since upgrading, I lose a few jobs a night due to database locking >>>> timeouts, which I have set to 3600. I also log slow queries. >>> >>> Can you get some information about these locks? On which table? Can you >>> give some statistics on your catalog like the size and the number of >>> records of the File, Filename and Path table? Would be nice also if you >>> can give the number of Filename per Client (from the job table). >>> >>> You might have many orphan Filenames, and MySQL is not always very good >>> to join large tables (it uses nested loops, and cannot use the index on >>> the Text column in all queries). >>> >>>> It appears that typically during a months I have about 90-100 queries >>>> that take longer than 15 minutes to run. Already this month (upgraded >>>> earlier this week), I have 32 queries that take longer than 15 minutes. >>>> At this rate (after 2 days) that will up my regular average of >>>> 90-100 >>>> to 480! >>>> >>>> Something is wrong and the coincidence is pretty strong that it's >>>> related to the upgrade. >>> >>> Maybe, but I'm not sure, we did not change a lot of thing in this area, >>> we did mostly refactoring. >>> >>> Best Regards, >>> Eric >>> >> -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Eric B. <eri...@ba...> - 2015-10-09 06:17:11
|
Thanks Ana! Something such as SELECT ClientId, SUM(JobFiles) AS NB FROM Job GROUP BY ClientId ORDER BY NB DESC; should also do the trick a bit more faster ;-) Best Regards, Eric Le 07. 10. 15 15:23, Ana Emília M. Arruda a écrit : > Hello Stephen, > > On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson > <st...@se... <mailto:st...@se...>> wrote: > > > Regarding: > > Would be nice also if you can give the number of Filename per Client > (from the job table). > > Do you have a sample SQL to retrieve this stat? > > > select Client.Name, count(distinct Filename.FilenameId) from Client, > Filename, File, Job where Filename.FilenameId=File.FilenameId and > File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by > Client.ClientId; > > The above query should work. > > Best regards, > Ana > > > > thanks, > Stephen > > > > > > > > On 10/03/2015 12:02 AM, Eric Bollengier wrote: > > Hello Stephen, > > > > On 10/03/2015 12:00 AM, Stephen Thompson wrote: > >> > >> > >> All, > >> > >> I believe I'm having mysql database issues since upgrading to > 7.2 (from > >> 7.0.2). I run mysql innodb with 900Gb database that's largely > the File > >> table. > > > > For large catalog, we usually advise to use PostgreSQL where we have > > multi-terabytes databases in production. > > > >> Since upgrading, I lose a few jobs a night due to database locking > >> timeouts, which I have set to 3600. I also log slow queries. > > > > Can you get some information about these locks? On which table? > Can you > > give some statistics on your catalog like the size and the number of > > records of the File, Filename and Path table? Would be nice also > if you > > can give the number of Filename per Client (from the job table). > > > > You might have many orphan Filenames, and MySQL is not always > very good > > to join large tables (it uses nested loops, and cannot use the > index on > > the Text column in all queries). > > > >> It appears that typically during a months I have about 90-100 > queries > >> that take longer than 15 minutes to run. Already this month > (upgraded > >> earlier this week), I have 32 queries that take longer than 15 > minutes. > >> At this rate (after 2 days) that will up my regular average > of 90-100 > >> to 480! > >> > >> Something is wrong and the coincidence is pretty strong that it's > >> related to the upgrade. > > > > Maybe, but I'm not sure, we did not change a lot of thing in this > area, > > we did mostly refactoring. > > > > Best Regards, > > Eric > > > > -- > Stephen Thompson Berkeley Seismological Laboratory > st...@se... <mailto:st...@se...> > 215 McCone Hall # 4760 > Office: 510.664.9177 <tel:510.664.9177> University of > California, Berkeley > Remote: 510.214.6506 <tel:510.214.6506> (Tue,Wed) Berkeley, CA > 94720-4760 > > ------------------------------------------------------------------------------ > _______________________________________________ > Bacula-users mailing list > Bac...@li... > <mailto:Bac...@li...> > https://lists.sourceforge.net/lists/listinfo/bacula-users > > |
From: Ana E. M. A. <emi...@gm...> - 2015-10-09 12:24:38
|
Hello Eric! Thank you. I thought that you were looking for the number of filename per Client that had not been pruned yet :). Best regards, Ana On Fri, Oct 9, 2015 at 3:17 AM, Eric Bollengier < eri...@ba...> wrote: > Thanks Ana! > > Something such as > > SELECT ClientId, SUM(JobFiles) AS NB FROM Job GROUP BY ClientId ORDER BY > NB DESC; > > should also do the trick a bit more faster ;-) > > Best Regards, > Eric > > Le 07. 10. 15 15:23, Ana Emília M. Arruda a écrit : > >> Hello Stephen, >> >> On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson >> <st...@se... <mailto:st...@se...>> wrote: >> >> >> Regarding: >> > Would be nice also if you can give the number of Filename per >> Client >> (from the job table). >> >> Do you have a sample SQL to retrieve this stat? >> >> >> select Client.Name, count(distinct Filename.FilenameId) from Client, >> Filename, File, Job where Filename.FilenameId=File.FilenameId and >> File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by >> Client.ClientId; >> >> The above query should work. >> >> Best regards, >> Ana >> >> >> >> thanks, >> Stephen >> >> >> >> >> >> >> >> On 10/03/2015 12:02 AM, Eric Bollengier wrote: >> > Hello Stephen, >> > >> > On 10/03/2015 12:00 AM, Stephen Thompson wrote: >> >> >> >> >> >> All, >> >> >> >> I believe I'm having mysql database issues since upgrading to >> 7.2 (from >> >> 7.0.2). I run mysql innodb with 900Gb database that's largely >> the File >> >> table. >> > >> > For large catalog, we usually advise to use PostgreSQL where we >> have >> > multi-terabytes databases in production. >> > >> >> Since upgrading, I lose a few jobs a night due to database locking >> >> timeouts, which I have set to 3600. I also log slow queries. >> > >> > Can you get some information about these locks? On which table? >> Can you >> > give some statistics on your catalog like the size and the number >> of >> > records of the File, Filename and Path table? Would be nice also >> if you >> > can give the number of Filename per Client (from the job table). >> > >> > You might have many orphan Filenames, and MySQL is not always >> very good >> > to join large tables (it uses nested loops, and cannot use the >> index on >> > the Text column in all queries). >> > >> >> It appears that typically during a months I have about 90-100 >> queries >> >> that take longer than 15 minutes to run. Already this month >> (upgraded >> >> earlier this week), I have 32 queries that take longer than 15 >> minutes. >> >> At this rate (after 2 days) that will up my regular average >> of 90-100 >> >> to 480! >> >> >> >> Something is wrong and the coincidence is pretty strong that it's >> >> related to the upgrade. >> > >> > Maybe, but I'm not sure, we did not change a lot of thing in this >> area, >> > we did mostly refactoring. >> > >> > Best Regards, >> > Eric >> > >> >> -- >> Stephen Thompson Berkeley Seismological Laboratory >> st...@se... <mailto:st...@se...> >> 215 McCone Hall # 4760 >> Office: 510.664.9177 <tel:510.664.9177> University of >> California, Berkeley >> Remote: 510.214.6506 <tel:510.214.6506> (Tue,Wed) Berkeley, CA >> 94720-4760 >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> Bacula-users mailing list >> Bac...@li... >> <mailto:Bac...@li...> >> https://lists.sourceforge.net/lists/listinfo/bacula-users >> >> >> |
From: Eric B. <eri...@ba...> - 2015-10-09 17:02:01
|
Very good point Ana, So, you might want to add to the query "AND PurgedFiles = 0" Thanks, Eric Le 09. 10. 15 14:24, Ana Emília M. Arruda a écrit : > Hello Eric! > > Thank you. I thought that you were looking for the number of filename > per Client that had not been pruned yet :). > > Best regards, > Ana > > On Fri, Oct 9, 2015 at 3:17 AM, Eric Bollengier > <eri...@ba... > <mailto:eri...@ba...>> wrote: > > Thanks Ana! > > Something such as > > SELECT ClientId, SUM(JobFiles) AS NB FROM Job GROUP BY ClientId > ORDER BY NB DESC; > > should also do the trick a bit more faster ;-) > > Best Regards, > Eric > > Le 07. 10. 15 15:23, Ana Emília M. Arruda a écrit : > > Hello Stephen, > > On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson > <st...@se... > <mailto:st...@se...> > <mailto:st...@se... > <mailto:st...@se...>>> wrote: > > > Regarding: > > Would be nice also if you can give the number of > Filename per Client > (from the job table). > > Do you have a sample SQL to retrieve this stat? > > > select Client.Name, count(distinct Filename.FilenameId) from > Client, > Filename, File, Job where Filename.FilenameId=File.FilenameId and > File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by > Client.ClientId; > > The above query should work. > > Best regards, > Ana > > > > thanks, > Stephen > > > > > > > > On 10/03/2015 12:02 AM, Eric Bollengier wrote: > > Hello Stephen, > > > > On 10/03/2015 12:00 AM, Stephen Thompson wrote: > >> > >> > >> All, > >> > >> I believe I'm having mysql database issues since > upgrading to > 7.2 (from > >> 7.0.2). I run mysql innodb with 900Gb database that's > largely > the File > >> table. > > > > For large catalog, we usually advise to use PostgreSQL > where we have > > multi-terabytes databases in production. > > > >> Since upgrading, I lose a few jobs a night due to > database locking > >> timeouts, which I have set to 3600. I also log slow > queries. > > > > Can you get some information about these locks? On which > table? > Can you > > give some statistics on your catalog like the size and > the number of > > records of the File, Filename and Path table? Would be > nice also > if you > > can give the number of Filename per Client (from the job > table). > > > > You might have many orphan Filenames, and MySQL is not > always > very good > > to join large tables (it uses nested loops, and cannot > use the > index on > > the Text column in all queries). > > > >> It appears that typically during a months I have about > 90-100 > queries > >> that take longer than 15 minutes to run. Already this > month > (upgraded > >> earlier this week), I have 32 queries that take longer > than 15 > minutes. > >> At this rate (after 2 days) that will up my regular > average > of 90-100 > >> to 480! > >> > >> Something is wrong and the coincidence is pretty strong > that it's > >> related to the upgrade. > > > > Maybe, but I'm not sure, we did not change a lot of > thing in this > area, > > we did mostly refactoring. > > > > Best Regards, > > Eric > > > > -- > Stephen Thompson Berkeley Seismological > Laboratory > st...@se... <mailto:st...@se...> > <mailto:st...@se... > <mailto:st...@se...>> > 215 McCone Hall # 4760 > Office: 510.664.9177 <tel:510.664.9177> <tel:510.664.9177 > <tel:510.664.9177>> University of > California, Berkeley > Remote: 510.214.6506 <tel:510.214.6506> <tel:510.214.6506 > <tel:510.214.6506>> (Tue,Wed) Berkeley, CA > 94720-4760 > > > ------------------------------------------------------------------------------ > _______________________________________________ > Bacula-users mailing list > Bac...@li... > <mailto:Bac...@li...> > <mailto:Bac...@li... > <mailto:Bac...@li...>> > https://lists.sourceforge.net/lists/listinfo/bacula-users > > > |
From: Stephen T. <st...@se...> - 2015-10-09 21:13:40
|
mysql> SELECT ClientId, SUM(JobFiles) AS NB FROM Job WHERE PurgedFiles=0 GROUP BY ClientId ORDER BY NB DESC; +----------+------------+ | ClientId | NB | +----------+------------+ | 10 | 2239841562 | | 68 | 636728984 | | 157 | 291057196 | | 198 | 138969710 | | 226 | 64823112 | | 175 | 32000653 | | 193 | 31050266 | | 82 | 30262301 | | 128 | 29301627 | | 151 | 22475008 | | 19 | 21240274 | | 29 | 20033877 | | 83 | 19478910 | | 11 | 19457193 | | 71 | 19082044 | | 182 | 17809346 | | 2 | 16574343 | | 160 | 16026000 | | 59 | 15619494 | | 208 | 15302525 | | 137 | 14738802 | | 211 | 14222314 | | 87 | 13960994 | | 52 | 13780046 | | 161 | 13779654 | | 185 | 13302479 | | 37 | 13225958 | | 67 | 13109596 | | 20 | 12671550 | | 51 | 12466348 | | 181 | 12251756 | | 202 | 12042804 | | 171 | 11780484 | | 1 | 11757542 | | 32 | 11638635 | | 110 | 11638577 | | 149 | 11204765 | | 212 | 10868461 | | 130 | 10852372 | | 97 | 10595084 | | 86 | 10499474 | | 190 | 10494196 | | 150 | 10333134 | | 78 | 10217282 | | 199 | 10180544 | | 91 | 10130751 | | 79 | 9970247 | | 58 | 9810940 | | 147 | 9674228 | | 104 | 9596818 | | 116 | 9452029 | | 89 | 9332180 | | 165 | 9212989 | | 188 | 9188834 | | 105 | 8983345 | | 77 | 8949849 | | 99 | 8831861 | | 203 | 8754005 | | 153 | 8529691 | | 142 | 8289018 | | 9 | 8094114 | | 23 | 7835491 | | 74 | 7752206 | | 117 | 7734697 | | 169 | 7713724 | | 98 | 7676700 | | 114 | 7596313 | | 103 | 7594553 | | 3 | 7569220 | | 22 | 7534628 | | 163 | 7394539 | | 80 | 7327367 | | 191 | 7290254 | | 133 | 7278652 | | 187 | 7238320 | | 73 | 7236988 | | 24 | 7135758 | | 13 | 7128109 | | 55 | 6963791 | | 81 | 6911802 | | 173 | 6876827 | | 109 | 6755680 | | 92 | 6665835 | | 177 | 6600357 | | 136 | 6533864 | | 210 | 6520067 | | 101 | 6512996 | | 108 | 6451729 | | 18 | 6318452 | | 152 | 6279522 | | 8 | 6222276 | | 107 | 6211843 | | 178 | 6173749 | | 144 | 6145270 | | 93 | 6142275 | | 139 | 6111513 | | 15 | 6060498 | | 197 | 6051544 | | 158 | 6025219 | | 179 | 5961047 | | 170 | 5936383 | | 167 | 5927953 | | 66 | 5840134 | | 47 | 5747998 | | 33 | 5723391 | | 64 | 5639616 | | 168 | 5566232 | | 7 | 5564096 | | 102 | 5557595 | | 126 | 5529101 | | 155 | 5518324 | | 84 | 5473028 | | 70 | 5406265 | | 16 | 5327062 | | 75 | 5320136 | | 196 | 5315381 | | 176 | 5230530 | | 27 | 5197330 | | 54 | 5179433 | | 180 | 5161361 | | 12 | 5112984 | | 76 | 5084587 | | 17 | 5044930 | | 4 | 5037036 | | 207 | 4940761 | | 45 | 4811348 | | 132 | 4737109 | | 156 | 4595621 | | 28 | 4532633 | | 218 | 4480522 | | 40 | 4413881 | | 140 | 4237828 | | 217 | 4090761 | | 209 | 3837518 | | 127 | 3519580 | | 205 | 3334936 | | 122 | 2912512 | | 219 | 2852438 | | 118 | 2697189 | | 223 | 2458155 | | 120 | 2374121 | | 85 | 2131980 | | 184 | 1997627 | | 39 | 1921967 | | 215 | 1434650 | | 94 | 1346155 | | 119 | 1344102 | | 6 | 1339676 | | 216 | 1314965 | | 131 | 1284235 | | 69 | 1045568 | | 143 | 1041213 | | 129 | 1000119 | | 220 | 899042 | | 65 | 825059 | | 21 | 784129 | | 72 | 780538 | | 90 | 780387 | | 192 | 735048 | | 221 | 700934 | | 222 | 700639 | | 106 | 673953 | | 121 | 666393 | | 225 | 609158 | | 30 | 584147 | | 224 | 549788 | | 95 | 492221 | | 138 | 485395 | | 206 | 462543 | | 115 | 447957 | | 96 | 437252 | | 213 | 395689 | | 26 | 394206 | | 166 | 366626 | | 154 | 339426 | | 164 | 325200 | | 36 | 313950 | | 88 | 292422 | | 100 | 292099 | | 214 | 277613 | | 159 | 274184 | | 162 | 273890 | | 189 | 252673 | | 123 | 234201 | | 35 | 219973 | | 41 | 219826 | | 53 | 219767 | | 63 | 219749 | | 135 | 219746 | | 141 | 219344 | | 124 | 219157 | | 57 | 219070 | | 134 | 215349 | | 227 | 154642 | | 112 | 134792 | | 125 | 114623 | | 31 | 99493 | | 49 | 98341 | | 34 | 92193 | | 50 | 90190 | | 46 | 88746 | | 111 | 87960 | | 148 | 70591 | | 62 | 68151 | | 145 | 65377 | | 42 | 65290 | | 25 | 63220 | | 60 | 62653 | | 38 | 62183 | | 43 | 46063 | | 228 | 45989 | | 44 | 45433 | | 113 | 44317 | | 186 | 1 | | 5 | 0 | | 56 | 0 | | 172 | 0 | | 195 | 0 | | 174 | 0 | | 48 | 0 | | 61 | 0 | +----------+------------+ 221 rows in set (0.21 sec) On 10/09/2015 10:01 AM, Eric Bollengier wrote: > Very good point Ana, > > So, you might want to add to the query "AND PurgedFiles = 0" > > Thanks, > > Eric > > Le 09. 10. 15 14:24, Ana Emília M. Arruda a écrit : >> Hello Eric! >> >> Thank you. I thought that you were looking for the number of filename >> per Client that had not been pruned yet :). >> >> Best regards, >> Ana >> >> On Fri, Oct 9, 2015 at 3:17 AM, Eric Bollengier >> <eri...@ba... >> <mailto:eri...@ba...>> wrote: >> >> Thanks Ana! >> >> Something such as >> >> SELECT ClientId, SUM(JobFiles) AS NB FROM Job GROUP BY ClientId >> ORDER BY NB DESC; >> >> should also do the trick a bit more faster ;-) >> >> Best Regards, >> Eric >> >> Le 07. 10. 15 15:23, Ana Emília M. Arruda a écrit : >> >> Hello Stephen, >> >> On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson >> <st...@se... >> <mailto:st...@se...> >> <mailto:st...@se... >> <mailto:st...@se...>>> wrote: >> >> >> Regarding: >> > Would be nice also if you can give the number of >> Filename per Client >> (from the job table). >> >> Do you have a sample SQL to retrieve this stat? >> >> >> select Client.Name, count(distinct Filename.FilenameId) from >> Client, >> Filename, File, Job where Filename.FilenameId=File.FilenameId and >> File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by >> Client.ClientId; >> >> The above query should work. >> >> Best regards, >> Ana >> >> >> >> thanks, >> Stephen >> >> >> >> >> >> >> >> On 10/03/2015 12:02 AM, Eric Bollengier wrote: >> > Hello Stephen, >> > >> > On 10/03/2015 12:00 AM, Stephen Thompson wrote: >> >> >> >> >> >> All, >> >> >> >> I believe I'm having mysql database issues since >> upgrading to >> 7.2 (from >> >> 7.0.2). I run mysql innodb with 900Gb database that's >> largely >> the File >> >> table. >> > >> > For large catalog, we usually advise to use PostgreSQL >> where we have >> > multi-terabytes databases in production. >> > >> >> Since upgrading, I lose a few jobs a night due to >> database locking >> >> timeouts, which I have set to 3600. I also log slow >> queries. >> > >> > Can you get some information about these locks? On which >> table? >> Can you >> > give some statistics on your catalog like the size and >> the number of >> > records of the File, Filename and Path table? Would be >> nice also >> if you >> > can give the number of Filename per Client (from the job >> table). >> > >> > You might have many orphan Filenames, and MySQL is not >> always >> very good >> > to join large tables (it uses nested loops, and cannot >> use the >> index on >> > the Text column in all queries). >> > >> >> It appears that typically during a months I have about >> 90-100 >> queries >> >> that take longer than 15 minutes to run. Already this >> month >> (upgraded >> >> earlier this week), I have 32 queries that take longer >> than 15 >> minutes. >> >> At this rate (after 2 days) that will up my regular >> average >> of 90-100 >> >> to 480! >> >> >> >> Something is wrong and the coincidence is pretty strong >> that it's >> >> related to the upgrade. >> > >> > Maybe, but I'm not sure, we did not change a lot of >> thing in this >> area, >> > we did mostly refactoring. >> > >> > Best Regards, >> > Eric >> > >> >> -- >> Stephen Thompson Berkeley Seismological >> Laboratory >> st...@se... <mailto:st...@se...> >> <mailto:st...@se... >> <mailto:st...@se...>> >> 215 McCone Hall # 4760 >> Office: 510.664.9177 <tel:510.664.9177> <tel:510.664.9177 >> <tel:510.664.9177>> University of >> California, Berkeley >> Remote: 510.214.6506 <tel:510.214.6506> <tel:510.214.6506 >> <tel:510.214.6506>> (Tue,Wed) Berkeley, CA >> 94720-4760 >> >> >> >> ------------------------------------------------------------------------------ >> >> _______________________________________________ >> Bacula-users mailing list >> Bac...@li... >> <mailto:Bac...@li...> >> <mailto:Bac...@li... >> <mailto:Bac...@li...>> >> https://lists.sourceforge.net/lists/listinfo/bacula-users >> >> >> -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall # 4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Stephen T. <st...@se...> - 2015-10-12 14:33:56
|
update... After adding more RAM, we are back to getting a about 3 queries a day that run longer than 15 minutes. This was our norm before upgrading. No job errors since the first couple days from this month (Oct). Not sure if the reduction in long running queries was actually from additional RAM or not, since last week before adding RAM, the number of long running queries per day had already greatly diminished since beginning of month. So, I guess, problem solved for now, though I'm not completely confident about what actually happened or if I did anything to fix it. Oh, well. Stephen On 10/9/15 2:08 PM, Stephen Thompson wrote: > > > Eric, > > I appreciate all the feedback. We went through a few iterations of > tuning awhile back and have not generally had any significant issues > over the years with database responsiveness. > > Back to the original post, it's only been since our upgrade that we > started having database lock timeout issues. Otherwise we've run for > years (6 or so) without issue. We also went through an orphan record > cleanout earlier this year. > > Stat wise, it looks like our slow queries are still happening at twice > the rate compared to recent months, but half as often as they were when > I first reported the issue a week ago, so I am equally nonplussed about > the improvement as I was about the lockouts. > > I did get a chance to double the ram from 8 to 16GB today though > unfortunately we don't have the ready resources to do many hardware > upgrades, though I quite understand why that's a recommendation. > > Stephen > > > > On 10/08/2015 10:58 PM, Eric Bollengier wrote: >> Hello Stephen, >> >> >> Le 05. 10. 15 19:17, Stephen Thompson a écrit : >>> >>> Eric, >>> >>> Thanks for the reply. >>> >>> I've heard the postgres recommendation a fair number of times. A couple >>> years back, we setup a parallel instance but even after tuning still >>> wound up with _worse_ performance than with mysql. I could not figure >>> out what to attribute this to (because it was in such contrast to all >>> the pro-postgres recommendations) except possibly our memory-poor server >>> - 8Gb RAM. >>> >>> At any rate, the only thing that's changed was the upgrade from 7.0.5 to >>> 7.2.0. The table involved is definitely the File table. We do have >>> jobs with 20-30 million records, so those jobs can be slow when it comes >>> time for attribute insertion into the database (or to read out a file >>> list for Accurate backups). This why we've historically had innodb lock >>> timeout of 3600. However, it's only last week after the upgrade that >>> we've ever had queries extend beyond that hour mark. >>> >>> We also went through a database cleaning process last month due to >>> nearly reaching 1Tb and I can pretty authoritatively claim that we don't >>> have orphan records. The database content and schema all appear to be >>> appropriate. >> >> A 1TB database (running either Postgresql, MySQL or whatever other kind >> of product) should be carefully tuned and monitored. My guess would be >> that your my.cnf settings are not suitable for such database size. You >> can run a tool such as MySQLtuner to check that everything is ok on >> MySQL side, increase the size of the memory of your server or try to >> cleanup orphan filename records. >> >> The size of the File table should not impact performances on Backup, but >> other tables such as Path or Filename are important (and they are pretty >> big on your site). >> >> > I was worried that queries had been rewritten that made it >> > more efficient for other databases, but less so for mysql. >> >> We didn't wrote database query specifically for PostgreSQL or MySQL but >> we optimize them when it's possible, some SQLite queries were optimized >> by a contributor 2 or 3 years ago, and it was way faster for some parts >> of Bacula afterward. >> >> If you look the database world from outside, you might think that >> everything is nice and smooth because all products seem to talk the >> same language (SQL), but they all have a different way to handle the >> work and the SQL specifications (and the lack of specifications). >> For myself, I'm a PostgreSQL user for a quite long time, I have good >> relationships with the PostgreSQL community, and we got huge help when >> we wrote the "Batch Mode" few years ago. I know that it works well and >> we can analyze problems quite easily, doing so I always advise strongly >> to use PostgreSQL for all large setup. For other products, developers >> uses MySQL and the PostgreSQL driver is not good at all. >> >> With the time, I found that you can do "more" with "less" hardware when >> using the PostgreSQL catalog. In your case (a fairly big database), it >> might be the time to spend a bit of money to get more RAM and/or make >> sure that your Path/Filename indexes stay in RAM. >> >> >> Hope it helps. >> >> Best Regards, >> Eric >> >>> >>> >>> More info... >>> >>> example from slow query logfile: >>> # Time: 151001 1:28:14 >>> # User@Host: bacula[bacula] @ localhost [] >>> # Query_time: 3675.052083 Lock_time: 73.719795 Rows_sent: 0 >>> Rows_examined: 3 >>> SET timestamp=1443688094; >>> INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, >>> DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, >>> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch >>> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = >>> Filename.Name); >>> >>> mysqld: >>> mysql-5.1.73-5.el6_6.x86_64 >>> >>> record counts per table: >>> File 4,315,675,600 >>> Filename 154,748,787 >>> Path 28,534,411 >>> >>> innodb file sizes: >>> 847708500 File.ibd >>> 19488772 Filename.ibd >>> 8216580 Path.ibd >>> 106500 PathHierarchy.ibd >>> 57344 JobMedia.ibd >>> 40960 PathVisibility.ibd >>> 27648 Job.ibd >>> 512 Media.ibd >>> 176 FileSet.ibd >>> 144 JobHisto.ibd >>> 144 Client.ibd >>> 112 RestoreObject.ibd >>> 112 Pool.ibd >>> 112 Log.ibd >>> 112 BaseFiles.ibd >>> 96 Version.ibd >>> 96 UnsavedFiles.ibd >>> 96 Storage.ibd >>> 96 Status.ibd >>> 96 MediaType.ibd >>> 96 LocationLog.ibd >>> 96 Location.ibd >>> 96 Device.ibd >>> 96 Counters.ibd >>> 96 CDImages.ibd >>> 4 Snapshot.MYI >>> 0 Snapshot.MYD >>> >>> >>> >>> Not related, but I just noticed that somehow the new Snapshot table is >>> MyISAM format. How did that happen? >>> >>> Regarding: >>> > Would be nice also if you can give the number of Filename per Client >>> (from the job table). >>> >>> Do you have a sample SQL to retrieve this stat? >>> >>> >>> thanks, >>> Stephen >>> >>> >>> >>> >>> >>> >>> >>> On 10/03/2015 12:02 AM, Eric Bollengier wrote: >>>> Hello Stephen, >>>> >>>> On 10/03/2015 12:00 AM, Stephen Thompson wrote: >>>>> >>>>> >>>>> All, >>>>> >>>>> I believe I'm having mysql database issues since upgrading to 7.2 (from >>>>> 7.0.2). I run mysql innodb with 900Gb database that's largely the File >>>>> table. >>>> >>>> For large catalog, we usually advise to use PostgreSQL where we have >>>> multi-terabytes databases in production. >>>> >>>>> Since upgrading, I lose a few jobs a night due to database locking >>>>> timeouts, which I have set to 3600. I also log slow queries. >>>> >>>> Can you get some information about these locks? On which table? Can you >>>> give some statistics on your catalog like the size and the number of >>>> records of the File, Filename and Path table? Would be nice also if you >>>> can give the number of Filename per Client (from the job table). >>>> >>>> You might have many orphan Filenames, and MySQL is not always very good >>>> to join large tables (it uses nested loops, and cannot use the index on >>>> the Text column in all queries). >>>> >>>>> It appears that typically during a months I have about 90-100 queries >>>>> that take longer than 15 minutes to run. Already this month (upgraded >>>>> earlier this week), I have 32 queries that take longer than 15 minutes. >>>>> At this rate (after 2 days) that will up my regular average of >>>>> 90-100 >>>>> to 480! >>>>> >>>>> Something is wrong and the coincidence is pretty strong that it's >>>>> related to the upgrade. >>>> >>>> Maybe, but I'm not sure, we did not change a lot of thing in this area, >>>> we did mostly refactoring. >>>> >>>> Best Regards, >>>> Eric >>>> >>> > -- Stephen Thompson Berkeley Seismological Laboratory st...@se... 215 McCone Hall #4760 Office: 510.664.9177 University of California, Berkeley Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760 |
From: Uwe S. <uwe...@ni...> - 2015-10-14 09:49:30
|
On Mon, Oct 12, 2015 at 07:33:46AM -0700, Stephen Thompson wrote: > > update... > > After adding more RAM, we are back to getting a about 3 queries a day > that run longer than 15 minutes. This was our norm before upgrading. > No job errors since the first couple days from this month (Oct). Not > sure if the reduction in long running queries was actually from > additional RAM or not, since last week before adding RAM, the number of > long running queries per day had already greatly diminished since > beginning of month. > > So, I guess, problem solved for now, though I'm not completely confident > about what actually happened or if I did anything to fix it. > Oh, well. > > Stephen Hi Stephen, you might also try giving MariaDB a shot which has been performing fine as a drop-in mysql replacement for us for the last few years with catalogs of similar size. Cheers, Uwe |
From: Alan B. <aj...@ms...> - 2015-10-14 15:20:23
|
On 14/10/15 10:49, Uwe Schuerkamp wrote: > > you might also try giving MariaDB a shot which has been performing > fine as a drop-in mysql replacement for us for the last few years with > catalogs of similar size. > Speaking as a mysql affectionado and one who's using both DBs regularly in various applications: If you have large catalogs there is _no_ substitute for using postgresql. I spent years regularly tuning mysql for large memory loads and then quite a while kicking myself that I'd resisted moving to PGsql all that time - since the changeover I've barely had to touch database tuning. MySQL/Maria are good at what they do, but they don't scale to large datasets very well. PGsql is overkill for a small dataset but it scales to large ones extremely well. (48Gb machine for the database. Mysql was using most of the ram and using tmpfiles. After changing to PGsql the memory footprint seldom exceeds 12Gb.) Changing over is relatively easy, _BUT_ you have to make sure you make your mysqldump in full compatibility mode and that means that it's slow to import into PGsql. Budget a day for this step. If you're determined to stick with MySQL then the solution for seriously slow queries is to find out which tables are causing the bottleneck and to index appropriate columns. The EXPLAIN and ANALYZE commands are your friends. Let's not get into religious arguments about XYZ database is better. The answer is that "better" depends what kinds of loads are being thrown at them. Mysql is good on catalogs and PGsql is better on large ones. I think the crossover point is between 10 and 20 million entries. |