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. 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