The most important index is: INDEX (JobId, PathId, FilenameId)
The others probably won't help in your case. This is different for PostgreSQL, where you will probably need an index on each of the above Ids by by themselves. The very first time you run Bacula, it will be a bit slower, but should speed up on subsequent backups. If you have any substantial number of hard links on your system, the FD will consume 100% CPU and be very slow. If the problem is in MySQL, then it is most likely due to lack of proper indexes. If the problem is in MySQL then reducing the number of concurrent jobs may help. You can create multiple databases and that will speed things up enormously, but is a bit harder to manage. From the reports I have seen, MySQL is generally 4-10 times faster than PostgreSQL for inserts, which is quite possibly your problem, but if you have the time to test, it might be worth a try. Don't be surprised if it is slower ... On Tuesday 24 May 2005 07:54, Ronan Eckelberry wrote: > 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 -- Best regards, Kern ("> /\ V_V ------------------------------------------------------- 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_idt12&alloc_id344&op=click _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
