>>>>> On Wed, 17 Jun 2009 13:48:58 +0200, Tom Sommer said:
>
> Martin Simmons wrote:
> >>>>>> On Tue, 16 Jun 2009 15:05:18 +0200, Tom Sommer said:
> >>>>>>
> >> Hi,
> >>
> >> I have a somewhat pressing problem with the performance of my Bacula
> >> installation.
> >>
> >> My MySQL database currently holds 247,342,127 (36GB) records in the File
> >> table, and 78,576,199 (10GB) records in the Filename table.
> >>
> >> Since 3.0.0, but even more since 3.0.1, I have a problem with queries
> >> being really slow. Basically when doing a full backup of a server
> >> (mailserver, LOTS of small files), I can have my MySQL hanging for up to
> >> 24+ hours on queries like this:
> >>
> >> INSERT INTO Filename( Name )
> >> SELECT a.Name
> >> FROM (
> >>
> >> SELECT DISTINCT Name
> >> FROM batch
> >> ) AS a
> >> WHERE NOT
> >> EXISTS (
> >>
> >> SELECT Name
> >> FROM Filename AS f
> >> WHERE f.Name = a.Name
> >> )
> >>
> >> with the status "Sending data", and a lot of other similar queries in
> >> queue with the status "Locked". One of these queries take approx 10.000
> >> seconds to execute, but is just followed by another similar (identical)
> >> query with the same duration. This is a problem mostly because it
> >> prevents me doing restores while backups are "Dir inserting attributes"
> >>
> >> Obviously I would think it's a MySQL performance issue, but I was
> >> wondering if anything had been done to the queries? They seem to be a
> >> LOT slower and a LOT heavier.
> >> I've just put more RAM into the server, but it's done little to improve
> >> the duration of the queries. My server now has 4GB RAM (will update to
> >> 6GB) - but again it's a recent issue, because Bacula has been running
> >> perfectly for many months on 2GB RAM, until I updated to 3.0.1
> >>
> >> I've done REPAIR TABLE and OPTIMIZE TABLE and seen no improvement.
> >>
> >> Finally if anyone have any specific ideas to improve performance on my
> >> huge SQL database, please share :)
> >>
> >
> > Sorry, I don't know how to optimize it.
> >
> > These queries are part of the new "batch insert" code. You can turn that
> > off
> > by passing --disable-batch-insert to configure when building Bacula.
> >
> what does this mean in practice? 1 query per file? or?
The non-batch approach makes several queries per file.
> My real problem is these huge queries lock the File table, so no other
> jobs can touch it for many hours.
The batch queries update the Filename and Path tables atomically, so locking
is expected.
__Martin
------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
