[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