[rt-users] RT 3.6.4 poor query performance
Ruslan Zakirov
ruz at bestpractical.com
Wed Mar 19 13:25:13 EDT 2008
Jeff, always Cc the list.
Version of your mysql server?
As far as I can see you suffer from mysql bug, output from your server
is equal in both cases what is really wrong and mysql must use new
index in those test queries I sent to the list.
There are several options:
1) Delete any indexes on CachedGroupMembers table which starts from
MemberId column, but that will slowdown other queries and may be
terribly, depends on proprotions of your DB.
2) Upgrade to mysql 5.0.45 or greater and create index I suggested in
this thread earlier.
3) I have another idea how we can improve that in the code, but that
needs more investigation with a lot of users' feedback and a lot of
mine and users' time.
As long as MySQL 4.x has ended its life time and 5.0.x is stable
version then I think it's fair enough to recommend recent versions
instead of continuose refactoring of the code to make all those broken
mysqls happy.
On Wed, Mar 19, 2008 at 6:22 PM, Jeff Voskamp <javoskam at uwaterloo.ca> wrote:
>
> 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
> >>
> Since we were also having problems here's our output.
> spw.out is before. spw.out2 is after.
>
> Jeff Voskamp
> University of Waterloo
>
> +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> | 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 4138 | Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | Groups_3 | ref | PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1a | 67 | const | 630 | Using where; Using index; Distinct |
> | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 | PRIMARY | 4 | rt3_inst.main.id | 1 | Using where; Distinct |
> | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 | MyCGM1 | 10 | rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; Distinct |
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index; Distinct |
> +----+-------------+----------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Cc | 1 |
> | Group | 372 |
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> | 72 |
> +--------------------+
> +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+----------+-------+-------------------------------------------+---------+---------+-------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | 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 | 371 | 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 | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | 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 | 371 | 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 | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a | Groups1 | 67 | const | 630 | 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 | 897772 | NULL | NULL | | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled | A | 897772 | NULL | NULL | | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId | A | 897772 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled | A | 897772 | NULL | NULL | | BTREE | NULL |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
> +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> | 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 4138 | Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | Groups_3 | ref | PRIMARY,groups_key,Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1a | 67 | const | 630 | Using where; Using index; Distinct |
> | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY,Principals4 | PRIMARY | 4 | rt3_inst.main.id | 1 | Using where; Distinct |
> | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,MyCGM1 | MyCGM1 | 10 | rt3_inst.main.id,rt3_inst.Groups_3.id | 1 | Using where; Using index; Distinct |
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index; Distinct |
> +----+-------------+----------------------+--------+--------------------------------------------------------------------------+----------+---------+---------------------------------------+------+----------------------------------------------+
> +---------------+-----------+
> | PrincipalType | COUNT(id) |
> +---------------+-----------+
> | Cc | 1 |
> | Group | 372 |
> +---------------+-----------+
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
> +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
> +--------------------+
> | COUNT(Groups_3.id) |
> +--------------------+
> | 72 |
> +--------------------+
> +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+----------+-------+-------------------------------------------------------+---------+---------+-------+------+--------------------------+
> | 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | 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 | 371 | 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 | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | 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 | 371 | 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 | 371 | Using where; Using index |
> | 1 | SIMPLE | Groups_3 | ref | Groups1,Groups2,Groups9,Groups2a,Groups1a,RUZ_Groups1 | Groups1 | 67 | const | 630 | 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 | 901467 | NULL | NULL | | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 1 | GroupId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 2 | MemberId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | DisGrouMem | 3 | Disabled | A | 901467 | NULL | NULL | | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 1 | MemberId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 2 | GroupId | A | 901467 | NULL | NULL | YES | BTREE | NULL |
> | CachedGroupMembers | 1 | MyCGM1 | 3 | Disabled | A | 901467 | NULL | NULL | | BTREE | NULL |
> +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
>
--
Best regards, Ruslan.
More information about the rt-users
mailing list