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

[Jesse Vincent]

On Thu, Jan 04, 2007 at 06:42:54PM +0200, Theo Kramer wrote:
> 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.


> 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
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 

--