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 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
