[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