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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-users
