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 :TPC-C
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 1.Is there a way to tune the lock contention ? 2.Is any recommendations to tune/reduce the lock contention via postgres.conf Postgres.conf used in Baremetal ======================== shared_buffers = 128GB(1/4 th RAM size) effective_cachesize=392 GB(1/3 or 75% of RAM size) huge_pages = on temp_buffers = 4000MB work_mem = 4000MB maintenance_work_mem = 512MB autovacuum_work_mem = -1 max_stack_depth = 7MB dynamic_shared_memory_type = posix max_files_per_process = 4000 effective_io_concurrency = 32 wal_level = minimal synchronous_commit = off wal_buffers = 512MB checkpoint_timeout = 1h checkpoint_completion_target = 1 checkpoint_warning = 0 log_min_messages = error log_min_error_statement = error log_timezone = 'GB' autovacuum = off datestyle = 'iso, dmy' timezone = 'GB' lc_messages = 'en_GB.UTF-8' lc_monetary = 'en_GB.UTF-8' lc_numeric = 'en_GB.UTF-8' lc_time = 'en_GB.UTF-8' default_text_search_config = 'pg_catalog.english' max_locks_per_transaction = 64 max_pred_locks_per_transaction = 64 Best Regards Anil