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 (http://www.inoc.net/~doon)
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
