Recently upgraded from 4.0 to 4.2 and have noticed that we are now getting slow queries on pretty much all ticket history queries. I ’ve been reading the list and attempting to google but I cannot seem to figure out what the cause is.
This RT database started life 11 years ago and has been dragged along from upgrade to upgrade and this is the first time we’ve run into any real issue. I’ve compared our Schema with a freshly installed version of 4.2 and don’t see any indexes missing, or anything along those lines.
I think that it has something to do with the way either have groups setup, or permissions, since if I make my account an admin/super user all the queries are super fast…
We are running on postgresql 9.3.4
here is the query that is causing us issues…
SQL(20.280673s):
SELECT COUNT(DISTINCT main.id)
FROM Tickets main
JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = ‘rt::ticket-role’ ) AND ( LOWER(Groups_1.Name) = ‘requestor’ ) AND ( Groups_1.Instance = main.id )
JOIN Groups Groups_3 ON ( LOWER(Groups_3.Domain) = ‘rt::ticket-role’ ) AND ( Groups_3.Instance = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = ‘0’ ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )
LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.Disabled = ‘0’ ) AND ( CachedGroupMembers_4.MemberId = ‘22’ ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )
WHERE (
(
( main.Queue = ‘1’ OR main.Queue = ‘4’ OR main.Queue = ‘5’ ) OR
( main.Owner = ‘22’ AND main.Queue = ‘2’ ) OR
( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = ‘admincc’ AND main.Queue = ‘2’ )
)
)
AND (main.IsMerged IS NULL)
AND (main.Status != ‘deleted’)
AND (main.Type = ‘ticket’)
AND (
( CachedGroupMembers_2.MemberId = ‘364’ ) AND
( LOWER(main.Status) = ‘new’ OR LOWER(main.Status) = ‘open’ OR LOWER(main.Status) = ‘stalled’ )
)
And then the next query which returns the data takes about the same time to run. so a small ticket takes around 40 seconds to render.
and the query plan
Aggregate (cost=37.50…37.51 rows=1 width=4)
→ Nested Loop Left Join (cost=1.95…37.50 rows=1 width=4)
Filter: ((main.queue = 1) OR (main.queue = 4) OR (main.queue = 5) OR ((main.owner = 22) AND (main.queue = 2)) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = ‘admincc’::text) AND (main.queue = 2)))
→ Nested Loop (cost=1.53…30.61 rows=1 width=22)
→ Nested Loop (cost=1.11…22.16 rows=1 width=26)
Join Filter: (groups_1.instance = main.id)
→ Nested Loop (cost=0.83…18.32 rows=1 width=22)
→ Index Scan using groups2 on groups groups_1 (cost=0.42…8.44 rows=1 width=8)
Index Cond: ((lower((domain)::text) = ‘rt::ticket-role’::text) AND (lower((name)::text) = ‘requestor’::text))
→ Index Scan using groups2 on groups groups_3 (cost=0.41…9.87 rows=1 width=14)
Index Cond: ((lower((domain)::text) = ‘rt::ticket-role’::text) AND (instance = groups_1.instance))
→ Index Scan using tickets_pkey on tickets main (cost=0.28…3.83 rows=1 width=12)
Index Cond: (id = groups_3.instance)
Filter: ((ismerged IS NULL) AND ((status)::text <> ‘deleted’::text) AND ((type)::text = ‘ticket’::text) AND ((queue = 1) OR (queue = 4) OR (queue = 5) OR (queue = 2) OR (queue = 2)) AND ((lower((status)::text) = ‘new’::text) OR (lower((status)::text) = ‘open’::text) OR (lower((status)::text) = ‘stalled’::text)))
→ Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.41…8.44 rows=1 width=4)
Index Cond: ((groupid = groups_1.id) AND (memberid = 364) AND (disabled = 0::smallint))
→ Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_4 (cost=0.41…6.85 rows=1 width=8)
Index Cond: ((groupid = groups_3.id) AND (memberid = 22) AND (disabled = 0::smallint))
(18 rows)
So pointers on places to look or things to try would be very helpful. I am still trying to wrap my head around why if I am root it doesn’t slow down, which leads me to think it has something to do with the way we have permissions setup.
Thanks,
-Patrick
Patrick Muldoon
Network/Software Engineer
INOC (http://www.inoc.net)
PGPKEY (Colocation, Cloud And Internet Solutions Provider | Albany, New York | INOC Data Centers)
Key ID: 0x2D808DE5
I do not fear computers. I fear the lack of them. - Isaac Asimov
here is the query that is causing us issues…
SQL(20.280673s): [snip]
This is because you (user #22) have rights to see queue 2, but only see
tickets in it which you are the AdminCc or Owner of. RT applies these
limits in SQL in RT 4.2 by default (the configuration defaulted to off
in 4.0 and lower), and you’re running into a pessimal case.
If you turn $UseSQLForACLChecks off, this query will likely speed up –
but you will run into the situation where ticket result pages may be
“short” due to applying ACLs right before they are displayed. That is,
a page which nominally contains 50 tickets may only display 40, 20, or 0
responses and still contain a “next page” link.
Ther other fix is to adjust your rights on the queue to not rely on
ticket roles to control ShowTicket.
and the query plan
[snip]
For future reference, EXPLAIN ANALYZE will give you the realtime
amounts, as well.
So pointers on places to look or things to try would be very
helpful. I am still trying to wrap my head around why if I
am root it doesn�t slow down, which leads me to think it has
something to do with the way we have permissions setup.
SuperUser ignores all permissions, yes.
here is the query that is causing us issues…
SQL(20.280673s): [snip]
This is because you (user #22) have rights to see queue 2, but only see
tickets in it which you are the AdminCc or Owner of. RT applies these
limits in SQL in RT 4.2 by default (the configuration defaulted to off
in 4.0 and lower), and you’re running into a pessimal case.
If you turn $UseSQLForACLChecks off, this query will likely speed up –
but you will run into the situation where ticket result pages may be
“short” due to applying ACLs right before they are displayed. That is,
a page which nominally contains 50 tickets may only display 40, 20, or 0
responses and still contain a “next page” link.
Ther other fix is to adjust your rights on the queue to not rely on
ticket roles to control ShowTicket.
Thanks, That makes sense. At the same time as upgrading to 4.2, we also enabled the approvals. which is what appears to be causing this. as 2 is ___Approvals queue.
Following the information in approvers rights section it said:
To grant rights to your Change Approvers group, go to the queue configuration page for the ___Approvals queue. Click on Group Rights in the page menu. Grant ShowTicket and ModifyTicket rights to the Owner and AdminCc roles. This should be enough for most cases.
Now members of the ‘Change Approvers’ group can act on approvals even if they have no SuperUser rights.
So now to figure out proper way to assign that without making the rest crawl…
-Patrick
Patrick Muldoon
Network/Software Engineer
INOC (http://www.inoc.net)
PGPKEY (Colocation, Cloud And Internet Solutions Provider | Albany, New York | INOC Data Centers)
Key ID: 0x2D808DE5
I’m sorry a pentium won’t do, you need an SGI to connect with us.
So now to figure out proper way to assign that without making the rest crawl�
If you’re only using Approvals in one workflow, you can assign the
Change Approvers group as a queue-level AdminCc.
From RT Wiki: FAQ Page In most case it’s a problem with User/Groups rights
that makes too much users appears in the select owner dropdown. If this
dropdown seems to contains more than wanted people, then check everywhere
(Global rights/ Queue rights) in RT rights to see if OwnTicket right is not
granted to Everyone, Unprivileged or a group that shouldn’t have this right
and contains many people. You can also run the following SQL query on the
RT database to find each objects that grant OwnTicket:
SELECT http://search.oracle.com/search/search?group=MySQL&q=SELECT *
FROM http://search.oracle.com/search/search?group=MySQL&q=FROM ACL
where http://search.oracle.com/search/search?group=MySQL&q=WHERE
RightName=‘OwnTicket’;
e.g:
mysql> SELECT
http://search.oracle.com/search/search?group=MySQL&q=SELECT * FROM
http://search.oracle.com/search/search?group=MySQL&q=FROM ACL where
http://search.oracle.com/search/search?group=MySQL&q=WHERE
RightName=‘OwnTicket’;
2.
3. ±----±--------------±------------±----------±-----------±---------±--------±--------------------±--------------±--------------------+
4. | id | PrincipalType | PrincipalId | RightName | ObjectType |
ObjectId | Creator | Created | LastUpdatedBy | LastUpdated
|
5. ±----±--------------±------------±----------±-----------±---------±--------±--------------------±--------------±--------------------+
6. | 316 | Group | 50 | OwnTicket | RT::Queue |
1 | 0 | NULL
http://search.oracle.com/search/search?group=MySQL&q=NULL
| 0 | NULL
http://search.oracle.com/search/search?group=MySQL&q=NULL
|
7. …
8. …
9.
…
10. | 557 | Group | 50 | OwnTicket | RT::Queue |
27 | 12 | 2012-06-03 13:07:19 | 12 | 2012-06-03
13:07:19 |
11. | 669 | AdminCc | 271319 | OwnTicket | RT::Queue |
28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03
14:06:24 |
12. | 723 | Owner | 271321 | OwnTicket | RT::Queue |
28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03
14:06:24 |
13. | 911 | Group | 349063 | OwnTicket | RT::Queue |
33 | 12 | 2013-02-26 10:57:44 | 12 | 2013-02-26
10:57:44 |
14. ±----±--------------±------------±----------±-----------±---------±--------±--------------------±--------------±--------------------+
Here OwnTicket is granted to Owner on queue “28”. Check this on the UI:
https://rt/Admin/Queues/GroupRights.html?id=28
I also found that adding index to several more
tableshttp://blog.rabin.io/23/speed-up-and-improve-ticket-deleting-and-shredding-in-rtwill
speed up shredder as well,
RabinOn Fri, May 23, 2014 at 7:11 PM, Patrick Muldoon doon.bulk@inoc.net wrote:
Recently upgraded from 4.0 to 4.2 and have noticed that we are now getting
slow queries on pretty much all ticket history queries. I ’ve been
reading the list and attempting to google but I cannot seem to figure out
what the cause is.
This RT database started life 11 years ago and has been dragged along from
upgrade to upgrade and this is the first time we’ve run into any real
issue. I’ve compared our Schema with a freshly installed version of 4.2
and don’t see any indexes missing, or anything along those lines.
I think that it has something to do with the way either have groups setup,
or permissions, since if I make my account an admin/super user all the
queries are super fast…
We are running on postgresql 9.3.4
here is the query that is causing us issues…
SQL(20.280673s):
SELECT COUNT(DISTINCT main.id)
FROM Tickets main
JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = ‘rt::ticket-role’ )
AND ( LOWER(Groups_1.Name) = ‘requestor’ ) AND ( Groups_1.Instance =
main.id )
JOIN Groups Groups_3 ON ( LOWER(Groups_3.Domain) = ‘rt::ticket-role’ )
AND ( Groups_3.Instance = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.Disabled = ‘0’ ) AND ( CachedGroupMembers_2.GroupId =
Groups_1.id )
LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.Disabled = ‘0’ ) AND ( CachedGroupMembers_4.MemberId =
‘22’ ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )
WHERE (
(
( main.Queue = ‘1’ OR main.Queue = ‘4’ OR main.Queue = ‘5’ ) OR
( main.Owner = ‘22’ AND main.Queue = ‘2’ ) OR
( CachedGroupMembers_4.MemberId IS NOT NULL AND
LOWER(Groups_3.Name) = ‘admincc’ AND main.Queue = ‘2’ )
)
)
AND (main.IsMerged IS NULL)
AND (main.Status != ‘deleted’)
AND (main.Type = ‘ticket’)
AND (
( CachedGroupMembers_2.MemberId = ‘364’ ) AND
( LOWER(main.Status) = ‘new’ OR LOWER(main.Status) = ‘open’
OR LOWER(main.Status) = ‘stalled’ )
)
And then the next query which returns the data takes about the same time
to run. so a small ticket takes around 40 seconds to render.
and the query plan
Aggregate (cost=37.50…37.51 rows=1 width=4)
→ Nested Loop Left Join (cost=1.95…37.50 rows=1 width=4)
Filter: ((main.queue = 1) OR (main.queue = 4) OR (main.queue = 5)
OR ((main.owner = 22) AND (main.queue = 2)) OR
((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text)
= ‘admincc’::text) AND (main.queue = 2)))
→ Nested Loop (cost=1.53…30.61 rows=1 width=22)
→ Nested Loop (cost=1.11…22.16 rows=1 width=26)
Join Filter: (groups_1.instance = main.id)
→ Nested Loop (cost=0.83…18.32 rows=1 width=22)
→ Index Scan using groups2 on groups groups_1
(cost=0.42…8.44 rows=1 width=8)
Index Cond: ((lower((domain)::text) =
‘rt::ticket-role’::text) AND (lower((name)::text) = ‘requestor’::text))
→ Index Scan using groups2 on groups groups_3
(cost=0.41…9.87 rows=1 width=14)
Index Cond: ((lower((domain)::text) =
‘rt::ticket-role’::text) AND (instance = groups_1.instance))
→ Index Scan using tickets_pkey on tickets main
(cost=0.28…3.83 rows=1 width=12)
Index Cond: (id = groups_3.instance)
Filter: ((ismerged IS NULL) AND ((status)::text
<> ‘deleted’::text) AND ((type)::text = ‘ticket’::text) AND ((queue = 1) OR
(queue = 4) OR (queue = 5) OR (queue = 2) OR (queue = 2)) AND
((lower((status)::text) = ‘new’::text) OR (lower((status)::text) =
‘open’::text) OR (lower((status)::text) = ‘stalled’::text)))
→ Index Only Scan using disgroumem on cachedgroupmembers
cachedgroupmembers_2 (cost=0.41…8.44 rows=1 width=4)
Index Cond: ((groupid = groups_1.id) AND (memberid =
364) AND (disabled = 0::smallint))
→ Index Only Scan using disgroumem on cachedgroupmembers
cachedgroupmembers_4 (cost=0.41…6.85 rows=1 width=8)
Index Cond: ((groupid = groups_3.id) AND (memberid = 22)
AND (disabled = 0::smallint))
(18 rows)
So pointers on places to look or things to try would be very helpful. I
am still trying to wrap my head around why if I am root it doesn’t slow
down, which leads me to think it has something to do with the way we have
permissions setup.
Thanks,
-Patrick
–
Patrick Muldoon
Network/Software Engineer
INOC (http://www.inoc.net)
PGPKEY (Colocation, Cloud And Internet Solutions Provider | Albany, New York | INOC Data Centers)
Key ID: 0x2D808DE5
I do not fear computers. I fear the lack of them. - Isaac Asimov
–
RT Training - Boston, September 9-10
http://bestpractical.com/training