RT 4.0.7 to 4.4.3 upgrade leads to performance issues due ACL checks


#1

Hi,

I‘m trying to update an old request-tracker 4.0.7 instance to 4.4.3. The upgrade works even with 82’319 user ( inclusive privileged LDAP users; ExternalAuth ).

So far so good, but I realized that the performance for displaying queues is terribly slow by using a privileged user. The longer the displayed queue the longer the execution time. I’m talking about minutes… By using the root user or a LDAP-user with the administration permission “do everything or nothing” ( translated from German ) I got instantly the queues.

Furthermore I figured out that a mysql query leads to the long execution time. Since the root execution does not require an ACL check it runs faster. But I guess that’s not normal to consume so much time. It took hours to figure that out.

Basically the differences by queries (MySQL EXPLAIN SELECT …) are:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| SIMPLE| Groups_2| ref| groups1,groups2,groups3| groups1 | 67 | const | 693212 | Using where; Distinct
| SIMPLE | CachedGroupMembers_3 | ref | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem | 12 | rt3.Groups_2.id,const,const | 1 | Using where; Using index; Distinct

I guess 693212 rows in the “Groups” table are pretty much. The old (still running) RT 4.0.3 instance with the same not updated instance runs like a charm.

Did I made a mistake?

By the way I got this warning during the database upgrade:
Processing 4.3.0
Now inserting data.
[6564] [Wed Aug 29 22:44:51 2018] [warning]: You have 11141 users(s) with a non-empty value for column ‘ExternalAuthId’. Core RT does not use this column, so perhaps an extension or local modification makes use of it. Please migrate these user values to a custom field or an attribute because this upgrade will drop these columns. at ./etc/upgrade/4.3.0/content line 67. (./etc/upgrade/4.3.0/content:67)
[6564] [Wed Aug 29 22:44:51 2018] [warning]: You have 256 users(s) with a non-empty value for column ‘AuthSystem’. Core RT does not use this column, so perhaps an extension or local modification makes use of it. Please migrate these user values to a custom field or an attribute because this upgrade will drop these columns. at ./etc/upgrade/4.3.0/content line 67. (./etc/upgrade/4.3.0/content:67)

I did not change anything in order to solve this problem but since the LDAP user are still able to login I guess that’s not the problem.

root:

SELECT main.* FROM Tickets main JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) WHERE (main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (main.Queue = ‘72’ AND ( ( Queues_1.Lifecycle = ‘default’ AND ( main.Status = ‘new’ OR main.Status = ‘open’ OR main.Status = ‘stalled’ ) ) OR ( Queues_1.Lifecycle = ‘assets’ AND ( main.Status = ‘new’ OR main.Status = ‘allocated’ OR main.Status = ‘in-use’ ) ) OR ( Queues_1.Lifecycle = ‘approvals’ AND ( main.Status = ‘new’ OR main.Status = ‘open’ OR main.Status = ‘stalled’ ) ) ) ) ORDER BY main.id ASC LIMIT 50;
11 rows in set (0.00 sec)

normal user:

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 = ‘1296794’ ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( main.Queue IN (‘44’, ‘59’, ‘1’, ‘4’, ‘5’, ‘6’, ‘7’, ‘8’, ‘9’, ‘10’, ‘11’, ‘12’, ‘13’, ‘14’, ‘15’, ‘16’, ‘19’, ‘20’, ‘21’, ‘22’, ‘23’, ‘24’, ‘25’, ‘26’, ‘27’, ‘29’, ‘30’, ‘31’, ‘32’, ‘33’, ‘34’, ‘35’, ‘36’, ‘37’, ‘38’, ‘39’, ‘40’, ‘41’, ‘42’, ‘43’, ‘44’, ‘45’, ‘46’, ‘47’, ‘48’, ‘49’, ‘50’, ‘51’, ‘52’, ‘53’, ‘54’, ‘55’, ‘57’, ‘58’, ‘59’, ‘60’, ‘62’, ‘63’, ‘64’, ‘65’, ‘66’, ‘68’, ‘69’, ‘72’, ‘78’, ‘79’, ‘73’, ‘80’, ‘82’, ‘83’, ‘85’, ‘88’, ‘90’, ‘92’, ‘93’, ‘94’, ‘97’, ‘99’, ‘28’, ‘102’, ‘103’, ‘106’, ‘108’, ‘109’) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = ‘Requestor’ ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = ‘Cc’ AND main.Queue IN (‘77’) ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = ‘AdminCc’ ) OR ( main.Owner = ‘1296794’ ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (main.Queue = ‘72’ AND ( ( Queues_1.Lifecycle = ‘assets’ AND ( main.Status = ‘new’ OR main.Status = ‘allocated’ OR main.Status = ‘in-use’ ) ) OR ( Queues_1.Lifecycle = ‘default’ AND ( main.Status = ‘new’ OR main.Status = ‘open’ OR main.Status = ‘stalled’ ) ) OR ( Queues_1.Lifecycle = ‘approvals’ AND ( main.Status = ‘new’ OR main.Status = ‘open’ OR main.Status = ‘stalled’ ) ) ) ) ORDER BY main.id ASC LIMIT 50;
11 rows in set (37.49 sec)

Does somebody has any ideas?


#2

Thats… a lot of privileged users! Did you go through the UPGRADING-X.Y documents that apply to you? Note the new-to-you ‘"$UseSQLForACLChecks" in RT_Config’ flag:

https://docs.bestpractical.com/rt/latest/UPGRADING-4.2.html#UPGRADING-FROM-RT-4.0.0-and-greater

I guess the first step is to turn that off to see if you get your performance back. Maybe its okay to run with this off, as its how it has been working for you?

Theres also some table shrinking utilities in etc/upgrade, but looking here those look like they’re for upgrading from pre-4.0.

Anyways, I guess what I would do is a quick test with $UseSQLForACLChecks off, and then if I decided I needed that on, I’d think about restructuring my system (do you REALLY have 80,000+ people logging in to the RT UI? If you do, okay, but my knee-jerk reaction is that a lot of those people don’t need to be privileged).


#3

Hi Todd_Wade1, thank you very much help.

Indeed setting $UserSQLForACLChecks to 0 helps to return the original performance. But unfortunatley this is not applicable for our use case, because not all privileged users are equivalent

We have

  • some hundreds privileged user,

  • and a huge amount of unprivilieged users ( customers ).

> SELECT COUNT(id) AS privileged FROM CachedGroupMembers WHERE GroupId = 4;
625

> SELECT COUNT(MemberId) AS unprivileged FROM CachedGroupMembers WHERE GroupId = 5;

112472

I know these MySQL statements are not precise but they give a small approximation about the numbers. Furthermore almost all privileged users will be authenticated against LDAP.

I’ll try it out but I think that’s okay. Thank you very much!