Performance problems on upgrade from 3.0 to 3.4 or 3.6.1

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

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

For kicks, cna you try mysql 4.1? I’ve had horrible problems with the
braindead query optimizer on mysql 5.

> > System Details as follows > > > > O/S: Ubuntu Dapper > > Database: MySQL 5.0.22 > > For kicks, cna you try mysql 4.1? I've had horrible problems with the > braindead query optimizer on mysql 5. > >

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)

Thanks Jesse - downgraded to mysql 4.1 and on exactly the same data and
exactly the same indexes get the following.

10 rows in set (0.32 sec)

which makes it viable.

So my conclusion is that mysql 5.0.22 is not viable for request-tracker
in our case.

Regards
Theo