Gavin McCullagh wrote: > On Tue, 09 Nov 2010, Alan Brown wrote: > >>> and it still takes 14 minutes to build the tree on one of our bigger >>> clients. >>> We have 51 million entries in the file table. >>> >> Add individual indexes for Fileid, Jobid and Pathid >> >> Postgres will work with the combined index for individual table queries, >> but mysql won't. > > The following are the indexes on the file table: > > mysql> SHOW INDEXES 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 > | 55861148 | NULL | NULL | | BTREE | | > | File | 1 | PathId | 1 | PathId | A > | 735015 | NULL | NULL | | BTREE | | > | File | 1 | FilenameId | 1 | FilenameId | A > | 2539143 | NULL | NULL | | BTREE | | > | File | 1 | FilenameId | 2 | PathId | A > | 13965287 | NULL | NULL | | BTREE | | > | File | 1 | JobId | 1 | JobId | A > | 1324 | NULL | NULL | | BTREE | | > | File | 1 | JobId | 2 | PathId | A > | 2940060 | NULL | NULL | | BTREE | | > | File | 1 | JobId | 3 | FilenameId | A > | 55861148 | NULL | NULL | | BTREE | | > | File | 1 | jobid_index | 1 | JobId | A > | 1324 | NULL | NULL | | BTREE | | > | File | 1 | pathid_index | 1 | PathId | A > | 735015 | NULL | NULL | | BTREE | | > +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > > I added the last two per your instructions. Building the tree took about 14 > minutes without these indexes and takes about 17-18 minutes having added > them.
What tuning (if any) have you performed on your my.cnf and how much memory do you have? > Have I done something wrong? As FileId is a primary key, it doesn't seem > like I should need an extra index on that one -- is that wrong? It doesn't need an extra index. You've also got a duplicate pathid indeax which can be deleted. This kind of thing is why it makes more sense to switch to postgres when mysql databases get large. ------------------------------------------------------------------------------ Centralized Desktop Delivery: Dell and VMware Reference Architecture Simplifying enterprise desktop deployment and management using Dell EqualLogic storage and VMware View: A highly scalable, end-to-end client virtualization framework. Read more! http://p.sf.net/sfu/dell-eql-dev2dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
