[rt-devel] RT 3.0rc1 - "Ticket Create" slow load issue RESOLVED

Matthew J. Draper matthewd at staff.airnet.com.au
Tue Mar 18 02:32:46 EST 2003


Apologies... After discovering that Users_Overlay.pm hadn't changed (or
have I gone truly crazy? ;), I got a little over-zealous with my `grep
-v`s on the diff output. :)

RT is still running The Query From Hell when I try to comment/reply, and
is still taking ~100sec to run it.

My only other question: I'm using PostgreSQL at the moment, as I
mentioned earlier. There doesn't appear to be a CachedGroupMembers4
(although there is a 3)... Should I drop 3? Is 4 called something else
in Postgres, or are the indicies set up completely differently in the
two database environments?

In reply to your earlier question: No, this has not improved my
performance... This may be because I still have some
CachedGroupMembers4-equivalent index lying around somewhere, or because
I'm using PostgreSQL, however. I've included some potentially useful
information about just what the query is doing (as well as a copy of the
query), below... I have absolutely no idea how much use it actually is.

Matthew

QUERY:
SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups
Groups_2, Principals Principals_3, ACL ACL_4, CachedGroupMembers
CachedGroupMembers_5   WHERE ((ACL_4.RightName =
'SuperUser')OR(ACL_4.RightName = 'OwnTicket')) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType =
'User')) AND (ACL_4.ObjectType = 'RT::System'  OR (ACL_4.ObjectType =
'RT::Queue'  AND ACL_4.ObjectId = '6') ) AND ( Principals_3.id =
Groups_2.id  AND (ACL_4.PrincipalId = Principals_3.Id  AND
ACL_4.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 = '6')
OR ( Groups_2.Domain = 'RT::Ticket-Role' AND Groups_2.Instance = '773')
)  AND Groups_2.Type = ACL_4.PrincipalType AND
Principals_3.PrincipalType = 'Group') )  AND main.id = Principals_1.id
AND CachedGroupMembers_5.MemberId = Principals_1.Id  AND
CachedGroupMembers_5.GroupId = Principals_3.Id  ORDER BY main.Name ASC;

