[rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8

Foggi, Nicola NFOGGI at depaul.edu
Fri Jun 25 12:33:48 EDT 2010


A little follow up on the explains:

here is the query as run when displaying a ticket:

mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc')  ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys                  | key        | key_len | ref                                 | rows | Extra                                        |
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | main                 | range  | PRIMARY                        | PRIMARY    | 4       | NULL                                |  316 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Groups_3             | ref    | PRIMARY,Groups1,Groups2        | Groups2    | 67      | const                               | 4000 | Using where; Distinct                        | 
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                        | PRIMARY    | 4       | rt3.main.id                         |    1 | Using where; Distinct                        | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10      | rt3.Groups_3.id,rt3.Principals_1.id |    1 | Using where; Using index; Distinct           | 
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)


here is the modified removing the OR 

mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3')) AND (Groups_3.Type = 'AdminCc')  ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
| id | select_type | table                | type   | possible_keys                  | key        | key_len | ref                         | rows | Extra                                                     |
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | Groups_3             | ref    | PRIMARY,Groups1,Groups2        | Groups1    | 139     | const,const,const           |    1 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | main                 | range  | PRIMARY                        | PRIMARY    | 4       | NULL                        |  316 | Using where                                               | 
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                        | PRIMARY    | 4       | rt3.main.id                 |    1 | Using where; Distinct                                     | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10      | rt3.Groups_3.id,rt3.main.id |    1 | Using where; Using index; Distinct                        | 
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

index listing from Principals:

mysql> show index from Principals;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Principals |          0 | PRIMARY     |            1 | id          | A         |       22309 |     NULL | NULL   |      | BTREE      | NULL    | 
| Principals |          1 | Principals2 |            1 | ObjectId    | A         |       22309 |     NULL | NULL   | YES  | BTREE      | NULL    | 
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

not sure if this helps tracking it down... still having figured out what the Groups_3.Domain = 'RT::System-Role'...

Nicola

-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com on behalf of Foggi, Nicola
Sent: Fri 6/25/2010 11:07 AM
To: Ruslan Zakirov
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
 

UseSQLForACLChecks is set as the default under RT_Config.pm to "Set($UseSQLForACLChecks, undef);"

here is the explain:

EXPLAIN SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc')  ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys                  | key        | key_len | ref                                 | rows | Extra                                        |
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | main                 | range  | PRIMARY                        | PRIMARY    | 4       | NULL                                |  316 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Groups_3             | ref    | PRIMARY,Groups1,Groups2        | Groups2    | 67      | const                               | 3992 | Using where; Distinct                        | 
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                        | PRIMARY    | 4       | rt3.main.id                         |    1 | Using where; Distinct                        | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10      | rt3.Groups_3.id,rt3.Principals_1.id |    1 | Using where; Using index; Distinct           | 
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

here is the indexes:

mysql> show index from Groups;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Groups |          0 | PRIMARY  |            1 | id          | A         |       21563 |     NULL | NULL   |      | BTREE      | NULL    | 
| Groups |          1 | Groups1  |            1 | Domain      | A         |          31 |     NULL | NULL   | YES  | BTREE      | NULL    | 
| Groups |          1 | Groups1  |            2 | Instance    | A         |       21563 |     NULL | NULL   | YES  | BTREE      | NULL    | 
| Groups |          1 | Groups1  |            3 | Type        | A         |       21563 |     NULL | NULL   | YES  | BTREE      | NULL    | 
| Groups |          1 | Groups1  |            4 | id          | A         |       21563 |     NULL | NULL   |      | BTREE      | NULL    | 
| Groups |          1 | Groups2  |            1 | Type        | A         |          10 |     NULL | NULL   | YES  | BTREE      | NULL    | 
| Groups |          1 | Groups2  |            2 | Instance    | A         |       21563 |     NULL | NULL   | YES  | BTREE      | NULL    | 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

I did find in some trial and error testing if I drop the " OR (Groups_3.Domain = 'RT::System-Role')" from the query it gives the same results in under 1 second vs 11-12 seconds, but bot sure what that OR is adding into the mix.  That query is what is performed when I display a ticket through the web interface.

Any help is appreciated!

Nicola

-----Original Message-----
From: ruslan.zakirov at gmail.com on behalf of Ruslan Zakirov
Sent: Fri 6/25/2010 11:05 AM
To: Foggi, Nicola
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
 
Hello,

Do you use SQLForACLChecks option?
Where is EXPLAIN for this query?
Show indexes from Groups table.

On Fri, Jun 25, 2010 at 8:04 AM, Foggi, Nicola <NFOGGI at depaul.edu> wrote:
>
> hey everyone,
>
> after upgrading from 3.8.6 to 3.8.8 we're getting a slow query on this
> query:
>
> use rt3;
> SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1  ON (
> Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON
> ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON
> ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled
> = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType =
> 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance =
> '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type =
> 'AdminCc')  ORDER BY main.Name ASC;
> # Time: 100624 22:44:20
> # User at Host: rt_user[rt_user] @ rt.internal [10.12.10.72]
> # Query_time: 13  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-dc95ab57bb8d19e23fa6fa70314e3c0e', 3600);
> # Time: 100624 22:49:28
>
> when loading any ticket page.  I've verified the cachedgroupmembers3 index
> is in place:
>
> show index from CachedGroupMembers;
> ...
> | CachedGroupMembers |          1 | CachedGroupMembers3 |            1 |
> MemberId          | A         |       36038 |     NULL | NULL   | YES  |
> BTREE      | NULL    |
> | CachedGroupMembers |          1 | CachedGroupMembers3 |            2 |
> ImmediateParentId | A         |       36038 |     NULL | NULL   | YES  |
> BTREE      | NULL    |
>
>
> but still extremely slow... any ideas?  it's pretty bad...
>
> Nicola
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100625/7f6bd9fa/attachment.htm>


More information about the rt-users mailing list