On Friday 10 June 2005 12:46, Ronan Eckelberry wrote: > Hey Kern, > > Just giving an update on this. I added your other suggested indexes, I > also upgraded to MySQL 4.1. That with the changes to my.cnf seem to > have sped up my backups quite a bit. I think a big thing was increasing > the memory for the keys. My top reports a 99.7% key efficiency now > also.
Thanks for the feedback. I'll add a section to the manual about performance on MySQL with something about the my.cnf section as I think a lot of people would like to know about this. > > I appreciate all the help you and everyone on the list have given. > I'll let you know if I have any more problems as I am constantly adding > servers with large amounts (80-500 gig) of web data. At some point if you start having performance problems with database bottlenecks, you might want to think about using multiple "Catalogs" within Bacula so you can have multiple simultaneous jobs writing to different MySQL databases at the same time. It might be slightly more difficult from an administrative standpoint, but the performance gains could be really substantial (assuming you are running multiple simultaneous jobs). > > Thanks again, > > -Ronan > > On Tue, 2005-05-24 at 10:22 +0200, Kern Sibbald wrote: > > 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 > > ------------------------------------------------------- > This SF.Net email is sponsored by: NEC IT Guy Games. How far can you > shotput a projector? How fast can you ride your desk chair down the office > luge track? If you want to score the big prize, get to know the little guy. > Play to win an NEC 61" plasma display: http://www.necitguy.com/?r=20 > _______________________________________________ > 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: NEC IT Guy Games. How far can you shotput a projector? How fast can you ride your desk chair down the office luge track? If you want to score the big prize, get to know the little guy. Play to win an NEC 61" plasma display: http://www.necitguy.com/?r=20 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
