We’re long time users of RT and we love it. However, our existing installation (RT3.6.6 on FreeBSD) is getting rather old so we’re attempting an upgrade. We have around 300,000 tickets, 200 intermittent privileged users
The plan is to move to RT4.2.15 on a newUbuntu VM, by using the deb packages. We’ve got it installed and working OK and like the improvements from our older version. We’ve also imported and updated the old database successfully onto a dedicated postresql 9.5 host (lots of fast disk and memory).
Unfortunately the performance is not what I’d hoped for. We’re seeing home page load times of 4-6 seconds. I’ve been through the relevant tips at https://rt-wiki.bestpractical.com/wiki/PerformanceTuning and have seen some improvement but not much.
The worst performing page seems to be creating a new search in the query builder. I’ve tried to correlate it with logs on the database server and I think this query is the problem:
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’) OR (ACL_3.ObjectType = ‘RT::System’ AND ACL_3.ObjectId = 1)) 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
It is taking 137 seconds to complete. Rebuilding indexes on postgres doesn’t make any difference at all to this.
Is this sort of performance expected in the ‘newer’ versions of RT? Is there anything odd about the query that would be simple to fix? Is there perhaps a problem caused by the upgrade path?.
Thanks in advance for any help.