Lock contention high
| От | Ashkil Dighin |
|---|---|
| Тема | Lock contention high |
| Дата | |
| Msg-id | CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Lock contention high
(Mikhail Zhilin <zhilin.ms@phystech.edu>)
Re: Lock contention high (Mladen Gogala <gogala.mladen@gmail.com>) Re: Lock contention high (Justin Pryzby <pryzby@telsasoft.com>) Re: Lock contention high (Peter Geoghegan <pg@bowt.ie>) Re: Lock contention high (Jeremy Schneider <schneider@ardentperf.com>) Re: Lock contention high (Andres Freund <andres@anarazel.de>) |
| Список | pgsql-performance |
Hi,
Lock contention observed high in PostgreSQLv13.3
The source code compiled with GNC(GCCv11.x)
PostgreSQL version: 13.3
Operating system: RHEL8.3
Operating system: RHEL8.3
Kernel name:4.18.0-305.10.2.el8_4.
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
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
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
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Laurenz AlbeДата:
Сообщение: Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle