[rt-users] Performance problems on upgrade from 3.0 to 3.4 or 3.6.1

[Theo Kramer]

Hi

So I thought I would spend the silly season upgrading our system from
RT3.0 to R3.4 after having resolved a previous performance problem but
no luck.

System Details as follows

O/S: Ubuntu Dapper
Database: MySQL 5.0.22
Architecture: intel Pentium 3GHz
RAM: 2GByte
uname -a: Linux rtfm 2.6.15-27-server #1 SMP Sat Sep 16 02:57:21 UTC
          2006 i686 GNU/Linux
RT Version: 3.4.4-1 and 3.6.1-3

Problem Details as follows

The time taken to open/comment/reply to a ticket is way to high to be
useful - ie greater than 30 seconds. I did a bit of digging and looking
at mysql-slow.log discovered the following query

SELECT DISTINCT main.* FROM Users main , Principals Principals_1,  
  ACL ACL_2, CachedGroupMembers CachedGroupMembers_3
WHERE
  ((ACL_2.PrincipalId = CachedGroupMembers_3.GroupId)) AND
  ((ACL_2.PrincipalType = 'Group')) AND
  ((ACL_2.RightName = 'OwnTicket')) AND
  ((CachedGroupMembers_3.MemberId = Principals_1.id)) AND
  ((Principals_1.Disabled = '0')) AND
  ((Principals_1.PrincipalType = 'User')) AND
  ((Principals_1.id != '1')) AND
  ((main.id = Principals_1.id)) AND
  ((ACL_2.ObjectType = 'RT::Ticket' AND ACL_2.ObjectId   = 391495) OR
    (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId   = 11) OR
    (ACL_2.ObjectType = 'RT::System'))
  ORDER BY main.Name ASC;

10 rows in set (36.72 sec)

Removing the ORDER BY clause returns the 10 rows in no time!!

Query cache reset between running the query with the order by and
without the order by clause. 

Explain on the rt generated query using the order by clause shows

  Using where; Using temporary; Using filesort on Users (aka main)

and nothing when removing the order by clause...

Indexes are as per mysql.schema.

We have no such problems on the same data set on our old crunchy 700 MHz
production machine running RT3.0.

Any suggestions much appreciated.

-- 
Regards
Theo