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
|