STATISTICS:
! system usage stats:
!       99.005569 elapsed 93.590000 user 5.320000 system sec
!       [94.310000 user 5.430000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       624/502 [810/1535] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:        977 read,          0 written, buffer hit
rate = 96.12%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written

EXPLAIN:
 Unique  (cost=557.32..557.41 rows=1 width=898)
   ->  Sort  (cost=557.32..557.32 rows=1 width=898)
         Sort Key: main.name, main.id, main."password", main.comments,
main.signature, main.emailaddress, main.freeformcontactinfo,
main.organization, main.realname, main.nickname, main.lang,
main.emailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.pgpkey, main.creator,
main.created, main.lastupdatedby, main.lastupdated
         ->  Nested Loop  (cost=82.68..557.31 rows=1 width=898)
               Join Filter: ((("outer".principalid = "inner".id) OR
("outer"."domain" = 'RT::Ticket-Role'::character varying) OR
("outer"."domain" = 'RT::Queue-Role'::character varying)) AND
(("outer".principalid = "inner".id) OR ("outer".instance =
'773'::character varying) OR ("outer"."domain" =
'RT::Queue-Role'::character varying)) AND (("outer".principalid =
"inner".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying)
OR ("outer".instance = '6'::character varying)) AND
(("outer".principalid = "inner".id) OR ("outer".instance =
'773'::character varying) OR ("outer".instance = '6'::character
varying)) AND (("inner".id = "outer".id) OR ("outer"."type" =
"outer".principaltype)) AND (("outer".principalid = "inner".id) OR
("outer"."type" = "outer".principaltype)) AND (("inner".id = "outer".id)
OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR
("outer"."domain" = 'RT::Queue-Role'::character varying)) AND
(("inner".id = "outer".id) OR ("outer".instance = '773'::character
varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying))
AND (("inner".id = "outer".id) OR ("outer"."domain" =
'RT::Ticket-Role'::character varying) OR ("outer".instance =
'6'::character varying)) AND (("inner".id = "outer".id) OR
("outer".instance = '773'::character varying) OR ("outer".instance =
'6'::character varying)) AND (("inner".id = "outer".id) OR
("inner".principaltype = 'Group'::character varying)) AND
(("outer"."domain" = 'SystemInternal'::character varying) OR
("outer"."domain" = 'UserDefined'::character varying) OR
("outer"."domain" = 'ACLEquivalence'::character varying) OR
("inner".principaltype = 'Group'::character varying)) AND
(("outer".principalid = "inner".id) OR ("inner".principaltype =
'Group'::character varying)) AND (("outer".principaltype =
'Group'::character varying) OR ("inner".principaltype =
'Group'::character varying)))
               ->  Nested Loop  (cost=0.00..263.81 rows=1 width=50)
                     Join Filter: ((("outer".principaltype =
'Group'::character varying) OR ("inner"."domain" =
'RT::Ticket-Role'::character varying) OR ("inner"."domain" =
'RT::Queue-Role'::character varying)) AND (("outer".principaltype =
'Group'::character varying) OR ("inner".instance = '773'::character
varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying))
AND (("outer".principaltype = 'Group'::character varying) OR
("inner"."domain" = 'RT::Ticket-Role'::character varying) OR
("inner".instance = '6'::character varying)) AND (("outer".principaltype
= 'Group'::character varying) OR ("inner".instance = '773'::character
varying) OR ("inner".instance = '6'::character varying)) AND
(("outer".principaltype = 'Group'::character varying) OR ("inner"."type"
= "outer".principaltype)) AND (("inner"."domain" =
'SystemInternal'::character varying) OR ("inner"."domain" =
'UserDefined'::character varying) OR ("inner"."domain" =
'ACLEquivalence'::character varying) OR ("inner"."type" =
"outer".principaltype)))
                     ->  Seq Scan on acl acl_4  (cost=0.00..14.70 rows=1
width=13)
                           Filter: (((rightname = 'SuperUser'::character
varying) OR (rightname = 'OwnTicket'::character varying)) AND
((objecttype = 'RT::Queue'::character varying) OR (objecttype =
'RT::System'::character varying)) AND ((objectid = 6) OR (objecttype =
'RT::System'::character varying)))
                     ->  Seq Scan on groups groups_2  (cost=0.00..242.98
rows=14 width=37)
                           Filter: ((("domain" =
'SystemInternal'::character varying) OR ("domain" =
'UserDefined'::character varying) OR ("domain" =
'ACLEquivalence'::character varying) OR ("domain" =
'RT::Ticket-Role'::character varying) OR ("domain" =
'RT::Queue-Role'::character varying)) AND (("domain" =
'SystemInternal'::character varying) OR ("domain" =
'UserDefined'::character varying) OR ("domain" =
'ACLEquivalence'::character varying) OR (instance = '773'::character
varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND
(("domain" = 'SystemInternal'::character varying) OR ("domain" =
'UserDefined'::character varying) OR ("domain" =
'ACLEquivalence'::character varying) OR ("domain" =
'RT::Ticket-Role'::character varying) OR (instance = '6'::character
varying)) AND (("domain" = 'SystemInternal'::character varying) OR
("domain" = 'UserDefined'::character varying) OR ("domain" =
'ACLEquivalence'::character varying) OR (instance = '773'::character
varying) OR (instance = '6'::character varying)))
               ->  Materialize  (cost=291.42..291.42 rows=20 width=848)
                     ->  Nested Loop  (cost=82.68..291.42 rows=20
width=848)
                           ->  Nested Loop  (cost=82.68..231.81 rows=20
width=836)
                                 ->  Hash Join  (cost=82.68..91.01
rows=9 width=828)
                                       Hash Cond: ("outer".id =
"inner".id)
                                       ->  Seq Scan on users main
(cost=0.00..6.84 rows=184 width=824)
                                       ->  Hash  (cost=82.20..82.20
rows=188 width=4)
                                             ->  Seq Scan on principals
principals_1  (cost=0.00..82.20 rows=188 width=4)
                                                   Filter: ((disabled =
0::smallint) AND (principaltype = 'User'::character varying))
                                 ->  Index Scan using
cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_5
(cost=0.00..15.17 rows=4 width=8)
                                       Index Cond:
(cachedgroupmembers_5.memberid = "outer".id)
                           ->  Index Scan using principals_pkey on
principals principals_3  (cost=0.00..3.01 rows=1 width=12)
                                 Index Cond: ("outer".groupid =
principals_3.id)


> -----Original Message-----
> From: Jesse Vincent [mailto:jesse at bestpractical.com] 
> Sent: Tuesday, 18 March 2003 17:26
> To: Matthew J. Draper
> Cc: rt-devel at fsck.com
> Subject: Re: [rt-devel] RT 3.0rc1 - "Ticket Create" slow load 
> issue RESOLVED
> 
> 
> 
> So. What are you basing that on? It's only about 4 lines of diff, but
> they're 4 _important_ lines ;)
> 
> 
> On Tue, Mar 18, 2003 at 04:47:25PM +1030, Matthew J. Draper wrote:
> > Erm, Jesse, I could be wrong, but rc2 doesn't seem any 
> different to rc1.
> > 
> > Matthew
> > 
> > > -----Original Message-----
> > > From: Jesse Vincent [mailto:jesse at bestpractical.com] 
> > > Sent: Tuesday, 18 March 2003 16:16
> > > To: rt-devel at fsck.com
> > > Subject: Re: [rt-devel] RT 3.0rc1 - "Ticket Create" slow load 
> > > issue RESOLVED
> > > 
> > > 
> > > 3.0.0rc2 is now available.  I'd like to get at least one 
> > > success report
> > > from someone who had issues with rc1 before announcing it to 
> > > the masses.
> > > 
> > > If you're upgrading from an earlier release candidate, 
> you'll need to
> > > issue the following commands to mysql:
> > > 
> > > 
> > >  alter table CachedGroupMembers drop index CachedGroupMembers3;
> > >  alter table CachedGroupMembers drop index CachedGroupMembers4;
> > > 
> > > 
> > > 
> > > -- 
> > > http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.
> > > _______________________________________________
> > > rt-devel mailing list
> > > rt-devel at lists.fsck.com
> > > http://lists.fsck.com/mailman/listinfo/rt-devel
> > > 
> 
> -- 
> http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.
> 



More information about the Rt-devel mailing list