On Thursday 21 September 2006 08:41, Uwe Schuerkamp wrote: > On Wed, Sep 20, 2006 at 03:10:27PM +0200, Kern Sibbald wrote: > > > > 10 GB is pretty big, but there are users with databases that large. Take a > > look at src/cats/make_mysql_tables. There are a few tips in that file for > > creating extra indexes if you have slow pruning. Once you have the indexes > > properly setup for your site requirements, pruning should run in a maximum of > > a couple of minutes. > > > > Hello Ken et al., > > I created another index on the File table as recommended in the > documentation yesterday which took about one hour to create (the File > table data file is around 6 GB), because I wasn't sure about MySQL's > index display format, but it seems I created an identical index to one > already there (the new one is called file_jfp_index, sorry for the > crummy formatting): > > mysql> show index from File ; > +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | > Comment | > +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | File | 0 | PRIMARY | 1 | FileId | A > | 59778107 | NULL | | | BTREE | > | > | File | 1 | JobId | 1 | JobId | A > | 318 | NULL | | | BTREE | > | > | File | 1 | JobId_2 | 1 | JobId | A > | 318 | NULL | | | BTREE | > | > | File | 1 | JobId_2 | 2 | PathId | A > | 4598315 | NULL | | | BTREE | > | > | File | 1 | JobId_2 | 3 | FilenameId | A > | 59778107 | NULL | | | BTREE | > | > | File | 1 | file_jfp_idx | 1 | JobId | A > | 318 | NULL | | | BTREE | > | > | File | 1 | file_jfp_idx | 2 | FilenameId | A > | 29889053 | NULL | | | BTREE | > | > | File | 1 | file_jfp_idx | 3 | PathId | A > | 59778107 | NULL | | | BTREE | > | > +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > 8 rows in set (0.00 sec) > > > So to me, it looks like the new index is identical in setup to the one > that's been there all along called JobId_2. As a consequence, I don't > expect any performance improvements when it comes to pruning the next > time around. Is this correct? > > As per Ken's recommendation, I checked the table creation script, and > it seems not to mention any additional indices I should create that > aren't already there: > > > # > # 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), > # > > I don't have a combined (for lack of a better expression) index on > (FilenameId, PathId), but wouldn't that just be a subset of the > (Job, Filename, Path) index? Also, the comment talks about slow > verifies, but my problem is slow pruning ;-) > > Should I drop the 2nd, identical index? Backup last night was nearly > as fast as before (varying from 10MB/sec down to 3MB/sec depending on > the client), so it doesn't seem to hurt in a major way.
I'm not convinced the indexes are the same. Some people say yes, some say no, and it seems to be also dependent on whether one is talking about MySQL as you are or PostgreSQL. The best solution is to test for your particular case. > > Again, if I'm missing some crucial information, let me know and I'll > be happy to provide what I can. Below are the my.cnf parameters > relevant to the server: > > # > # * Fine Tuning > # > key_buffer = 256M > max_allowed_packet = 16M > thread_stack = 128K > record_buffer = 256M > sort_buffer_size = 64M > myisam_sort_buffer_size = 64M > # > # * Query Cache Configuration > # > query_cache_limit = 6448576 > query_cache_size = 64777216 > query_cache_type = 1 > > Mysqld uses around 500-700MB RSS memory of 1GB total (sorry, got that > wrong in my last post, the server only has 1 GB RAM installed), > depending on activity. > > All the best & thanks in advance for your comments, > > Uwe > > -- > Uwe Schuerkamp, NIONEX GmbH (http://www.nionex.com/) > [EMAIL PROTECTED] Tel: +49 (0)5241 / 80 10 66 FAX: / 806 23 38 > Avenwedder Str. 55, D-33311 Guetersloh, Germany > GnuPG KeyID: 5887047D, Fingerprint: 2E1320229A3F63 7F676FE9B1A836A461 > > ------------------------------------------------------------------------- > 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 > ------------------------------------------------------------------------- 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
