At least I hope so. could folks who have been having this issue verify
that issuing the following commands to mysql and using the attached copy
of Users_Overlay.pm resolves this issue?
alter table CachedGroupMembers drop index CachedGroupMembers4;
alter table CachedGroupMembers drop index CachedGroupMembers4;
Thanks,
jesse
Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.
Users_Overlay.pm (7.68 KB)
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;
Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.
Erm, Jesse, I could be wrong, but rc2 doesn’t seem any different to rc1.
Matthew
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@bestpractical.com]
Sent: Tuesday, 18 March 2003 16:16
To: rt-devel@fsck.com
Subject: Re: [rt-devel] RT 3.0rc1 - “Ticket Create” slow load
issue RESOLVED3.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;–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
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 -vs 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)
Apologies… After discovering that Users_Overlay.pm hadn’t changed (or
have I gone truly crazy? ;), I got a little over-zealous with mygrep -vs on the diff output.
Sorry. the change was actually in code which calls Users. (inside of
html)
RT is still running The Query From Hell when I try to comment/reply, and
is still taking ~100sec to run it.
Actually, the query is a good deal simpler (not multiply joining on
CachedGroupMembers.
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?
Drop both 2 and 3. Tell me how that does. (That change made it into the
repo post cutting rc2.
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Drop both 2 and 3. Tell me how that does. (That change made
it into the repo post cutting rc2.
Okay, I’ve done this, but it’s still taking just as long. Adding
separate indices on groups.domain and groups.instance seem to provide
some improvement to the cost (down to low 400s), according to EXPLAIN,
but only reduced the query runtime by about 3 seconds (the output below
is from prior to adding those indices).
DEBUG: StartTransactionCommand
LOG: 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 = ‘19’) ) 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 = ‘19’)
OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND Groups_2.Instance = ‘682’)
) 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
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
LOG: QUERY STATISTICS
! system usage stats:
! 102.463478 elapsed 95.370000 user 6.890000 system sec
! [102.140000 user 7.750000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 623/455 [824/2297] 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: 190 read, 1 written, buffer hit
rate = 98.98%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
Matthew
When the page does actually load, RT shows every user on the system
(including non-privileged) in the Owner dropdown. (It gives an
appropriate error if I actually attempt to change the owner to an
unprivileged user, however).
Matthew
Hi Jesse, Matthew,
Matthew J. Draper wrote:
Erm, Jesse, I could be wrong, but rc2 doesn’t seem any different to rc1.
same for me here, but only when I try to display “people” or “jumbo”. hth.
rt-2-1-84 works fine. And yes, I dropped those two indexes.
Ciao - Andrea
Still having the same problems here. I went through the tables and made
sure that my indexes matched the indexes in the schema; no change.
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 =
‘9’) ) 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 = ‘9’) OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND
Groups_2.Instance = ‘3222’) ) 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
I’ve upgraded from 2.1.x to rc1 to rc2. Did I miss an index change?
mysql> alter table CachedGroupMembers drop index CachedGroupMembers3;
ERROR 1091: Can’t DROP ‘CachedGroupMembers3’. Check that column/key exists----- Original Message -----
From: “Jesse Vincent” jesse@bestpractical.com
To: rt-devel@fsck.com
Sent: Monday, March 17, 2003 9:46 PM
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;–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
Ok. I’ve backed out all the changes that seem to kill performance on
the “Create Ticket” back to 0.85 levels.
Any victims willing to try out the tarball from the following url?
http://fsck.com/aegis/aegis.cgi/rt.3.0.C18.tar.gz?file@aetar+project@rt.3.0+change@18
Best,
Jesse
Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.
That fixes the problems. replying to a ticket is now very very speedy
(compared to the latest rc code)
That fixes the problems. replying to a ticket is now very very speedy
(compared to the latest rc code)
Thanks very much for the sanity check. One more patch and then you
should see RC3 tonight.
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Changing values in a ticket (e.g. Priority / Status), I get a
"Permission Denied" error, but the change succeeds. Some weird quirk on
my system, or an issue in the current version?
Matthew
Can’t replicate it here. Anyone else?On Wed, Mar 19, 2003 at 03:22:42PM +1030, Matthew J. Draper wrote:
Changing values in a ticket (e.g. Priority / Status), I get a
“Permission Denied” error, but the change succeeds. Some weird quirk on
my system, or an issue in the current version?Matthew
-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Wednesday, 19 March 2003 14:41
To: Joseph W. Breu
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] RT 3.0rc1 - ‘Ticket Create’ slow load
issue RESOLVEDOn Tue, Mar 18, 2003 at 10:07:59PM -0600, Joseph W. Breu wrote:
That fixes the problems. replying to a ticket is now very
very speedy
(compared to the latest rc code)Thanks very much for the sanity check. One more patch and then you
should see RC3 tonight.–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.