[rt-users] RT 3.6.4 poor query performance

Jesse Vincent jesse at bestpractical.com
Wed Mar 19 12:39:50 EDT 2008




On Wed, Mar 19, 2008 at 04:38:12PM +0000, Richard Ellis wrote:
> Hi Jesse,
> 
> Thanks. To the best of my knowledge nobody has added any indexes to the 
> database on anything except what RT patches apply on each upgrade. This 
> DB was originally 3.0 and has been upgraded more times than I want to 
> think about over the years to 3.6.6 now :)

That index doesn't follow RT's standard index naming/capitalization scheme. Someone may have gone behind your back ;)

> 
> Richard
> 
> 
> Jesse Vincent wrote:
> >
> >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