> 4 feb. 2016 kl. 00:48 skrev David Gwynne <da...@gwynne.id.au>: > > >> On 4 Feb 2016, at 00:08, Palle Girgensohn <gir...@pingpong.net> 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. :-( > > yeah, after i sent it i recognised some issues with mine. i can fix it, but > then id have to go back and make RT generate the query and that idea makes me > sad. >
Mmm, the problem is really the ORM. It never really marries well with SQL. Sets vs objects. Sadly, I don't think it'll be worth the effort trying to force RT to generate the query. But my experience with this specific implementation is limited. > if there's interest i can fix my query, but only if it'll help someone else I'm happy with the first advice, reverting the patch. It made RT usable from not being that! :-) Btw, already had AutocompleteOwnersForSearch enabled. Palle > >> >> 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