Hi all,
I have a largish RT installation (± 200K tickets) that I am in the
process of upgrading (3.0.11 → 3.4.4).
Having had a look at pervious posts to the list I see that other have
run into the same issue.
The query that is causing fastcgi to timeout :
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4
WHERE ((ACL_2.RightName = ‘OwnTicket’)) AND
((CachedGroupMembers_4.MemberId = Principals_1.id)) AND ((Groups_3.id
= CachedGroupMembers_4.GroupId)) AND ((Principals_1.Disabled = ‘0’)or
(Principals_1.Disabled = ‘0’)) AND ((Principals_1.id != ‘1’)) AND
((main.id = Principals_1.id)) AND ( ( ACL_2.PrincipalId =
Groups_3.id AND ACL_2.PrincipalType = ‘Group’ AND
( Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain =
‘UserDefined’ OR Groups_3.Domain = ‘ACLEquivalence’)) OR
( ( (Groups_3.Domain = ‘RT::Queue-Role’ ) ) AND Groups_3.Type =
ACL_2.PrincipalType) ) AND (ACL_2.ObjectType = ‘RT::System’ OR
(ACL_2.ObjectType = ‘RT::Queue’) ) ORDER BY main.Name ASC;
The cost of this query :
Unique (cost=499689.57…499690.27 rows=8 width=2692)
My mistake was setting the initial instance up with rights for
Everyone to Create Ticket and Reply To Ticket. Is it possible to
reverse this from a database perspective as I need to only see the
local support agents in the owner drop down (all the agents have
email address in the same domain). Any ideas appreciated.
I understand that the common wisdom is to upgrade to 3.4.5, I am
however not sure that it will deliver the result I require.
Thanks and regards,
Andre
Andre Sachs
IT Architect, Clue Technologies PTY (LTD)
email: andre@clue.co.za
Hi all,
I have a largish RT installation (± 200K tickets) that I am in the
process of upgrading (3.0.11 → 3.4.4).
Having had a look at pervious posts to the list I see that other have
run into the same issue.
Have you tried RT 3.4.5 yet?
(Please reply to the list and not to me personally)
Hi Jesse,
Have you tried RT 3.4.5 yet?
I haven’t yet.
My mistake was setting the initial instance up with rights for
Everyone to Create Ticket and Reply To Ticket. Is it possible to
reverse this from a database perspective as I need to only see the
local support agents in the owner drop down (all the agents have
email address in the same domain).
I understand that the common wisdom is to upgrade to 3.4.5, I am
however not sure that it will deliver the result I require.
Well, the problem you’ve described is exactly the problem we’ve fixed.
I’m not sure it’ll deliver the result you require either. It is,
however, the first step you should be taking if you’re looking for help
from me or anyone else on the mailinglist.
Best,
Jesse
Well, the problem you’ve described is exactly the problem we’ve fixed.
I’m not sure it’ll deliver the result you require either. It is,
however, the first step you should be taking if you’re looking for
help
from me or anyone else on the mailinglist.
Apologies, I did not mean to offend.
The upgrade to 3.4.5 improved the situation, the owner query now
returns results on the Query Builder page.
It is however still taking some time to complete the query - 108
seconds with a cost that looks like :
Unique (cost=26792.76…26794.16 rows=16 width=2692)
To the meat of the question - at install time I setup RT to allow
Everyone “Create Ticket” and “Reply To Ticket”. Needless to say this
was a mistake 
The result is that RT has created lots of possible ticket owners.
I am unclear on how Users relate ACLs and ACLs to Tickets, can
someone help me understand how to go about fixing my mistake ?
Thanks for all the help and a great product,
Andre
Andre Sachs
IT Architect, Clue Technologies PTY (LTD)
email: andre@clue.co.za
Well, the problem you’ve described is exactly the problem we’ve fixed.
I’m not sure it’ll deliver the result you require either. It is,
however, the first step you should be taking if you’re looking for
help
from me or anyone else on the mailinglist.
Apologies, I did not mean to offend.
The upgrade to 3.4.5 improved the situation, the owner query now
returns results on the Query Builder page.
It is however still taking some time to complete the query - 108
seconds with a cost that looks like :
Unique (cost=26792.76…26794.16 rows=16 width=2692)
To the meat of the question - at install time I setup RT to allow
Everyone “Create Ticket” and “Reply To Ticket”. Needless to say this
was a mistake
The result is that RT has created lots of possible ticket owners.
Neither of those implies “Own Ticket” Perhaps you’ve granted “Everyone”
the right to Own tickets? Also, are you vacuuming your postgres database
regularly?
The upgrade to 3.4.5 improved the situation, the owner query now
returns results on the Query Builder page.
It is however still taking some time to complete the query - 108
seconds with a cost that looks like :
Unique (cost=26792.76…26794.16 rows=16 width=2692)
To the meat of the question - at install time I setup RT to allow
Everyone “Create Ticket” and “Reply To Ticket”. Needless to say this
was a mistake
The result is that RT has created lots of possible ticket owners.
Neither of those implies “Own Ticket” Perhaps you’ve granted
“Everyone”
the right to Own tickets? Also, are you vacuuming your postgres
database
regularly?
Everyone has rights to “CreateTicket” and “ReplyToTicket”. The queues
themselves don’t specify any additional rights for the Everyone
group. The
database was freshly vacuumed before the above result was posted.
I not sure how the “OwnTicket” right has been set for all the
requesters unless its something that happened historically in 3.0.11.
Here are the table row counts for the tables involved in the slow query:
acl: 833
cachedgroupmembers: 1829946
principals: 861954
users: 52835
And the query that is take the time is :
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, CachedGroupMembers CachedGroupMembers_3 WHERE
((ACL_2.PrincipalId = CachedGroupMembers_3.GroupId)) AND
((ACL_2.PrincipalType = ‘Group’)) AND ((ACL_2.RightName =
‘OwnTicket’)) AND ((CachedGroupMembers_3.MemberId = Principals_1.id))
AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType
= ‘User’)) AND ((Principals_1.id != ‘1’)) AND ((main.id =
Principals_1.id)) AND ((ACL_2.ObjectType = ‘RT::Queue’) OR
(ACL_2.ObjectType = ‘RT::System’)) ORDER BY main.Name ASC;
Thanks and regards,
Andre
Andre Sachs
IT Architect, Clue Technologies PTY (LTD)
email: andre@clue.co.za