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 postgres [.] LWLockAcquire
4.88% postgres postgres [.] _bt_compare
3.10% postgres postgres [.] LWLockRelease
pg_Count_ware=800(previously I used Warehouse 800)
--------------------------------------------
17.91% postgres postgres [.] LWLockAcquire
5.76% postgres postgres [.] _bt_compare
3.06% postgres postgres [.] LWLockRelease
pg_Count_ware_1600
-----------------
17.80% postgres postgres [.] LWLockAcquire
5.88% postgres postgres [.] _bt_compare
2.70% postgres postgres [.] LWLockRelease
pg_Count_ware_2400
------------------
17.77% postgres postgres [.] LWLockAcquire
6.01% postgres postgres [.] _bt_compare
2.71% postgres postgres [.] LWLockRelease
pg_Count_ware_3200
------------------
17.46% postgres postgres [.] LWLockAcquire
6.32% postgres postgres [.] _bt_compare
2.86% postgres postgres [.] hash_search_with_hash_value
1.Tired different values of lock management values in postgres.conf but it not helped to reduce lock contention.
deadlock_timeout = 5s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
max_pred_locks_per_relation = -2
max_pred_locks_per_page = 2
2.Intention to check the postgreSQL scalability and performance or throughput(TPC-C/TPC-H)
with HammerDB and pgbench with server configuration on tune settings(postgresql.conf)-reduce lock contention
CPU's :256
Threadper core: 2
Core per socket: 64
Sockets: 2
NUMA node0 : 0-63,128-191
NUMA node1 : 64-127,192-255
RAM size :512GB
SSD :1TB
On Thursday, October 14, 2021, Peter Geoghegan <pg@xxxxxxx> wrote:
On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin <ashkildighin76@xxxxxxxxx> 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 used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
You didn't say how many TPC-C warehouses you used. In my experience,
people sometimes run TPC-C with relatively few, which will tend to
result in extreme contention on certain B-Tree leaf pages. (My
experiences are with BenchmarkSQL, but I can't imagine HammerDB is too
much different.)
Assuming that's the case here, for you, then it's not clear that you
have a real problem. You're really not supposed to run the benchmark
in that way, per the TPC-C spec, which strictly limits the number of
transactions per minute per warehouse -- for better or worse, valid
results generally require that you use lots of warehouses to get a
very large database (think terabytes). If you run the benchmark with
100 warehouses or less, on a big server, then the contention you'll
see will be out of all proportion to what you're ever likely to see in
the real world.
--
Peter Geoghegan
