[rt-devel] Re: [rt-users] RT 3.0.3 - Sudden MySQL slowness

[Harald Wagener]

On Wed, Jun 25, 2003 at 05:08:21PM -0400, Jesse Vincent wrote:
> 
> 
> On Wed, Jun 25, 2003 at 05:40:42PM +0200, Harald Wagener wrote:
> > So, further investigation shows that the MySQL queries are slowed down
> > to death. Setting the idle-timeout for the FastCgiServer to 120 seconds,
> > the internal server errors don't pop up anymore. The MySQL queries take
> > about 80 seconds to complete in the first run, and are stunningly fast
> > being served out of the query cache.
>  
> 
> Can you tell us _which_ mysql queries are running slow?

The one below. Setting tmp_table_size to 320M (50% of physical RAM) does
not help  either. Some more investigation showed that CachedGroupMembers
has around 40000 lines - is that feasible with ~200 requestors, 8 admins
and 4700 tickets?

Regards,
     Harald Wagener

SELECT DISTINCT main.*
  FROM Users main, Principals Principals_1, CachedGroupMembers
     CachedGroupMembers_2, Groups Groups_3, Principals Principals_4, ACL
ACL_5, 
     CachedGroupMembers CachedGroupMembers_6
  WHERE ((ACL_5.RightName = 'OwnTicket')OR(ACL_5.RightName =
'SuperUser')) AND
     ((CachedGroupMembers_2.GroupId = '4')) AND ((Principals_1.Disabled
= '0'))
     AND ((Principals_1.PrincipalType = 'User')) AND ((ACL_5.PrincipalId
=
     Principals_4.id AND Principals_4.id = Groups_3.id AND
ACL_5.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.Instance = 5) OR (Groups_3.Domain =
     'RT::Ticket-Role' AND Groups_3.Instance = 4684)) AND Groups_3.Type
=
     ACL_5.PrincipalType AND Groups_3.id = Principals_4.id)) AND
     (ACL_5.ObjectType = 'RT::System' OR (ACL_5.ObjectType = 'RT::Queue'
AND
     ACL_5.ObjectId = 5)) AND main.id = Principals_1.id AND
Principals_1.id =
     CachedGroupMembers_2.MemberId AND CachedGroupMembers_6.MemberId =
     Principals_1.id AND CachedGroupMembers_6.GroupId = Principals_4.id
  ORDER BY main.Name ASC