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 Ref link: https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-postgresql-performance-and-scalability/ On Thursday, October 14, 2021, Peter Geoghegan <p...@bowt.ie> wrote: > On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin <ashkildighi...@gmail.com> > 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 >