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

[Nicolae P. Costescu]

15 minutes - check if you are running out of disk space while this query is 
running. When postgres has used up its sort ram it will writ 
pg_sorttempXXXX files to the database directory and do sorts on disk. If 
you have a bad query, this will go on until you run out of disk space, at 
which time the postgres backend you were talking to will die and clean up 
all its temp sort files (so you dont' see the evidence). You have to do a 
disk usage while it's running and see if that's the cause.

>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

****************************************************
Nicolae P. Costescu, Ph.D.  / Senior Developer
Stronghold Technologies
46040 Center Oak Plaza, Suite 160 / Sterling, Va 20166
Tel: 571-434-1472 / Fax: 571-434-1478