在 CentOS 上调优 PostgreSQL,通常从 操作系统层、PostgreSQL 参数、硬件与文件系统、监控与压测 四个方面入手。下面给你一套比较实用、可落地的调优思路(以 CentOS 7/8 + PostgreSQL 12/13/14 为例)。
PostgreSQL 官方不建议使用 THP:
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
永久生效:
# /etc/rc.d/rc.local
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
chmod +x /etc/rc.d/rc.local
# 共享内存
kernel.shmmax = 68719476736
kernel.shmall = 16777216
# 文件句柄
fs.file-max = 2097152
# 网络
net.core.somaxconn = 1024
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_time = 60
# 交换倾向(减少 swap)
vm.swappiness = 10
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
生效:
sysctl -p
# /etc/security/limits.conf
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 65536
postgres hard nproc 65536
defaults,noatime,nodiratime,discard
示例:
mount -o remount,noatime /data
配置文件位置:
/var/lib/pgsql/<version>/data/postgresql.conf
shared_buffers = 25% ~ 40% 内存
# 例如 64G 内存
shared_buffers = 16GB
work_mem = 16MB ~ 64MB # 复杂查询多可适当调大
maintenance_work_mem = 1GB ~ 2GB
effective_cache_size = 50% ~ 75% 内存
effective_cache_size = 48GB
wal_buffers = 16MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
max_connections = 200 ~ 500
# 高并发建议配合连接池(pgbouncer)
synchronous_commit = on # 性能与安全的折中
random_page_cost = 1.1 # SSD 场景
seq_page_cost = 1.0
logging_collector = on
log_min_duration_statement = 500ms
log_line_prefix = '%m %u %d %p'
log_checkpoints = on
log_connections = on
log_disconnections = on
VACUUM ANALYZE;
重要表:
VACUUM (ANALYZE, VERBOSE) your_table;
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
PostgreSQL 连接成本高:
yum install pgbouncer
模式:
pg_stat_statementspg_topiostat, vmstat, sar启用:
shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;
pgbench -i -s 100 testdb
pgbench -c 50 -j 8 -T 300 testdb
| 场景 | 建议 |
|---|---|
| 读多写少 | 增大 shared_buffers |
| 写密集 | 增大 WAL / checkpoint |
| 高并发 | pgbouncer |
| 分析型 | 增大 work_mem |
如果你愿意,我可以:
pgbench 或慢 SQL直接告诉我你的服务器配置和业务类型即可。