Re: Lock contention high
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 an in-memory queue manager which is, generally speaking, very fast. Applications usually do stupid things. I've seen GUI doing "SELECT FOR UPDATE". And then the operator decided to have lunch. I'll leave the rest to your imagination. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Lock contention high
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 [.] 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 ? How many concurrent sesions are you running? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Lock contention high
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. Thanks, Michael. On 10/12/21 10:35 AM, 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 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
Lock contention high
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
