From: | Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> |
---|---|
To: | Ashkil Dighin <ashkildighin76(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de> |
Cc: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Lock contention high |
Date: | 2021-12-21 05:45:52 |
Message-ID: | bca5495fa6ef812028ae8eb9424b66a9da1ce47d.camel@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
В Чт, 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
Attachment | Content-Type | Size |
---|---|---|
vpg13v1-0001-bufmgr-do-not-acquire-two-partition-locks.patch | text/x-patch | 25.1 KB |
v1-0001-bufmgr-do-not-acquire-two-partition-lo.patch | text/x-patch | 25.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2021-12-21 21:13:37 | Re: Query is slower with a large proportion of NULLs in several columns |
Previous Message | Tom Lane | 2021-12-21 05:33:06 | Re: Query is slower with a large proportion of NULLs in several columns |