From: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
---|---|
To: | Michal Charemza <michal(at)charemza(dot)name>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Extremely slow to establish connection when user has a high number of roles |
Date: | 2024-04-20 12:57:20 |
Message-ID: | 75a5d1d6-a203-4f9a-a94c-a8b3ac044a14@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 4/20/24 13:55, Michal Charemza wrote:
> Hi,
>
> We're running PostgreSQL as essentially a data warehouse, and we have a few
> thousand roles, which are used to grant permissions on a table-by-table
> basis to a few thousand users, so a user would typically have say between 1
> and 2 thousand roles. There is also quite a lot of "churn" in terms of
> tables being created/removed, and permissions changed.
>
> The issue is that we're hitting a strange performance problem on
> connection. Sometimes it can take ~25 to 40 seconds just to connect,
> although it's often way quicker. There seems to be no middle ground - never
> have I seen a connection take between 0.5 and 25 seconds for example. We
> suspect it's related to the number of roles the connecting user has
> (including via other roles), because if we remove all roles but one from
> the connecting user (the one that grants connection permissions),
> connecting is always virtually instantaneous.
>
I tried a couple simple setups with many roles (user with many roles
granted directly and with many roles granted through other roles), but
I've been unable to reproduce this.
> The closest issue that I can find that's similar is
> https://www.postgresql.org/message-id/flat/CAGvXd3OSMbJQwOSc-Tq-Ro1CAz%3DvggErdSG7pv2s6vmmTOLJSg%40mail.gmail.com,
> which reports that GRANT role is slow with a high number of roles - but in
> our case, it's connecting that's the problem, before (as far as we can
> tell) even one query is run. The database is busy, say up to 60-80% on a 16
> VCPU machine - even if it's a "good amount" below 100%, the issue occurs.
>
> Is there anything we can do to investigate (or hopefully fix!) the issue?
>
A reproducer would be great - a script that creates user/roles, and
triggers the long login time would allow us to investigate that.
Another option would be to get a perf profile from the process busy with
logging the user in - assuming it's CPU-intensive, and not (e.g.) some
sort of locking issue.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-04-20 14:37:51 | Re: Extremely slow to establish connection when user has a high number of roles |
Previous Message | Michal Charemza | 2024-04-20 11:55:12 | Extremely slow to establish connection when user has a high number of roles |