Re: Lock contention high

Hi,

On 2021-10-25 18:38:40 -0600, Michael Lewis wrote:
> On Mon, Oct 25, 2021, 5:36 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> If your hot data set is actually larger than s_b, I'd recommend trying a
> larger s_b. It's plausible that a good chunk of lock contention is from
> that.

> How much larger might you go?

I've seen s_b in the ~700GB range being a considerable speedup over lower
values quite a few years ago. I don't see a clear cut upper boundary. The one
thing this can regress measurably is the speed of dropping / truncating
tables.

> Any write ups on lock contention as it relates to shared buffers?

I don't have a concrete thing to point you to, but searching for
NUM_BUFFER_PARTITIONS might point you to some discussions.

> How impactful might huge pages (off, transparent or on) be to the use of
> shared buffers and the related locking mechanism?

Using huge pages can *hugely* help performance-wise. Not directly by relieving
postgres-side contention however (it does reduce cache usage somewhat, but
it's mainly really just the frequency of TLB misses that makes the
difference).

Greetings,

Andres Freund