В Чт, 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
1. How to check which NUMA node in PostgreSQL process fetching from the
memory?
2. Is NUMA configuration is better for PostgreSQL?
vm.zone_reclaim_mode= 0
numactl --interleave = all /init.d/ PostgreSQL start
kernel.numa_balancing= 0
On Wednesday, November 17, 2021, arjun
Hi Askhil
PostgreSQL utilizes lightweight locks(LWLocks) to synchronize and control
access to the buffer content. A process acquires an LWLock in a shared
mode to read from the buffer and an exclusive mode to write to the buffer.
Therefore, while holding an exclusive lock, a process prevents ot
Ashkil Dighin writes:
> I suspect lock contention and performance issues with __int128. And I would
> like to check the performance by forcibly disabling int128(Maxalign16bytes)
> and enable like long long(maxlign 8bytes).
> Is it possible to disable int128 in PostgreSQL?
Sure, you can build wit
Hi
I suspect lock contention and performance issues with __int128. And I would
like to check the performance by forcibly disabling int128(Maxalign16bytes)
and enable like long long(maxlign 8bytes).
Is it possible to disable int128 in PostgreSQL?
On Thursday, October 28, 2021, Andres Freund wrote
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 “WalIn
Hi,
On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin
wrote:
>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
Hi,
On 2021-10-25 18:38:40 -0600, Michael Lewis wrote:
> On Mon, Oct 25, 2021, 5:36 PM Andres Freund 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?
On Mon, Oct 25, 2021, 5:36 PM Andres Freund 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? Any write ups on lock contention as it
relates to shared buffers
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.0
Hi
B-tree index used in the postgres environment
Checked on warehouse different values like 100,800,1600,2400 and 3200 with
virtual user 64
On different values(warehouse) the lock contention same i.e. approx 17% and
iostat usage is 30-40%
pg_Count_ware=100
-
17.76% postgres pos
Not using PostGIS
On Thursday, October 14, 2021, Paul Friedman <
paul.fried...@streetlightdata.com> wrote:
> 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 ther
NUMA node0 CPU(s): 0-63,128-191NUMA node1 CPU(s): 64-127,192-255
Thread(s) per core: 2
Core(s) per socket: 64
Socket(s): 2
NUMA node(s):2
corepinning(ta perf lock contention results for 24,32 vu
0-63
24: 18.03% postgres postgres[.] LWLockAcquire
32: 7.02%
Hi
Captured the concurrent session with Netsat and pg-stat-actvity. Is the
procedure the right way to capture concurrent sesssions in postgresql?
netstat -a | grep postgres tcp 0 0 0.0.0.0:postgres 0.0.0.0:* LISTEN tcp 0
0 :postgres :53984 ESTABLISHED tcp 0 0 :postgres :54012 ESTABLISHED tcp 0
74
Ashkil,
Can you bind postgres in single NUMA node, for instance:
$ taskset -pc 0-63
Then run your benchmark, compare results in terms of benchmark metrics &
presence on LWLock(Acquire|Release) in perf top.
BR,
Michael.
On 10/14/21 9:45 AM, Ashkil Dighin wrote:
NUMA node0 CPU(s): 0-63,
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
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
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
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
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
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
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
On 10/12/21 03:35, Ashkil Dighin wrote:
1.Is there a way to tune the lock contention ?
Lock contention is usually an application issue. Application processes
are stepping on each other's toes. I have never seen a situation where
the database would be slow with managing locks. Postgres uses a
On Tue, 2021-10-12 at 13:05 +0530, Ashkil Dighin wrote:
> Perf data for 24vu(TPC-C)
>
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLoc
Hi,
How many sockets are on motherboard?
What is CPU model and interconnect type (UPI?)?
Can you share output of "lscpu"?
If you have more than 1 NUMA node it may be worth to run PostgreSQL in
single NUMA node via taskset. It will eliminate access to remote memory
and speed up processing.
Th
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 environment used IBM metal and test benchmark environment HammerDbv4.2
Test case
26 matches
Mail list logo
