Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Em qua., 27 de mar. de 2024 às 14:35, Nathan Bossart < nathandboss...@gmail.com> escreveu: > On Wed, Mar 27, 2024 at 01:47:38PM -0300, Ranier Vilela wrote: > > Em qua., 27 de mar. de 2024 às 13:41, Nathan Bossart < > > nathandboss...@gmail.com> escreveu: > >> On Wed, Mar 27, 2024 at 01:21:23PM -03

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Wed, Mar 27, 2024 at 01:47:38PM -0300, Ranier Vilela wrote: > Em qua., 27 de mar. de 2024 às 13:41, Nathan Bossart < > nathandboss...@gmail.com> escreveu: >> On Wed, Mar 27, 2024 at 01:21:23PM -0300, Ranier Vilela wrote: >> > I think that left an oversight in a commit d365ae7 >> > < >> https://g

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Em qua., 27 de mar. de 2024 às 13:41, Nathan Bossart < nathandboss...@gmail.com> escreveu: > On Wed, Mar 27, 2024 at 01:21:23PM -0300, Ranier Vilela wrote: > > Nathan Bossart writes: > >>Committed with that change. Thanks for the guidance on this one. > > > > I think that left an oversight in a c

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Wed, Mar 27, 2024 at 01:21:23PM -0300, Ranier Vilela wrote: > Nathan Bossart writes: >>Committed with that change. Thanks for the guidance on this one. > > I think that left an oversight in a commit d365ae7 >

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Hi, Nathan Bossart writes: >Committed with that change. Thanks for the guidance on this one. I think that left an oversight in a commit d365ae7 If the admin_role is a NULL pointer, so, can be dereferenced in t

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Tue, Mar 26, 2024 at 03:08:00PM -0400, Tom Lane wrote: > My one remaining suggestion is that this comment isn't very precise > about what's happening: > > * If there is a previously-created Bloom filter, use it to determine > * whether the role is missing from the list. Otherwise, do

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > On Tue, Mar 26, 2024 at 02:16:03PM -0400, Tom Lane wrote: >> ... I'm not sold on your "ROLES_LIST_BLOOM_THRESHOLD * 10" >> value. Maybe it doesn't matter though. > Yeah, I wasn't sure how much to worry about this. I figured that we might > as well set it to a reasonable

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Tue, Mar 26, 2024 at 02:16:03PM -0400, Tom Lane wrote: > I did a little experimentation using the attached quick-hack C > function, and came to the conclusion that setting up the bloom filter > costs more or less as much as inserting 1000 or so OIDs the dumb way. > So we definitely want a thresh

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > I spent some time trying to get some ballpark figures but have thus far > been unsuccessful. Even if I was able to get good numbers, I'm not sure > how much they'd help us, as we'll still need to decide how much overhead we > are willing to take in comparison to the linea

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Here is a new version of the patch that I feel is in decent shape. On Mon, Mar 25, 2024 at 10:16:47AM -0500, Nathan Bossart wrote: > On Mon, Mar 25, 2024 at 11:08:39AM -0400, Tom Lane wrote: >> * The magic constants (crossover list length and bloom filter size) >> need some testing to see if there

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Mon, Mar 25, 2024 at 11:08:39AM -0400, Tom Lane wrote: > * The magic constants (crossover list length and bloom filter size) > need some testing to see if there are better values. They should > probably be made into named #defines, too. I suspect, with little > proof, that the bloom filter siz

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > Are there any changes you'd like to see for the Bloom patch [0]? I'd like > to see about getting that committed for v17. One thing that crossed my > mind is creating a combined list/filter that transparently created a filter > when necessary (for reuse elsewhere), but I'

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 04:41:49PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> LGTM > > Thanks for looking, I'll push that shortly. Are there any changes you'd like to see for the Bloom patch [0]? I'd like to see about getting that committed for v17. One thing that crossed my mind is cr

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > On Fri, Mar 22, 2024 at 11:54:48AM -0500, Nathan Bossart wrote: >> On Fri, Mar 22, 2024 at 12:53:15PM -0400, Tom Lane wrote: >>> Would you like to review the catcache patch further, or do you >>> think it's good to go? >> I'll take another look this afternoon. > LGTM Th

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 11:54:48AM -0500, Nathan Bossart wrote: > On Fri, Mar 22, 2024 at 12:53:15PM -0400, Tom Lane wrote: >> Would you like to review the catcache patch further, or do you >> think it's good to go? > > I'll take another look this afternoon. LGTM -- Nathan Bossart Amazon Web Se

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 12:53:15PM -0400, Tom Lane wrote: > Would you like to review the catcache patch further, or do you > think it's good to go? I'll take another look this afternoon. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > On Fri, Mar 22, 2024 at 11:27:46AM -0400, Tom Lane wrote: >> * Do we want to risk back-patching any of this, to fix the performance >> regression in v16? I think that the OP's situation is a pretty >> narrow one, but maybe he's not the only person who managed to dodge >>

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 11:27:46AM -0400, Tom Lane wrote: > Yeah, that's about what I'd expect: hash+bloom ought to remove > most (not quite all) of the opportunity for simd to shine, because > the bloom filter should eliminate most of the list_member_oid calls. Right. IMHO the SIMD work is still

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > On Fri, Mar 22, 2024 at 09:47:39AM -0500, Nathan Bossart wrote: >> hash hash+simd hash+simd+bloom >> create 1.27 1.27 1.28 >> grant 0.18 0.11 0.03 > For just hash+bloom, I'm seeing 1.29 and 0.04. Yeah, that's about what I'd expect: hash+b

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Fri, Mar 22, 2024 at 09:47:39AM -0500, Nathan Bossart wrote: > hash hash+simd hash+simd+bloom > create 1.27 1.27 1.28 > grant 0.18 0.11 0.03 For just hash+bloom, I'm seeing 1.29 and 0.04. -- Nathan Bossart Amazon Web Services: https://aws.amazon.co

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Thu, Mar 21, 2024 at 08:59:54PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> On Thu, Mar 21, 2024 at 03:40:12PM -0500, Nathan Bossart wrote: >>> On Thu, Mar 21, 2024 at 04:31:45PM -0400, Tom Lane wrote: I don't think we have any really cheap way to de-duplicate the role OIDs, es

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > The Bloom filter appears to help a bit, although it regresses the > create-roles.sql portion of the test. I'm assuming that's thanks to all > the extra pallocs and pfrees, which are probably avoidable if we store the > filter in a long-lived context and just clear it at t

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

