Re: LwLocks contention

On Mon, Apr 25, 2022 at 10:33 AM Michael Lewis <lewis(dot)michaelr(at)gmail(dot)com> wrote:
>
> On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett <cbisnett(at)gmail(dot)com> wrote:
>>
>> We're working to update our application so that we can
>> take advantage of the pruning. Are you also using native partitioning?
>
>
> No partitioned tables at all, but we do have 1800 tables and some very complex functions, some trigger insanity, huge number of indexes, etc etc.
>
> There are lots of things to fix, but I just do not yet have a good sense of the most important thing to address right now to reduce the odds of this type of traffic jam occurring again. I very much appreciate you sharing your experience. If I could reliably reproduce the issue or knew what data points to start collecting going forward, that would at least give me something to go on, but it feels like I am just waiting for it to happen again and hope that some bit of information makes itself known that time.
>
> Perhaps I should have posted this to the performance list instead of general.

In my experience lwlock contention (especially around buffer_mapping)
is more about concurrent write activity than any particular number of
tables/partitions. The first recommendation I would have is to install
pg_buffercache and see if you can capture some snapshots of what the
buffer cache looks like, especially looking for pinning_backends. I'd
also spend some time capturing pg_stat_activity output to see what
relations are in play for the queries that are waiting on said lwlocks
(especially trying to map write queries to tables/indexes).

Robert Treat
https://xzilla.net