[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