[rt-users] any page showing a user drop down in a ticket is loading slow

[Kevin Falcone]

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.

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?

-kevin

> 
> # Time: 131217 14:42:38
> # User at Host: rt_user[rt_user] @ ticket.cs.pitt.edu [136.142.23.83]
> # Query_time: 9  Lock_time: 0  Rows_sent: 5  Rows_examined: 175403
> 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;
> # Time: 131217 14:42:48
> # User at Host: rt_user[rt_user] @ ticket.cs.pitt.edu [136.142.23.83]
> # Query_time: 8  Lock_time: 0  Rows_sent: 5  Rows_examined: 175403
> 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;
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 235 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131218/42c6f45c/attachment.sig>