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