PostgreSQL如何优化CentOS

在CentOS上优化PostgreSQL，通常从系统层、数据库参数、硬件与存储、SQL与索引、监控与维护几个方面入手。下面给你一套实战型优化清单，适合生产环境。

一、CentOS 系统层优化

1. 关闭透明大页（THP）

PostgreSQL 不推荐 THP，会导致延迟抖动。

# 查看
cat /sys/kernel/mm/transparent_hugepage/enabled

# 临时关闭
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 永久关闭（CentOS 7/8）
vim /etc/default/grub
# 在 GRUB_CMDLINE_LINUX 后加
transparent_hugepage=never

grub2-mkconfig -o /boot/grub2/grub.cfg
reboot

2. 调整内核参数（sysctl）

vim /etc/sysctl.conf

常用优化项：

vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
vm.overcommit_memory = 2
kernel.shmmax = 17179869184
kernel.shmall = 4194304
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535

生效：

sysctl -p

3. 文件描述符限制

vim /etc/security/limits.conf

postgres soft nofile 65535
postgres hard nofile 65535

4. 磁盘调度器（SSD 推荐）

cat /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler

二、PostgreSQL 参数优化（postgresql.conf）

1. 内存相关（最重要）

shared_buffers = 25% ~ 40% 内存
work_mem = 4MB ~ 64MB（复杂查询可调大）
maintenance_work_mem = 512MB ~ 2GB
effective_cache_size = 50% ~ 75% 内存

示例（32GB 内存服务器）：

shared_buffers = 8GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB

2. WAL 与写入优化

wal_buffers = 16MB
checkpoint_timeout = 15min ~ 30min
max_wal_size = 4GB
min_wal_size = 1GB
synchronous_commit = off   # 非金融场景可关闭

3. 连接与并发

max_connections = 200 ~ 500
superuser_reserved_connections = 10

⚠️ 高并发建议配合 pgbouncer

4. 统计与执行计划

random_page_cost = 1.1     # SSD
seq_page_cost = 1.0
default_statistics_target = 200

三、存储与硬件优化

1. 磁盘建议

✅ SSD / NVMe
✅ XFS 文件系统
✅ 数据盘与系统盘分离

mkfs.xfs /dev/sdb

2. 数据目录分离

/var/lib/pgsql/data
/var/lib/pgsql/wal

四、SQL 与索引优化

1. 索引优化

B-tree：等值、范围
GIN：JSONB、数组
BRIN：时序数据
避免过多索引（影响写入）

CREATE INDEX idx_order_time ON orders(created_at);

2. 分析执行计划

EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 1;

关注：

Seq Scan（全表扫描）
实际执行时间
行数估算是否准确

3. 统计信息更新

ANALYZE orders;

或自动：

autovacuum = on

五、日常维护优化

1. 自动 Vacuum

autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

2. 定期重建索引（大表）

REINDEX INDEX idx_name;

六、监控工具（强烈推荐）

pg_stat_statements（慢查询）
pg_top / htop
Prometheus + Grafana
pgBadger（日志分析）

启用：

shared_preload_libraries = 'pg_stat_statements'

七、典型优化顺序（实战）

系统参数（THP、sysctl）
内存参数（shared_buffers、work_mem）
WAL & Checkpoint
索引 + SQL
连接池（pgbouncer）
监控 + 持续调优

八、我可以继续帮你做的事

你可以直接告诉我：

✅ PostgreSQL 版本
✅ CentOS 版本
✅ 内存 / CPU / 磁盘类型
✅ 主要业务（OLTP / 报表 / 时序）

我可以给你一份完全定制的 postgresql.conf 配置。