>>>>> 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
