Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
> 4 feb. 2016 kl. 02:11 skrev David Gwynne : > >> >> On 4 Feb 2016, at 10:21, Palle Girgensohn wrote: >> >> >> >> 4 feb. 2016 kl. 00:48 skrev David Gwynne : >> >>> On 4 Feb 2016, at 00:08, 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. :-( >>> >>> 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. > > and you still got hit with the slow query? yes. signature.asc Description: Message signed with OpenPGP using GPGMail - RT 4.4 and RTIR Training Sessions (http://bestpractical.com/services/training.html) * Hamburg Germany March 14 & 15, 2016
Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
> On 4 Feb 2016, at 10:21, Palle Girgensohn wrote: > > > > 4 feb. 2016 kl. 00:48 skrev David Gwynne : > >> >>> On 4 Feb 2016, at 00:08, 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. :-( >> >> 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. and you still got hit with the slow query? - RT 4.4 and RTIR Training Sessions (http://bestpractical.com/services/training.html) * Hamburg Germany March 14 & 15, 2016
Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
> 4 feb. 2016 kl. 00:48 skrev David Gwynne : > > >> On 4 Feb 2016, at 00:08, 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. :-( > > 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 : >>> >>> 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) (
Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
> On 4 Feb 2016, at 00:08, 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. :-( 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. if there's interest i can fix my query, but only if it'll help someone else. > > Palle > > > >> 3 feb. 2016 kl. 13:39 skrev David Gwynne : >> >> 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_
Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
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 : 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
Re: [rt-users] slow join with cachedgroupmembers for a simple "comment" click
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 : > > 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)) >>
