I'm not sure if your Owner dropdown is large, but this option might also
help in a similar way by turning the Owner filed into a autocomplete so
RT doesn't have to generate the full Owner dropdown on each page load
where that field is offered:
https://bestpractical.com/docs/rt/4.2/RT_Config.html#AutocompleteOwners
On 2/3/16 9:08 AM, Palle Girgensohn wrote:
Hi David,
Thanks for this input.
it takes the query from 1 minute+ (== timeout in fcgid) to subseond.
Big leap forward!
Thanks!
The two queries you posted are equally fast for me, ~ 8 ms, but render
different result, 15 vs 16 rows. :-(
Palle
3 feb. 2016 kl. 13:39 skrev David Gwynne <da...@gwynne.id.au>:
On Thu, Jan 07, 2016 at 01:57:46PM +0100, Palle Girgensohn wrote:
Hi,
For our RT database, just clicking "comment" takes five seconds. In general, RT
is very slow for us, and I believe that after 10+ years of use, we have bloat in the
database. 500k+ entries in CachedGroupMembers, for example. All of them but a handful are
enabled (disabled = 0).
So when I click comment in a ticket, I wait for this query five seconds. Seems
to me it produces a list of users allowed to comment on this.
The results can be very different for different queus.
We'd like to keep the history, so shredding old tickets is not the first choice
for us.
rt=# explain ANALYZE
rt-# SELECT DISTINCT main.id,
rt-# main.name
rt-# FROM Users main
rt-# CROSS JOIN ACL ACL_3
rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_2 ON
(CachedGroupMembers_2.MemberId = Principals_1.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON
(CachedGroupMembers_4.MemberId = Principals_1.id)
rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'
rt(# AND ACL_3.ObjectId = 75164)
rt(# OR (ACL_3.ObjectType = 'RT::Queue'
rt(# AND ACL_3.ObjectId = 21)
rt(# OR (ACL_3.ObjectType = 'RT::System'
rt(# AND ACL_3.ObjectId = 1))
rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)
rt-# AND (ACL_3.PrincipalType = 'Group')
rt-# AND (ACL_3.RightName = 'OwnTicket')
rt-# AND (CachedGroupMembers_2.Disabled = '0')
rt-# AND (CachedGroupMembers_2.GroupId = '4')
rt-# AND (CachedGroupMembers_4.Disabled = '0')
rt-# AND (Principals_1.Disabled = '0')
rt-# AND (Principals_1.PrincipalType = 'User')
rt-# AND (Principals_1.id != '1')
rt-# ORDER BY main.Name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=554.36..554.37 rows=1 width=29) (actual time=5927.879..5927.937
rows=72 loops=1)
-> Sort (cost=554.36..554.37 rows=1 width=29) (actual
time=5927.877..5927.893 rows=149 loops=1)
Sort Key: main.name, main.id
Sort Method: quicksort Memory: 32kB
-> Nested Loop (cost=1.84..554.35 rows=1 width=29) (actual
time=5.926..5927.400 rows=149 loops=1)
-> Nested Loop (cost=1.56..550.64 rows=2 width=33) (actual
time=0.152..78.279 rows=129788 loops=1)
-> Nested Loop (cost=1.13..548.76 rows=1 width=37)
(actual time=0.131..7.133 rows=134 loops=1)
-> Nested Loop (cost=0.71..493.88 rows=36
width=33) (actual time=0.115..4.984 rows=136 loops=1)
-> Index Only Scan using disgroumem on
cachedgroupmembers cachedgroupmembers_2 (cost=0.42..5.94 rows=76 width=4) (actual
time=0.079..0.152 rows=137 loops=1)
Index Cond: ((groupid = 4) AND (disabled
= 0::smallint))
Heap Fetches: 0
-> Index Scan using users_pkey on users main
(cost=0.29..6.41 rows=1 width=29) (actual time=0.033..0.034 rows=1 loops=137)
Index Cond: (id =
cachedgroupmembers_2.memberid)
-> Index Scan using principals_pkey on principals
principals_1 (cost=0.42..1.51 rows=1 width=4) (actual time=0.014..0.015 rows=1
loops=136)
Index Cond: (id = main.id)
Filter: ((id <> 1) AND (disabled =
0::smallint) AND (principaltype = 'User'::text))
Rows Removed by Filter: 0
-> Index Only Scan using cachedgroupmembers2 on
cachedgroupmembers cachedgroupmembers_4 (cost=0.42..1.67 rows=21 width=8) (actual
time=0.011..0.290 rows=969 loops=134)
Index Cond: ((memberid = principals_1.id) AND
(disabled = 0::smallint))
Heap Fetches: 0
-> Index Only Scan using acl1 on acl acl_3 (cost=0.28..1.85
rows=1 width=4) (actual time=0.045..0.045 rows=0 loops=129788)
Index Cond: ((rightname = 'OwnTicket'::text) AND
(principaltype = 'Group'::text) AND (principalid =
cachedgroupmembers_4.groupid))
Filter: (((objecttype = 'RT::Ticket'::text) AND (objectid
= 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid = 21)) OR
((objecttype = 'RT::System'::text) AND (objectid = 1)))
Rows Removed by Filter: 0
Heap Fetches: 0
Planning time: 6.461 ms
Execution time: 5928.204 ms
(27 rows)
If I remove the join on CachedGroupMembers_2 (the one that joins on memberid =
principals.id where groupid = 4), it is lightning fast.
rt=# explain ANALYZE
rt-# SELECT DISTINCT main.id,
rt-# main.name
rt-# FROM Users main
rt-# CROSS JOIN ACL ACL_3
rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)
rt-# --JOIN CachedGroupMembers CachedGroupMembers_2 ON
(CachedGroupMembers_2.MemberId = Principals_1.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON
(CachedGroupMembers_4.MemberId = Principals_1.id)
rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'
rt(# AND ACL_3.ObjectId = 75164)
rt(# OR (ACL_3.ObjectType = 'RT::Queue'
rt(# AND ACL_3.ObjectId = 21)
rt(# OR (ACL_3.ObjectType = 'RT::System'
rt(# AND ACL_3.ObjectId = 1))
rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)
rt-# AND (ACL_3.PrincipalType = 'Group')
rt-# AND (ACL_3.RightName = 'OwnTicket')
rt-# -- AND (CachedGroupMembers_2.Disabled = '0')
rt-# -- AND (CachedGroupMembers_2.GroupId = '4')
rt-# AND (CachedGroupMembers_4.Disabled = '0')
rt-# AND (Principals_1.Disabled = '0')
rt-# AND (Principals_1.PrincipalType = 'User')
rt-# AND (Principals_1.id != '1')
rt-# ORDER BY main.Name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1323.30..1323.33 rows=4 width=29) (actual time=20.321..20.395
rows=74 loops=1)
-> Sort (cost=1323.30..1323.31 rows=4 width=29) (actual
time=20.320..20.340 rows=108 loops=1)
Sort Key: main.name, main.id
Sort Method: quicksort Memory: 30kB
-> Nested Loop (cost=614.87..1323.26 rows=4 width=29) (actual
time=18.323..19.919 rows=108 loops=1)
Join Filter: (main.id = principals_1.id)
-> Hash Join (cost=614.44..724.20 rows=1232 width=33) (actual
time=18.305..18.755 rows=124 loops=1)
Hash Cond: (cachedgroupmembers_4.memberid = main.id)
-> Nested Loop (cost=0.71..71.95 rows=2620 width=4)
(actual time=0.168..0.456 rows=136 loops=1)
-> Index Only Scan using acl1 on acl acl_3
(cost=0.28..12.31 rows=13 width=4) (actual time=0.149..0.238 rows=12 loops=1)
Index Cond: ((rightname = 'OwnTicket'::text)
AND (principaltype = 'Group'::text))
Filter: (((objecttype = 'RT::Ticket'::text)
AND (objectid = 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid =
21)) OR ((objecttype = 'RT::System'::text) AND (objectid = 1)))
Rows Removed by Filter: 108
Heap Fetches: 0
-> Index Only Scan using disgroumem on
cachedgroupmembers cachedgroupmembers_4 (cost=0.42..4.54 rows=5 width=8) (actual
time=0.009..0.013 rows=11 loops=12)
Index Cond: ((groupid = acl_3.principalid) AND
(disabled = 0::smallint))
Heap Fetches: 0
-> Hash (cost=454.44..454.44 rows=12744 width=29)
(actual time=18.118..18.118 rows=12819 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 771kB
-> Seq Scan on users main (cost=0.00..454.44
rows=12744 width=29) (actual time=0.009..9.680 rows=12819 loops=1)
-> Index Scan using principals_pkey on principals principals_1
(cost=0.42..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=124)
Index Cond: (id = cachedgroupmembers_4.memberid)
Filter: ((id <> 1) AND (disabled = 0::smallint) AND
(principaltype = 'User'::text))
Rows Removed by Filter: 0
Planning time: 2.446 ms
Execution time: 20.726 ms
(26 rows)
Any ideas how to make RT quicker here? What is the purpose of this query
anyway? I'm just getting the comments view?
ola,
we hit this today while working on updating our installation. another
guy figured out that reverting
https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03
brings the performance back in line with what we experience with
our currently 4.0 install.
it takes the query from ~5500ms down to ~110ms
however, while he was figuring that out, i was tinkering with the
query in psql with the intention of making it fast and then tricking
RT into generating the query. the query i ended up with runs in
about 8ms.
the current (slow) query looks like that for us:
SELECT
DISTINCT main.*
FROM
Users main
CROSS JOIN ACL ACL_3
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id )
JOIN CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.MemberId = Principals_1.id )
WHERE
(
(ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR
(ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)
) AND
(ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND
(ACL_3.PrincipalType = 'Group') AND
(ACL_3.RightName = 'OwnTicket') AND
(CachedGroupMembers_2.Disabled = '0') AND
(CachedGroupMembers_2.GroupId = '4') AND
(CachedGroupMembers_4.Disabled = '0') AND
(Principals_1.Disabled = '0') AND
(Principals_1.PrincipalType = 'User') AND
(Principals_1.id != '1')
ORDER BY
main.Name ASC
;
after reverting the LimitToPrivileged out it generates:
SELECT
DISTINCT main.*
FROM
Users main
CROSS JOIN ACL ACL_2
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_3 ON (
CachedGroupMembers_3.MemberId = Principals_1.id )
WHERE
(
(ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 3) OR
(ACL_2.ObjectType = 'RT::System' AND ACL_2.ObjectId = 1)
) AND
(ACL_2.PrincipalId = CachedGroupMembers_3.GroupId) AND
(ACL_2.PrincipalType = 'Group') AND
(ACL_2.RightName = 'OwnTicket') AND
(CachedGroupMembers_3.Disabled = '0') AND
(Principals_1.Disabled = '0') AND
(Principals_1.PrincipalType = 'User') AND
(Principals_1.id != '1')
ORDER BY
main.Name ASC
;
this is the query i came up with:
SELECT
DISTINCT main.*
FROM
ACL ACL_3
LEFT JOIN Principals ON (ACL_3.principalid = Principals.id)
LEFT JOIN cachedgroupmembers ON (Principals.id =
cachedgroupmembers.groupid)
LEFT JOIN users main ON (cachedgroupmembers.memberid = main.id)
JOIN cachedgroupmembers cachedgroupmembers_2 ON
(cachedgroupmembers_2.memberid=main.id)
WHERE
(
(ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR
(ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)
) AND
(ACL_3.PrincipalType = 'Group') AND
(ACL_3.RightName = 'OwnTicket') AND
(Principals.disabled = '0') AND
(cachedgroupmembers.disabled = '0') AND
(cachedgroupmembers_2.groupid = 4) AND
(cachedgroupmembers_2.disabled = '0') AND
(main.id != 1)
;
cheers,
dlg
---------
RT 4.4 and RTIR Training Sessions
(http://bestpractical.com/services/training.html)
* Hamburg Germany — March 14 & 15, 2016
---------
RT 4.4 and RTIR Training Sessions
(http://bestpractical.com/services/training.html)
* Hamburg Germany March 14 & 15, 2016
