如何让HPC集群Singularity容器内的PostgreSQL服务使用更多内存提升性能

问题根因说明 #

LSF统计的内存占用仅显示进程私有常驻内存（RSS），PostgreSQL的shared_buffers属于共享内存段，默认不会被计入单进程RSS统计值，你当前配置的7680MB共享内存已经正常分配使用，不存在内存闲置的问题，你观测到的不足500MB仅为PostgreSQL后台进程、业务连接的私有内存占用。

性能优化方案 #

针对1.5TB数据量的分析型数仓场景，结合你4核30GB内存的资源配额，可从以下方向调整优化，提升查询速度：

1. PostgreSQL配置调整 #

调整后的postgres.conf非默认参数参考如下：

max_connections = 30
# 数仓场景可将共享缓存调整到内存的30%-40%
shared_buffers = 10GB
# 保持为总内存的75%，用于优化器判断索引扫描收益
effective_cache_size = 23GB
# 调大维护内存，提升建索引、VACUUM的速度
maintenance_work_mem = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
# 若HPC使用SSD/并行存储，调低随机页成本，优化器会优先选择索引扫描
random_page_cost = 1.1
# SSD/并行存储可调高IO并发数，机械盘保持2即可
effective_io_concurrency = 100
# 调大单操作内存，避免排序、JOIN时走磁盘临时文件，可根据实际并发查询数调整
work_mem = 256MB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 4
# 4核场景可开3个并行工作进程，提升大查询扫描速度
max_parallel_workers_per_gather = 3
max_parallel_workers = 4
max_parallel_maintenance_workers = 3

2. 存储与部署优化 #

• 确保Singularity容器的数据目录挂载在HPC的高速并行存储上，避免使用计算节点本地慢盘，IO瓶颈是分析场景最常见的性能瓶颈
• 不要将WAL日志和数据文件放在同一块存储介质上，可进一步提升写入、查询的并发性能

3. 查询与数据模型优化 #

• 开启pg_stat_statements扩展收集慢查询日志，针对性优化高频慢SQL，收益远高于通用配置调整
• 对常用查询的过滤、关联字段建立合适的B树索引，大表可采用分区表结构，开启分区裁剪减少扫描数据量
• 定期执行ANALYZE更新表统计信息，保证查询优化器生成最优执行计划

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