YES!!! This completely alleviates the bottleneck and I'm able to run the
queries full-throttle. Thank you SO much for your help+insight.
Interestingly, "lock pg_toast.pg_toast_2233612264 in ACCESS SHARE MODE;"
which should do the same thing returns an error " ERROR:
"pg_toast_2233612264" is not a table or view"
Sounds like I should file this as a requested improvement?
Thanks again.
---Paul
Paul Friedman
CTO
677 Harrison St | San Francisco, CA 94107
M: (650) 270-7676
E-mail: paul.friedman@streetlightdata.com
-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Tuesday, April 13, 2021 11:17 AM
To: Paul Friedman <paul.friedman@streetlightdata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: 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