[rt-users] mysql slowness

[Kenneth Marshall]

Nate,

We also see this problem with a PostgreSQL DB on the same query.
This is not surprising considering what the query is doing. I thought
that there was some work being done to either provide some sort of
cache or materialized view to reduce the average cost of this query.

Ken

On Wed, Jul 13, 2005 at 10:57:07AM -0500, Kroll, Nathan Allen wrote:
> 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
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com