First of all thank you for looking into this. At the moment we workaround the problem by altering `acc` ROLE into a SUPERUSER in PostgreSQL 16 instances. It sidestep the problem and having the lowest cost to implement for us. While at first we think this feels like opening a security hole, it does

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Thu, Mar 21, 2024 at 08:03:32PM -0500, Nathan Bossart wrote: > On Thu, Mar 21, 2024 at 08:59:54PM -0400, Tom Lane wrote: >> However ... I just remembered that we have a Bloom filter implementation >> in core now (src/backend/lib/bloomfilter.c). How about using that >> to quickly reject (hopeful

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Thu, Mar 21, 2024 at 08:59:54PM -0400, Tom Lane wrote: > However ... I just remembered that we have a Bloom filter implementation > in core now (src/backend/lib/bloomfilter.c). How about using that > to quickly reject (hopefully) most role OIDs, and only do the > list_member_oid check if the fi

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Nathan Bossart writes: > On Thu, Mar 21, 2024 at 03:40:12PM -0500, Nathan Bossart wrote: >> On Thu, Mar 21, 2024 at 04:31:45PM -0400, Tom Lane wrote: >>> I don't think we have any really cheap way to de-duplicate the role >>> OIDs, especially seeing that it has to be done on-the-fly within the >>>

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Thu, Mar 21, 2024 at 03:40:12PM -0500, Nathan Bossart wrote: > On Thu, Mar 21, 2024 at 04:31:45PM -0400, Tom Lane wrote: >> I don't think we have any really cheap way to de-duplicate the role >> OIDs, especially seeing that it has to be done on-the-fly within the >> collection loop, and the orde

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

On Thu, Mar 21, 2024 at 04:31:45PM -0400, Tom Lane wrote: > I wrote: >> ... I still see the problematic GRANT taking ~250ms, compared >> to 5ms in v15. roles_is_member_of is clearly on the hook for that. > > Ah: looks like that is mainly the fault of the list_append_unique_oid > calls in roles_is

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

I wrote: > ... I still see the problematic GRANT taking ~250ms, compared > to 5ms in v15. roles_is_member_of is clearly on the hook for that. Ah: looks like that is mainly the fault of the list_append_unique_oid calls in roles_is_member_of. That's also an O(N^2) cost of course, though with a muc

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

I wrote: > It looks like part of the blame might be ascribable to catcache.c, > as if you look at the problem microscopically you find that > roles_is_member_of is causing catcache to make a ton of AUTHMEMMEMROLE > catcache lists, and SearchSysCacheList is just iterating linearly > through the cach

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

Tom Lane: Actually, roles_is_member_of sucks before v16 too; the new thing is only that it's being invoked during GRANT ROLE. Using the roles created by the given test case, I see in v15: [...] So it takes ~3.5s to populate the roles_is_member_of cache for "acc" given this membership set. This

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

I wrote: > I poked into this a bit. It seems the problem is that as of v16, we > try to search for the "best" role membership path from the current > user to the target role, and that's done in a very brute-force way, > as a side effect of computing the set of *all* role memberships the > current

Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

[ redirecting to -hackers ] alex work writes: > We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42 > seconds > in production, the client process at PostgresSQL would use 100% of the CPU. > Which is a surprise compared to other instances running older PostgreSQL > releases.