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
>>
>
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
