>>>>> On Fri, 19 Jun 2009 09:51:20 +0200, Tom Sommer said:
> 
> Martin Simmons wrote:
> >>>>>> 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.
> >   
> Well, the problem is really that first is spends time storing the files,
> and then it spends time saving information about the backup in the database.
> It would make more sense, for larger clients, to spend the idle database
> time (while taking the file backup) more wisely.
> 
> I'm having a hard time judging if turning off batch-inserts is the right
> action, if I would gain anything from this.

AFAIK, the non-batch approach in Bacula 3.x does the same queies as Bacula
2.x.  Since you started the thread by saying that backups are slower in 3.x
because of the queries, turning off batch-inserts might solve that.

__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