LWLocks by LockManager slowing large DB

Hello, apologies for the long post, but I want to make sure I’ve got enough
details to describe the problem for y’all.

I’ve got a 64-core (Ubuntu 18.04 – 240GB RAM running at GCP) instance
running PG 13.2 and PostGIS 3.1.1 and we’re having troubles getting it to
run more than 30 or so large queries at the same time accessing the same
tables. With 60 threads, each thread is only running at ~30% CPU and no
diskIO/IOWait (once the tables become cached).

Boiling the complex queries down to their simplest form, we test running 60
of this query simultaneously:

select

count(*)

from

travel_processing_v5.llc_zone z,

parent_set10.usca_trip_points7 t

where t.year_num = 2019 and t.month_num = 9

and st_intersects(t.lock_geom, z.s_geom)

and st_intersects(t.lock_geom, z.e_geom);

llc_zone = 981 rows (568k disk size) with s_geom and e_geom both of
datatype geometry(Multipolygon, 2163)

usca_trip_points7 = 79 million rows (469G disk size) with t.lock_geom
datatype geometry(Linestring, 2163)

(more detailed schema/stats can be provided if helpful)

postgresql.conf is pretty normal for a large system like this (with
appropriate shared_buffer, work_mem, etc. – can be provided if helpful, too)

What I’m finding in pg_stat_activity when running this is lots of
wait_events of type ‘LockManager’.

Rebuilding with CFLAGS=" -fno-omit-frame-pointer"
--prefix=/usr/local/pgsql_13debug --enable-dtrace CPPFLAGS='-DLOCK_DEBUG'
and then setting trace_lwlocks yields lots of records looking like:

[39691] LOG: 39691: LWLockAcquire(LockManager 0x7fab2cc09d80): excl 0
shared 0 haswaiters 1 waiters 6 rOK 1

Does anyone have any advice on how to alleviate LockManager’s LWlock issue?

Thanks for any assistance!

---Paul

Paul Friedman

CTO

677 Harrison St | San Francisco, CA 94107

*M:* (650) 270-7676

*E-mail:* paul(dot)friedman(at)streetlightdata(dot)com