Re: Lock contention high

В Чт, 28/10/2021 в 03:14 +0530, Ashkil Dighin пишет:
> Hi,
> Yes, lock contention reduced with postgresqlv14.
> Lock acquire reduced 18% to 10%
> 10.49 %postgres postgres [.] LWLockAcquire
> 5.09% postgres postgres [.] _bt_compare
>
> Is lock contention can be reduced to 0-3%?
> On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
>
>
> On Tuesday, October 26, 2021, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > Hi,
> >
> > On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> > > PostgreSQL version: 13.3
> >
> > You could try postgres 14 - that did improve scalability in some areas.
> >
> >
> >
> > > Perf data for 24vu(TPC-C)
> > > --------------------------------
> > >
> > > 18.99% postgres postgres [.] LWLockAcquire
> > > 7.09% postgres postgres [.] _bt_compare
> > > 8.66% postgres postgres [.] LWLockRelease
> > > 2.28% postgres postgres [.] GetSnapshotData
> > > 2.25% postgres postgres [.] hash_search_with_hash_value
> > > 2.11% postgres postgres [.] XLogInsertRecord
> > > 1.98% postgres postgres [.] PinBuffer
> >
> > To be more useful you'd need to create a profile with 'caller' information
> > using 'perf record --call-graph dwarf', and then check what the important
> > callers are.
> >
> >
> > > Postgres.conf used in Baremetal
> > > ========================
> > > shared_buffers = 128GB(1/4 th RAM size)
> > > effective_cachesize=392 GB(1/3 or 75% of RAM size)
> >
> > 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.
> >

Could you try attached patch?
It reduces lock contention in buffer manager by not acquiring
two locks simultaneously on buffer eviction.

v1-0001-* - it is file for postgresql 14 and master branch
vpg13v1-0001-* - this file for postgresql 13

Corresponding (not so loud) discussion:
https://postgr.es/m/flat/1edbb61981fe1d99c3f20e3d56d6c88999f4227c.camel%40postgrespro.ru

--------

regards,

Yura Sokolov
y(dot)sokolov(at)postgrespro(dot)ru
funny(dot)falcon(at)gmail(dot)com