RT becomes unusably slow without Global ShowTicket privilege

RT Users
1

Hi,

I’ve been troubleshooting why RT is so unusably slow for some of our users (for example taking 5 - 10 minutes to load the dashboard, or the queue that they are allocated).

What I’ve discovered is that assigning the global permission:

Global Group Everyone → General → ShowTicket
Or
Global / Priv → ShowTicket
Or
Global → ShowTicket

fixes the speed - it loads in a fraction of a second.

But… when you do this - it then allows users in that group to see all unassigned tickets. I.e. tickets that are not in queues that they have access to. For example

User1 is a member of Group1
Group1 has permission to access Queue1 (create / reply / seequeue / showticket)
User1 is not a member of Group2, which has corresponding perms on Queue2

So two questions:

  1. Why does assigning global “showticket” allow a user to see unassigned tickets that are not in their assigned queue? And they have no permissions via user / group to that queue?
  2. Why does removing the global “showticket” permission result in such abysmal performance?

Thanks,

Chris

1 Like
2

which version of RT?

3

Running 5.0.2, can’t remember if mysql or Maria dB sorry can check later

4

Hi Chris,

Assigning global “ShowTicket” means literally that. Anyone with that right can see each and every ticket in the system. That being the case, there is no need to perform an ACL check resulting in rapid results. Removing that right brings in the ACL check machinery resulting in your slowdown. Does your DB have any slow query logging so you can see what is going on?

Regards,
Ken

5

Hi Ken, sorry for the delay. Yes slow logging is enabled, but… it’s not picking these up. Not sure what’s going on there, But I’ve grabbed the queries from show full processlist:

SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE ( ( main.Queue IN ('3', '111') OR  ( main.Owner = '1567059' AND main.Queue IN ('2') )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Requestor' )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'AdminCc' AND main.Queue IN ('2') )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '10' AND main.Status IN ('new', 'open'))

explain SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_2  ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3  ON ( CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id )  WHERE ( ( main.Queue IN ('3', '111') OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'AdminCc' AND main.Queue IN ('2') )  OR  ( main.Owner = '1567059' AND main.Queue IN ('2') )  OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'Requestor' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Queue = '111' AND  (  ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') )  OR  ( Queues_1.Lifecycle = 'default' AND main.Status IN ('crp', 'new', 'open', 'review', 'stalled') )  ) )

SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2  ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3  ON ( CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id )  WHERE ( ( main.Queue IN ('3', '111') OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'AdminCc' AND main.Queue IN ('2') )  OR  ( main.Owner = '1567059' AND main.Queue IN ('2') )  OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'Requestor' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Queue = '111' AND  (  ( Queues_1.Lifecycle = 'default' AND main.Status IN ('crp', 'new', 'open', 'review', 'stalled') )  OR  ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') )  ) )  ORDER BY main.id ASC  LIMIT 50

SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE ( ( main.Queue IN ('3', '111') OR  ( main.Owner = '1567059' AND main.Queue IN ('2') )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Requestor' )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'AdminCc' AND main.Queue IN ('2') )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '10' AND main.Status IN ('new', 'open'));

These result in 2x query execution plans:
First:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	main	range	Tickets1,Tickets2,Status,Type	Tickets1	263	NULL	248	Using index condition; Using where
1	SIMPLE	Queues_1	eq_ref	PRIMARY	PRIMARY	4	rtfet.main.Queue	1	Using where
1	SIMPLE	Groups_2	ref	groups1,groups2,groups3,Domain	groups1	259	const	609467	Using where
1	SIMPLE	CachedGroupMembers_3	ref	DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1	DisGrouMem	5	rtfet.Groups_2.id	1	Using where; Using index

second:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	main	range	Tickets1,Tickets2,Status,Type	Type	67	NULL	145945	Using where; Using temporary
1	SIMPLE	Queues_1	eq_ref	PRIMARY	PRIMARY	4	rtfet.main.Queue	1	Using where; Distinct
1	SIMPLE	Groups_2	ref	groups1,groups2,groups3,Domain	groups1	259	const	609467	Using where; Distinct
1	SIMPLE	CachedGroupMembers_3	ref	DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1	DisGrouMem	5	rtfet.Groups_2.id	1	Using where; Using index; Distinct

One problem I can see is the queries being generated are structured inefficiently. You can rewrite the query to parse the same criteria… the simplest fix is changing the “LEFT JOIN Groups Groups_2 …” to “JOIN Groups Groups_2 …” . It’s way faster… backed up by the explain:

---------------------+--------+--------------------------------------------------------+
| id   | select_type | table                | type   | possible_keys                                                    | key        | key_len | ref                     | rows   | Extra                                                  |
+------+-------------+----------------------+--------+------------------------------------------------------------------+------------+---------+-------------------------+--------+--------------------------------------------------------+
|    1 | SIMPLE      | Groups_2             | ref    | groups1,groups2,groups3,Name,Domain                              | groups1    | 259     | const                   | 607330 | Using index condition; Using temporary; Using filesort |
|    1 | SIMPLE      | main                 | eq_ref | PRIMARY,Tickets1,Tickets2,Status,Type                            | PRIMARY    | 4       | rtfet.Groups_2.Instance |      1 | Using where                                            |
|    1 | SIMPLE      | Queues_1             | eq_ref | PRIMARY                                                          | PRIMARY    | 4       | rtfet.main.Queue        |      1 | Using where; Distinct                                  |
|    1 | SIMPLE      | CachedGroupMembers_3 | ref    | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1 | DisGrouMem | 5       | rtfet.Groups_2.id       |      1 | Using where; Using index; Distinct                     |
+------+-------------+----------------------+--------+------------------------------------------------------------------+------------+---------+-------------------------+--------+--------------------------------------------------------+

and you get the same benefits on the other generated queries. Same result set, a tiny fraction of the execution time…

Any idea why we’re using a LEFT JOIN there instead of INNER?

I don’t think tweaking mysql cache variables is going to help here, because the resulting record sets from multiple left joins is going to be too big.

Thanks,

Chris

6

Hi, any clue bats for me?

7

Nudge…? I would be very surprised if we were the only people experiencing this issue. The way the queries is structured looks wrong - they’re incurring am unnecessary performance cost. Or is this the wrong place to lodge this?

8

Hi,

I have a similar slowness issue. No idea if this is related, but you can check my detailed explanation of my issue in my post below :

After all, what I did to work around this issue was to optimize the queries for all users dashboards, and use only pre-approved queries. All users are denied creation of their own saved searches.

Maybe my post help you somehow.

9

Thanks @ALone I think (but not 100% certain) that it’s a slightly different scenario. We’re using default queries for types of users as far I’m aware… but the issue is that if you look at the actual SQL being generated - it’s creating huge joins that take memory / CPU to process, when it doesn’t need to. In your case it was memory chewed up by Apache - in our case it’s memory being used by the database engine to try and process the joins.

Hmm… OK I thought I had looked at this previously… and I have:
SQL sorting issues with requestor in 5.0.2

But the “solution” I found at the time is what @knation identified as incorrect user configuration.

The behaviour of the component that generates the resulting DB SQL query actually changed from 4.x → 5.x. The exact same operation in 4.x generates different SQL vs 5.x.

Others have previously talked about similar issues in earlier versions Extremely slow SQL queries after RT3 to RT4 upgrade

So getting back to it - is this the right forum to ask this question? If not - please let me know where I should be posting the question & detail.

10

Hi… if this isn’t the right place to ask - where should I be asking this question?

11

I think this is probably the right place to ask, but the lack of response means none of us have an answer I’m afraid.

12

Thanks @GreenJimll i might try the dev list. There’s a change in behaviour across version. Either the change in the generated sql is intentional BUT creating a performance issue OR is a bug and it should be still generating the same query as previously.

To clarify - using the sql syntax generated by earlier versions of RT returns the correct recordset in a fraction of the time, against the same data. New version generates slightly different sql as I’ve highlighted (the outer join), which leads to poor query execution.