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