On Mon, Apr 25, 2022 at 10:33 AM Michael Lewis <lewis.michaelr@gmail.com> wrote:
>
> On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett <cbisnett@gmail.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
numberof 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
nowto reduce the odds of this type of traffic jam occurring again. I very much appreciate you sharing your experience.
IfI could reliably reproduce the issue or knew what data points to start collecting going forward, that would at least
giveme something to go on, but it feels like I am just waiting for it to happen again and hope that some bit of
informationmakes 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