>>>>> On Thu, 18 Jun 2009 17:11:04 +0200, Michel Meyers said:
> 
> Martin Simmons wrote:
> >>>>>> 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
> >>>> )
> [...]
> >>> 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.
> 
> But it will do so as it backs up the files, as opposed to the batch
> approach where it collects all the queries and does them at the end of
> the job, right?

Yes, but if you have spooling turned on, then it will spool the attributes too
and insert them all at the end using several queries per file.

__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