Re: [Bacula-users] restore / slow directory tree build process on 5.0.x / mysql
2010-07-19 09:02:18
On 19/07/2010 12:33, Dan Langille wrote:
> You are probably missing an index. I'm pretty sure this has been discussed
> previously on this list. Sorry, I don't know which index is missing but I'm
> pretty sure it will be easy to find with a search.
I did look back over the list for a couple of months, but couldn't find
anything. Should have looked further.
Yes, this appears to have sorted the problem. There were two additional
indexes defined on the File table:
> +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | 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
> | 5711034 | NULL | NULL | | BTREE | |
> | File | 1 | JobId | 1 | JobId | A
> | 335 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 1 | JobId | A
> | 335 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 2 | PathId | A
> | 634559 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 3 | FilenameId | A
> | 5711034 | NULL | NULL | | BTREE | |
> | File | 1 | PathId_idx | 1 | PathId | A
> | 135977 | NULL | NULL | | BTREE | |
> | File | 1 | FilenameId_idx | 1 | FilenameId | A
> | 815862 | NULL | NULL | | BTREE | |
> +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Now that they've gone, the directory tree build time has gone down to 12
seconds.
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | 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 |
> 5711034 | NULL | NULL | | BTREE | |
> | File | 1 | JobId | 1 | JobId | A |
> 336 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 1 | JobId | A |
> 336 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 2 | PathId | A |
> 634559 | NULL | NULL | | BTREE | |
> | File | 1 | JobId_2 | 3 | FilenameId | A |
> 5711034 | NULL | NULL | | BTREE | |
> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
thanks for the suggestions.
Nick
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|