On 7/13/23 07:02, Nikolay Samokhvalov wrote:
> We're observing a few cases with lockmanager spikes in a few quite
> loaded systems.
>
> These cases are different; queries are different, Postgres versions are
> 12, 13, and 14.
>
> But in all cases, servers are quite beefy (96-128 vCPUs, ~600-800 GiB)
> receiving a lot of TPS (a few dozens of thousands). Most queries that
> struggle from wait_event=lockmanager involve a substantial number of
> tables/indexes, often with partitioning.
>
> FP_LOCK_SLOTS_PER_BACKEND is now hard-coded 16 in storage/proc.h – and
> it is now very easy to hit this threshold in a loaded system,
> especially, for example, if a table with a dozen of indexes was
> partitioned. It seems any system with good growth hits it sooner or later.
>
> I wonder, would it make sense to:
> 1) either consider increasing this hard-coded value, taking into account
> that 16 seems to be very low for modern workloads, schemas, and hardware
> – say, it could be 64,
Well, that has a cost too, as it makes PGPROC larger, right? At the
moment that struct is already ~880B / 14 cachelines, adding 48 XIDs
would make it +192B / +3 cachelines. I doubt that won't impact other
common workloads ...
However, the lmgr/README says this is meant to alleviate contention on
the lmgr partition locks. Wouldn't it be better to increase the number
of those locks, without touching the PGPROC stuff? Could this be tuned
using some heuristics based on number of connections?
> 2) or even make it configurable – a new GUC.
>
I'm rather strongly against adding yet another GUC for this low-level
stuff. We already have enough of such knobs it's almost impossible for
regular users to actually tune the system without doing something wrong.
I'd even say it's actively harmful, especially if it's aimed at very
common setups/workloads (like here).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company