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
stephen AT seismo.berkeley DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
