[rt-users] RT 3.6.4 poor query performance

Jesse Vincent jesse at bestpractical.com
Wed Mar 19 12:28:10 EDT 2008




On Wed, Mar 19, 2008 at 04:22:46PM +0000, Richard Ellis wrote:
> Hi Ruslan,
> 
> here's the two sets of results.

FWIW, from your response to ruslan, it _does_ look like your hand-added
"group1" index was messing up the query planner. It's on GroupId, while
we already had an index on GroupId, MemberId.





> 
> Thanks
> 
> Richard
> 
> 
> Ruslan Zakirov wrote:
> >Ok, I have an idea how to fix that problem
> >
> >Here is new file for testing that will give me more info to find the
> >best way to fixing this. We're really close.
> >
> >You can run it using:
> >mysql -t -u root -ppassword rt3 <../search_possible_owners.mysql.sql 
> >>test.res
> >
> >As a first step to fix it you can create the following index on Groups 
> >table:
> >CREATE INDEX RUZ_Groups1 ON Groups(Domain, Type, id);
> >
> >Please, run commands from the attachment twice before indexing and after.
> >
> >Thank you for the feedback.
> >
> >On Wed, Mar 19, 2008 at 11:49 AM, Richard Ellis <Richard.Ellis at sun.com> 
> >wrote:
> >  
> >> Hi Ruslan,
> >>
> >> Really appreciate the help on this. I'd love to find out why we are 
> >> seeing
> >>such odd results:
> >>
> >> 298 ticket owners when their are only 88 active users
> >> 1.5 million rows of data when we only have 9983 ticks as of this morning.
> >>
> >> Really odd
> >>
> >> Thanks
> >>
> >> Richard
> >>
> >>
> >>    
> >
> >[snip]
> >
> >
> >  

> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> | id | select_type | table                | type   | possible_keys                     | key     | key_len | ref                              | rows | Extra                                        |
> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> |  1 | SIMPLE      | main                 | range  | PRIMARY,Users3                    | PRIMARY | 4       | NULL                             | 1317 | Using where; Using temporary; Using filesort | 
> |  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                           | PRIMARY | 4       | rt3.main.id                      |    1 | Using where; Distinct                        | 
> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,GrouMem,group1,member1 | member1 | 5       | rt3.Principals_1.id              |    1 | Using where; Distinct                        | 
> |  1 | SIMPLE      | ACL_4                | range  | ACL1                              | ACL1    | 54      | NULL                             |  296 | Using where; Using index; Distinct           | 
> |  1 | SIMPLE      | Groups_3             | eq_ref | PRIMARY,Groups1,Groups2           | PRIMARY | 4       | rt3.CachedGroupMembers_2.GroupId |    1 | Using where; Distinct                        | 
> +----+-------------+----------------------+--------+-----------------------------------+---------+---------+----------------------------------+------+----------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Group         |       298 | 
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> |                  0 | 
> +--------------------+
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys   | key     | key_len | ref                     | rows | Extra                    |
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1            | ACL1    | 54      | NULL                    |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2 | Groups2 | 67      | rt3.ACL_4.PrincipalType | 1345 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys   | key     | key_len | ref                     | rows | Extra                    |
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1            | ACL1    | 54      | NULL                    |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2 | Groups2 | 67      | rt3.ACL_4.PrincipalType | 1345 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys   | key     | key_len | ref                     | rows | Extra                    |
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1            | ACL1    | 54      | NULL                    |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2 | Groups2 | 67      | rt3.ACL_4.PrincipalType | 1345 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------+---------+---------+-------------------------+------+--------------------------+
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | CachedGroupMembers |          0 | PRIMARY    |            1 | id          | A         |       99912 |     NULL | NULL   |      | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            1 | GroupId     | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            2 | MemberId    | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            3 | Disabled    | A         |       99912 |     NULL | NULL   |      | BTREE      |         | 
> | CachedGroupMembers |          1 | GrouMem    |            1 | GroupId     | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | GrouMem    |            2 | MemberId    | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | group1     |            1 | GroupId     | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | member1    |            1 | MemberId    | A         |       99912 |     NULL | NULL   | YES  | BTREE      |         | 
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> | id | select_type | table                | type   | possible_keys                       | key         | key_len | ref                               | rows | Extra                                                     |
> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> |  1 | SIMPLE      | ACL_4                | range  | ACL1                                | ACL1        | 54      | NULL                              |  296 | Using where; Using index; Using temporary; Using filesort | 
> |  1 | SIMPLE      | Groups_3             | ref    | PRIMARY,Groups1,Groups2,RUZ_Groups1 | RUZ_Groups1 | 134     | const,rt3.ACL_4.PrincipalType     |  365 | Using where; Using index                                  | 
> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,GrouMem,group1,member1   | DisGrouMem  | 5       | rt3.Groups_3.id                   |    1 | Using where; Using index                                  | 
> |  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                             | PRIMARY     | 4       | rt3.CachedGroupMembers_2.MemberId |    1 | Using where                                               | 
> |  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Users3                      | PRIMARY     | 4       | rt3.Principals_1.id               |    1 | Using where                                               | 
> +----+-------------+----------------------+--------+-------------------------------------+-------------+---------+-----------------------------------+------+-----------------------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Group         |       298 | 
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> |                  0 | 
> +--------------------+
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys               | key         | key_len | ref                           | rows | Extra                    |
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1                        | ACL1        | 54      | NULL                          |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2,RUZ_Groups1 | RUZ_Groups1 | 134     | const,rt3.ACL_4.PrincipalType |  365 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys               | key         | key_len | ref                           | rows | Extra                    |
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1                        | ACL1        | 54      | NULL                          |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2,RUZ_Groups1 | RUZ_Groups1 | 134     | const,rt3.ACL_4.PrincipalType |  365 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4 | range | ACL1          | ACL1 | 54      | NULL |  296 | Using where; Using index | 
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> | id | select_type | table    | type  | possible_keys               | key         | key_len | ref                           | rows | Extra                    |
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> |  1 | SIMPLE      | ACL_4    | range | ACL1                        | ACL1        | 54      | NULL                          |  296 | Using where; Using index | 
> |  1 | SIMPLE      | Groups_3 | ref   | Groups1,Groups2,RUZ_Groups1 | RUZ_Groups1 | 134     | const,rt3.ACL_4.PrincipalType |  365 | Using where; Using index | 
> +----+-------------+----------+-------+-----------------------------+-------------+---------+-------------------------------+------+--------------------------+
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | CachedGroupMembers |          0 | PRIMARY    |            1 | id          | A         |       97966 |     NULL | NULL   |      | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            1 | GroupId     | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            2 | MemberId    | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | DisGrouMem |            3 | Disabled    | A         |       97966 |     NULL | NULL   |      | BTREE      |         | 
> | CachedGroupMembers |          1 | GrouMem    |            1 | GroupId     | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | GrouMem    |            2 | MemberId    | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | group1     |            1 | GroupId     | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> | CachedGroupMembers |          1 | member1    |            1 | MemberId    | A         |       97966 |     NULL | NULL   | YES  | BTREE      |         | 
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com

-- 



More information about the rt-users mailing list