Eric,
I appreciate all the feedback. We went through a few iterations of
tuning awhile back and have not generally had any significant issues
over the years with database responsiveness.
Back to the original post, it's only been since our upgrade that we
started having database lock timeout issues. Otherwise we've run for
years (6 or so) without issue. We also went through an orphan record
cleanout earlier this year.
Stat wise, it looks like our slow queries are still happening at twice
the rate compared to recent months, but half as often as they were when
I first reported the issue a week ago, so I am equally nonplussed about
the improvement as I was about the lockouts.
I did get a chance to double the ram from 8 to 16GB today though
unfortunately we don't have the ready resources to do many hardware
upgrades, though I quite understand why that's a recommendation.
Stephen
On 10/08/2015 10:58 PM, Eric Bollengier wrote:
> 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
>>>
>>
--
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
|
