On 12/18/2013 11:43 AM, Kevin Falcone wrote:
On Tue, Dec 17, 2013 at 02:53:53PM -0500, Adam Hobaugh wrote:
I just updated from 4.0.10 to 4.2.1 and it seems to be working well
except when I am in a ticket, any page that shows a user drop down,
ie display and people load incredible slowly. I enabled slow query
logging and below are the two queries that appear to be causing the
problem. I appreciate any help in resolving this. 10 second load
times is rough. Please let me know what further information is
needed.
The slow query logs are most helpful when you then run EXPLAIN on the
query so you know why the database is having trouble.
Here is the EXPLAIN for the two queries.
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN
CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE
((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR
(ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue' AND
ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND
(ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND
(ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR
ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0')
AND (CachedGroupMembers_2.GroupId = '4') AND
(CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0')
AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1')
ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type |
possible_keys | key
| key_len | ref | rows |
Extra |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | CachedGroupMembers_2 | range |
DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem
| 10 | NULL | 264 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | eq_ref |
PRIMARY | PRIMARY
| 4 | rt4_2_1.CachedGroupMembers_2.MemberId | 1
| |
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY | PRIMARY
| 4 | rt4_2_1.main.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | CachedGroupMembers_4 | ref |
DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3
| 5 | rt4_2_1.CachedGroupMembers_2.MemberId | 1 | Using where;
Distinct |
| 1 | SIMPLE | ACL_3 | range |
ACL1 | ACL1
| 85 | NULL | 13 | Using where;
Using index; Distinct |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN
CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE
((ACL_3.ObjectType = 'RT::Ticket' AND ACL_3.ObjectId = 35402) OR
(ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR
(ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Ticket'
AND ACL_3.ObjectId = 35402) OR (ACL_3.ObjectType = 'RT::Queue' AND
ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND
(ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND
(ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR
ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0')
AND (CachedGroupMembers_2.GroupId = '4') AND
(CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0')
AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1')
ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type |
possible_keys | key
| key_len | ref | rows |
Extra |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | CachedGroupMembers_2 | range |
DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem
| 10 | NULL | 264 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | eq_ref |
PRIMARY | PRIMARY
| 4 | rt4_2_1.CachedGroupMembers_2.MemberId | 1
| |
| 1 | SIMPLE | Principals_1 | eq_ref |
PRIMARY | PRIMARY
| 4 | rt4_2_1.main.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | CachedGroupMembers_4 | ref |
DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3
| 5 | rt4_2_1.CachedGroupMembers_2.MemberId | 1 | Using where;
Distinct |
| 1 | SIMPLE | ACL_3 | range |
ACL1 | ACL1
| 85 | NULL | 15 | Using where;
Using index; Distinct |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
5 rows in set (0.00 sec)
Since your list is short (5 rows sent) rather than 500, you're
unlikely to have the common error someone else was pointing you at
where you grant OwnTicket too widely. RT 4.2 also has a workaround
for this case.
Do you grant your OwnTicket rights out to groups per queue, roles per
queue. Do you assign lots of Ticket level use groups (adminccs/ccs,
etc) which then pick up OwnTicket?
We have our users who can own tickets in groups and then assign those
groups to the queue and give them OwnTicket. We have at max 3 groups per
queue. We don't assign permissions on any role. We also have some
individuals added but those are there so that managers only watch the
tickets but can't own them.
Thanks!
//adam
