GCC/Clang编译的PostgreSQL运行TPCC时LWLock锁竞争过高调优咨询

问题背景 #

使用的软硬件环境 #

RAM size: 512 GB
SSD: 1TB
CPU(s): 256(0-255)
PostgreSQL version: 13.3
Operating system: RHEL8.4

24vu(TPCC)性能采样数据 #

14.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

Postgres.conf当前配置 #

shared_buffers = 64000MB
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_segments = 256
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

问题描述 #

在HammerDB测试环境运行TPCC基准测试时，经GCC/Clang编译的PostgreSQL 13.3的LWLockAcquire占比极高，存在严重锁竞争问题，需对应的调优手段降低锁竞争、提升数据库运行性能。

调优方案 #

数据库参数调整 #

• 调大lwlock_partitions参数：256核CPU场景下，默认的lwlock分区数（通常为8/16）不足以分散锁冲突，可调整为32或64，直接降低轻量锁的分区碰撞概率。
• 补充WAL相关配置：当前配置未设置max_wal_size，默认仅1G会频繁触发非计划checkpoint，加剧WAL锁竞争，可设置max_wal_size = 128GB，配合已有的1h checkpoint_timeout，大幅降低checkpoint触发频率；新增wal_compression = on，减小WAL写入量，缩短WAL锁持有时间。
• 优化后台写盘配置：新增调整bgwriter_delay = 10ms、bgwriter_lru_maxpages = 1000，提升后台写进程刷脏页的频率和数量，减少后端业务进程主动刷脏页的概率，降低缓冲区页锁的竞争。
• 调整内存相关参数：512GB内存场景下当前shared_buffers仅64GB，可提升至128GB，减少磁盘访问频次，同时新增effective_cache_size = 400GB，引导优化器生成更优执行计划，减少全表扫描带来的大批量缓冲区锁占用。
• 调大spinlock_delay参数：设置为1000~2000，让进程在锁竞争时优先自旋等待，减少直接进入睡眠排队带来的上下文切换开销，降低锁等待耗时。

Schema层面优化 #

• 拆分热点表为分区表：TPCC测试中order、inventory等表为访问热点，单表的热点索引页会产生集中的LWLock竞争，按范围或哈希拆分成分区表后，锁竞争会分散到多个分区的不同页上，可明显降低锁冲突概率。
• 优化热点索引：当前_bt_compare占比达7.09%，说明B树索引访问开销高，可针对高频查询的热点索引做前缀优化，或在业务允许的场景下替换为哈希索引，缩短索引访问时的锁持有时间。

编译优化 #

• 编译PostgreSQL时添加-march=native优化参数，适配当前CPU的指令集，提升锁操作的执行效率，减少锁持有时间；编译时关闭LOCK_DEBUG等调试类选项，避免额外的锁统计开销加剧竞争。

操作系统层面优化 #

• 关闭RHEL8默认开启的透明大页（THP），避免大页内存分配延迟导致的锁持有时间变长，配合数据库已开启的静态大页（huge_pages=on）降低内存寻址开销。
• 将CPU调频策略设置为performance，关闭节能模式，避免CPU频率动态切换带来的执行延迟，缩短锁持有周期。
• SSD磁盘的I/O调度器修改为none或mq-deadline，提升I/O响应速度，减少缓冲区I/O等待过程中的锁占用时间。

内容的提问来源于stack exchange，提问作者hpcresearch