Re: LWLocks by LockManager slowing large DB

Hi,

On 2021-04-12 12:37:42 -0700, Paul Friedman wrote:
> Boiling the complex queries down to their simplest form, we test running 60
> of this query simultaneously:

How long does one execution of these queries take (on average)? The
likely bottlenecks are very different between running 60 concurrent
queries that each complete in 0.1ms and ones that take > 1s.

Could you show the results for a query like
SELECT state, backend_type, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1, 2, 3, 4 ORDER BY count(*) DESC;
?

Without knowing the proportion of LockManager wait events compared to
the rest it's hard to know what to make of it.

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

It'd be useful to get a perf profile for this. Both for cpu time and for
what ends up blocking on kernel-level locks. E.g. something like

# cpu time
perf record --call-graph dwarf -F 500 -a sleep 5
perf report --no-children --sort comm,symbol

To send it to the list you can use something like
perf report --no-children --sort comm,symbol|head -n 500 > somefile

# kernel level blocking on locks
perf record --call-graph dwarf -e syscalls:sys_enter_futex -a sleep 3
perf report --no-children --sort comm,symbol

Greetings,

Andres Freund