Re: Init connection time grows quadratically

From: "Maksim(dot)Melnikov" <m(dot)melnikov(at)postgrespro(dot)ru>
To: Andres Freund <andres(at)anarazel(dot)de>,Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Init connection time grows quadratically
Date: 2026-11-17 14:40:32
Message-ID: 667ea1ff-3883-4e3b-b665-7f967269216f@postgrespro.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 9/11/26 22:51, Andres Freund wrote:

> Maybe I am just missing something, but is this ever a benefit outside of
> completely arbitrary scenarios? Who has >= 8k concurrent connections doing
> nothing but connecting/disconnecting? Even if one were to agree that it's
> useful to optimize large huge_pages=off workloads, surely that's an absurd
> enough workload that nobody cares?

Hi,

as Matthias noted, ProcArrayAdd/ProcArrayRemove are used not only for
connect
but also for a two-phase commit. Therefore, it could be said, that we
optimize
the execution time of the `ProcArrayAdd` and `ProcArrayRemove` methods
for specific
configurations without huge_pages specifically for cases with great
utilization of
these methods. The scenario with connections is just the way to
reproduce this high
utilization. It seems that a real, significant effect from the patch in
production
systems can be most visible during moments of anomalous, sudden spikes
in load; but
anyway, the patch prove and eliminates a dependency of page faults and
make system more
stable.

On 9/18/26 04:54, shihao zhong wrote:
> The real problem there is locks and long transactions. This patch
> might help
> the connection storm, but may not fix it.

I agree, this patch cannot fix the "storm" issue, as such storms are
typically a consequence
of underlying infrastructure problems and should be addressed at the
infrastructure level.
Nevertheless, the patch helps to mitigate these effects and achieve more
stable behavior.

On 9/11/26 22:51, Andres Freund wrote:
> This isn't entirely free, needing to look at a separate cachelines that are
> frequently modified (and thus commonly won't be in the cpu-local cache), in
> reasonably common codepaths like ProcArrayEndTransactionInternal(),
> TransactionIdIsInProgress(), GetSnapshotData() is far from free.
Thanks for this point, here [1] I've done some experiments for huge_page=on
scenarios. It is interesting because from one side here we didn't detect any
page_faults, so we exclude patch influence on  base problem, and from the
other side, we didn't see any degradations, so we didn't see any side effect
of potential false sharing. Also I see that ProcGlobal->xids and
ProcGlobal->subxidStates are optimized by the same way, so suggested
approach
looks code consistent.

P.S. I agree that patch looks controversial. My motivation was
that even if some configuration isn't commonly used, it does not mean
that it isn't interesting for someone else at all and, as a consequence,
it can be optimized without degradation for basic scenarios.
Patch is rebased to SHA 5ffc8210377 and re-attached.

Best regards,
Maksim Melnikov

Links:
[1]
https://www.postgresql.org/message-id/08e7c338-e175-4474-be53-6538bb67d4b6%40postgrespro.ru

Attachment Content-Type Size
v7-0001-This-patch-reduce-connection-init-close-time.patch text/x-patch 16.9 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Zhijie Hou 2026-11-17 15:20:54 Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
Previous Message Jakub Wartak 2026-11-17 14:24:46 Re: libpq: SCRAM client proofs serialize across threads on OpenSSL 3 (re-init of the HMAC per PBKDF2 iteration)