Thanks a lot Kern. I've got all indexes atm except for "INDEX (FilenameId, PathId)". I'll add that and see what kind of effect it has. I'll reply back to the list with the results.
I think my problem may be the amount of small files. Most of the backups are web servers containing a lot of images, html, etc. As I said before any 1 server can have anywhere between 20-500G of web content alone. Do you think that I would be better off with Postgres, or should MySQL hold up? I have increased the RAM in the server and also added some lines to my.cnf: key_buffer = 512M sort_buffer = 32M read_buffer_size = 16M max_allowed_packet = 16M thread_stack = 128K query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1 It no longer stays pegged at 99%, but jumps from around 94-99. I currently have it running 8 concurrent jobs. Would slowing this down speed up the individual backups and the entire set as a whole? As I said, they started out pulling between 1-3Mb a piece, but are now all at about 50kb/sec. I'm sure this all has something to do with my setup, but just can't put my finger on it. I'll add that other index and let you know. Again, thanks for your reply, -Ronan On Fri, 2005-05-20 at 15:39 +0200, Kern Sibbald wrote: > Hello, > > Check to make sure you have all the recommended indexes on the File table: > > # ****FIXME**** make FileId BIGINT someday when MySQL works ***** > CREATE TABLE File ( > FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, > FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0, > JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, > PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, > FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, > MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0, > LStat TINYBLOB NOT NULL, > MD5 TINYBLOB NOT NULL, > PRIMARY KEY(FileId), > INDEX (JobId, PathId, FilenameId) > ); > > # > # Possibly add one or more of the following indexes > # to the above File table if your Verifies are > # too slow. > # > # INDEX (PathId), > # INDEX (FilenameId), > # INDEX (FilenameId, PathId) > # INDEX (JobId), > # > > If you don't have the triple index on JobId, PathId, FilenameId, you will > certainly have problems. The other indexes *should* not be necessary, but > you might try playing with them -- it just costs disk space. > > On Thursday 12 May 2005 07:43, Ronan Eckelberry wrote: > > Hi all, > > > > I run rather large backups, about 70+ servers, 1 Full a month, rest > > daily incrementals. All disk -> disk backups. At the time of full > > backups I can be pulling anywhere from 1.5-3TB of total data from the > > servers. > > > > Backups start out great, pulling a few megs a piece from about 8 boxes > > at a time. The problem is after about 12 hours of running, MySQL shoots > > up to 99% CPU, which is not really a problem because it is SMP, and box > > is still responsive. I check the processes on mysql and it is > > responding to queries quickly still. Backup speed however drops to > > about 20k/sec from each box. It continues to run, but after awhile the > > director stops responding altogether and all the bacula process show as > > sleeping. I am forced to restart bacula and lose the backups that were > > unfinished. > > > > Not sure if this is because of MySQL, which seems to be operating fine, > > or maybe bacula, RAM, or what. Below is what the server is. I can > > supply configs or whatever may be needed. If anyone has any suggestions > > or a similar setup that had the same type of problem and was resolved, > > it would be appreciated. > > > > Server: > > Debian Woody > > Kernel 2.4.26 > > P4 2.8 HT > > 1G RAM > > 2G Swap > > Intel S875WP MB > > > > Thanks for any help, > > > > -Ronan > > > > > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click > > _______________________________________________ > > Bacula-users mailing list > > Bacula-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/bacula-users > ------------------------------------------------------- This SF.Net email is sponsored by Oracle Space Sweepstakes Want to be the first software developer in space? Enter now for the Oracle Space Sweepstakes! http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
