Re: LWLocks by LockManager slowing large DB

Hi,

On 2021-04-13 09:33:48 -0700, Paul Friedman wrote:
> I've attached the 2 perf reports. From the 2nd one, I can see lots of
> time waiting for TOAST table locks on the geometry column, but I
> definitely don't fully understand the implications or why LockManager
> would be struggling here.

Oh, that is interesting. For toast tables we do not keep locks held for
the duration of the transaction, but release the lock as soon as one
access is done. It seems your workload is doing so many toast accesses
that the table / index level locking for toast tables gets to be the
bottleneck.

It'd be interesting to know if the issue vanishes if you force the lock
on the toast table and its index to be acquired explicitly.

You can find the toast table names with something like:

SELECT reltoastrelid::regclass
FROM pg_class
WHERE oid IN ('travel_processing_v5.llc_zone'::regclass, 'travel_processing_v5.llc_zone'::regclass);

That should give you two relation names looking like
"pg_toast.pg_toast_24576", just with a different number.

If you then change your workload to be (with adjusted OIDs of course):

BEGIN;
SELECT * FROM pg_toast.pg_toast_24576 LIMIT 0;
SELECT * FROM pg_toast.pg_toast_64454 LIMIT 0;
<youquery>
COMMIT;

Does the scalability issue vanish?

Regards,

Andres