[rt-users] crazy sql during "reply" action

[Jesse Vincent]

You may want to follow the ongoing discussion of this issue on rt-devel.
It's a known problem and it's being worked on.

	-j


On Fri, Apr 11, 2003 at 04:31:33PM +1000, Jamie Wilkinson wrote:
> Hi all,
> 
> I'm running RT 3.0.0 with Postgres 7.3.2.  It's running at an average speed,
> but when I click on the Reply link to eply to a ticket, the database jumps
> up to 100% CPU usage for about 15 minutes, before it finally returns and I
> am presented wi th the Update ticket page for sending correspondance.
> 
> A bit of investigation showed that the database is choking on this SQL
> query:
> 
> SELECT DISTINCT main.* 
> FROM Users main, Principals Principals_1, Groups Groups_2,
>      Principals Principals_3, Principals Principals_4, ACL ACL_5,
>      CachedGroupMembers CachedGroupMembers_6,
>      CachedGroupMembers CachedGroupMembers_7
> WHERE ((ACL_5.RightName = 'SuperUser')OR(ACL_5.RightName = 'OwnTicket')) AND
>       ((CachedGroupMembers_7.GroupId = '4')) AND
>       ((Principals_1.Disabled = '0')) AND
>       ((Principals_1.PrincipalType = 'User')) AND
>       ((Principals_3.PrincipalType = 'User')) AND
>       (ACL_5.ObjectType = 'RT::System'  OR 
>           (ACL_5.ObjectType = 'RT::Queue'  AND ACL_5.ObjectId = '4')) AND
>       ((ACL_5.PrincipalId = Principals_4.Id AND
>         Principals_4.id = Groups_2.Id AND
>         ACL_5.PrincipalType = 'Group' AND
>         (Groups_2.Domain = 'SystemInternal' OR
>          Groups_2.Domain = 'UserDefined' OR
>          Groups_2.Domain = 'ACLEquivalence')) OR
>        (((Groups_2.Domain = 'RT::Queue-Role' AND
>           Groups_2.Instance = '4') OR
>          (Groups_2.Domain = 'RT::Ticket-Role' AND
>           Groups_2.Instance = '376')) AND
>         Groups_2.Type = ACL_5.PrincipalType AND
>         Groups_2.Id = Principals_4.id AND
>         Principals_4.PrincipalType = 'Group')) AND
>       main.id = Principals_1.id AND
>       Principals_1.id = CachedGroupMembers_7.MemberId AND
>       main.id = Principals_3.id AND
>       CachedGroupMembers_6.MemberId = Principals_3.Id AND
>       CachedGroupMembers_6.GroupId = Principals_4.Id
> ORDER BY main.Name ASC
> 
> Now, my first question is: What is this query trying to do?  I ran it
> manually, it looks like it's trying to fill out the Owner dropdown box, i.e.
> finding all the users who could possibly own this ticket.
> 
> My second question is: did you write this query by hand? :-)  It's quite
> insane.
> 
> Finally, what can I do to speed this up?  15 minutes is incredibly
> undesirable to wait to reply to a ticket, we may as well not bother using
> RT.
> 
> I've already checked postgres's configuration for tuning, and checked the
> indexes relating to this query and they seem okay.  I'm not sure that this
> is a problem with the database, though, only this query.
> 
> -- 
> jaq at spacepants.org                           http://spacepants.org/jaq.gpg
> _______________________________________________
> rt-users mailing list
> rt-users at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-users
> 
> Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.