Re: Lock contention high

2021-10-13 Thread Laurenz Albe
On Thu, 2021-10-14 at 11:33 +0530, Ashkil Dighin wrote: > Captured the concurrent session with Netsat and pg-stat-actvity. Is the > procedure the right way to capture concurrent sesssions in postgresql? > > Select pg_stat_activity [some two dozen sessions] That doesn't look like you would get

Re: Lock contention high

2021-10-13 Thread Peter Geoghegan
On Wed, Oct 13, 2021 at 6:54 PM Jeremy Schneider wrote: > only a half GB memory for autovac? (it will have a mandatory run as soon > as you hit 200 mil XIDs, seems like you'd want the full max 1GB for it) While anti-wraparound vacuums will become a problem for TPC-C (unless you tune for it), it's

Re: Lock contention high

2021-10-13 Thread Jeremy Schneider
Your settings are interesting, I'm curious what the goal is for this particular hammerdb exercise. A few comments inline On 10/12/21 00:35, Ashkil Dighin wrote: > > Postgres.conf used  in Baremetal > > maintenance_work_mem = 512MB            only a half GB memory for a

Re: Lock contention high

2021-10-13 Thread Peter Geoghegan
On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin wrote: > Lock contention observed high in PostgreSQLv13.3 > The source code compiled with GNC(GCCv11.x) > PostgreSQL version: 13.3 > Operating system: RHEL8.3 > Kernel name:4.18.0-305.10.2.el8_4.x86_64 > RAM Size:512GB > SSD: 1TB > The environment u

Re: Lock contention high

2021-10-13 Thread Paul Friedman
Are you using PostGIS? If so, there is an issue with TOAST table locking having these symptoms. ---Paul On Wed, Oct 13, 2021 at 11:15 AM MichaelDBA wrote: > 1.Is there a way to tune the lock contention ? > 2.Is any recommendations to tune/reduce the lock contention via postgres.conf > > I th

Re: Lock contention high

2021-10-13 Thread MichaelDBA
1.Is there a way to tune the lock contention ? 2.Is any recommendations to tune/reduce the lock contention via postgres.conf I think you'd want to find *which* LW locks are being waited on, to see if it's something that can be easily tuned. You can check pg_stat_activity, or maybe create a cronj

Re: Lock contention high

2021-10-13 Thread Justin Pryzby
On Tue, Oct 12, 2021 at 01:05:12PM +0530, Ashkil Dighin wrote: > Hi, > Lock contention observed high in PostgreSQLv13.3 > The source code compiled with GNC(GCCv11.x) > PostgreSQL version: 13.3 > Operating system: RHEL8.3 > Kernel name:4.18.0-305.10.2.el8_4.x86_64 > RAM Size:512GB > SSD: 1TB > The