[rt-users] mysql slowness

[Kroll, Nathan Allen]

I found a little more out about this problem we seem to be having. The
slowest part seems to be when loading the query builder page and this
mysql query is run:

SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4  WHERE
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND  ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND (
Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = 'UserDefined' OR
Groups_3.Domain = 'ACLEquivalence')) OR ( ( (Groups_3.Domain =
'RT::Queue-Role' )  )  AND Groups_3.Type = ACL_2.PrincipalType) )  AND
(ACL_2.ObjectType = 'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue') )
ORDER BY main.Name ASC;

The query has taken up to 59 seconds and is the most frequent slow query
logged to mysql.  The query returns about 2100 results.  Is this normal
for people or is something fubar-ed on our setup?

Also, the CachedGroupMembers table has about 170000 rows in it.  Is this
normal for a rt setup with about 19000 tickets and less than 30 users?

Thanks,

Nate


-----Original Message-----

So upon upgrading to 3.4 (latest debian release), we started to notice
extreme slowness (in excess of 2 minutes) upon loading the query builder
or sometimes when trying to create a new ticket.  To try to troubleshoot
this, first I tried to move from apache1 and mod_perl to apache2 with
fastcgi.  There was some improvement, but still taking minutes to load
sometimes and fastcgi needed apache2 to be restarted daily otherwise it
hung.  When watching the processes via top while loading query builder
or
doing any of the other "slow" tasks, the mysqld process would sometimes
use upwards of 80-90 percent of the CPU.  Due to the different
combinations of apache, apache2, and modperl, fastcgi, and speedycgi,
the
observation from top leads me to believe that the slowness is not due to
the webserver or perl processor, but due to mysql.  We have close to
19,000 tickets and numerious users in our system and it's never been
super
fast, but with 3.4, it's becoming unbearable.



I'm wondering if anyone has any ideas to try and speed mysql or
everything
up in general.  Has anyone else had this problem with so many tickets?
Is
the solution to drop some of the historical tickets?



Any help is greatly appreciated.  Thanks!



Nate Kroll

Milwaukee School of Engineering


_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com