I‘m trying to update an old request-tracker 4.0.3 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 privileged 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 the 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.
Does somebody has any ideas?
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)
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)
Ok, the answer is a "new" Request-Tracker feature: UseSQLForACLCheck.
Disabling this parameter return the whole performance.