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 step...@seismo.berkeley.edu 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 Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users