this sounds remarkably like Speed rt5 after upgrade from rt 4.4.3
@gvvg @Sean_Cwiek did you end up finding anything concrete?
Basic navigation is triggering queries with execution plans that are just way off the scale:
explain SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Name = 'Requestor' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.GroupId = Groups_1.id ) WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( ( CachedGroupMembers_2.MemberId IN ('1032992') ) or ( CachedGroupMembers_3.MemberId IN ('0') ) ) ORDER BY main.id ASC
the first two rows say it all:
1 SIMPLE main ref Status,Type Type 67 const 126626 Using index condition; Using where; Using temporary
1 SIMPLE Groups_1 ref groups1,groups2,groups3,Name,Domain Name 803 const 456660 Using where; Distinct
i.e. 57,825,029,160 rows to evaluate.
That’s a very different result set, comparing to the same operation from 4.x series. I’m not certain, but I don’t think it’s a MariaDB bug in how it’s indexing the tables - it’s actually executing a different query compared to the 4.x series. The 4.x query returns the same result set using a fraction of the memory / CPU / etc, compared with the query that 5.x is generating.