On 2006-09-20 15:44, Bill Moran wrote: > In response to Birger Blixt <[EMAIL PROTECTED]>: > >> Frank Sweetser wrote: >>> On Thu, Sep 07, 2006 at 10:58:49AM +0200, Masopust, Christian wrote: >>>> Hello Frank, >>>> >>>> thanks a lot for this info! but.... :-))) >>>> >>>> could anybody give the complete info (maybe also modify the wiki-page) how >>>> these indexes should be created? >>> The best place to find that is in the documentation for your database. That >>> way you can make sure you're getting accurate information, as opposed to >>> something for a previous version or the like. >> Amazing, my dbcheck at home was searching for orphaned Filename records >> the whole day, and I finaly did mysaladmin kill on the query. >> >> Then I did: >> >> mysql bacula >> >> create index Job_FilesetId_idx on Job(FileSetId); >> create index Job_ClientId_idx on Job(ClientId); > > I find it hard to believe that these indexes help much, unless you've got > a boatload of jobs in the system. I doubt they'll hurt anything, > though. > >> create index File_PathId_idx on File(PathId); >> create index File_FilenameId_idx on File(FilenameId); > > One of these two is redundant. There's already an index: > "file_fp_idx" btree (filenameid, pathid) CLUSTER > which should be usable by queries searching on filenameid (this is > PostgreSQL, but the idea is the same) Creating another index on > filenameid is just bloat and will slow down inserts. > > Personally, I don't recommend that anyone blindly create these > indexes. As you mentioned, it took over 30 minutes to create them, > which seems to indicate that their existence will have a negative > impact on inserts and updates. > > In my case, none of these indexes made a significant improvement to > dbcheck time. dbcheck took 5-1/2 minutes to run with and without the > above indexes (I have about 9,000,000 rows in the file table and > 500,000 in the filename table). > > Try them out ... if they make a significant improvement, use them. >
Well, I admit, I don't know what I'm doing in this case, but ,,, the search query _did_ take forever, and I canceled it at the end. With new indexes it goes fast, so something did happen. I was checking the script http://www.aha.com/bacula/recover.pl There I found: "In order for this program to have a chance of not being painfully slow, the following indexs should be added to your database. CREATE INDEX file_pathid_idx on file(pathid); CREATE INDEX file_filenameid_idx on file(filenameid); " I took that as an example, and added them, and the 2 Job indexes as a test, there is always a drop index on table command to remove them if it don't works. I should enjoy if someone that really knows how to tune mysql could send an output from "show index from File" , so I can optimize the table (that goes for the table Job too I guess ) File 0 PRIMARY 1 FileId 4541609 BTREE File 1 JobId 1 JobId 576 BTREE File 1 JobId 2 PathId 378467 BTREE File 1 JobId 3 FilenameId 4541609 BTREE File 1 JobId_2 1 JobId 576 BTREE File 1 File_PathId_idx 1 PathId 105618 BTREE File 1 File_FilenameId_idx 1 FilenameId 504623 BTREE Maybe I can drop something here, who knows ? One source for information can be the make_mysql_tables script CREATE TABLE File ( ...... PRIMARY KEY(FileId), INDEX (JobId), 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), # /birre ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
