[rt-users] RT 3.6.4 poor query performance
Richard Ellis
Richard.Ellis at Sun.COM
Wed Mar 19 12:38:12 EDT 2008
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 :)
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