CentOS PostgreSQL如何调优

在 CentOS 上优化 PostgreSQL，通常从 系统层、数据库参数、SQL/索引、运维策略 四个方面入手。下面给你一套实用、可落地的调优指南（适用于 CentOS 7/8、Rocky / Alma 也类似）。

一、系统层调优（CentOS）

1. 内存与 Swap

free -h

• 不建议使用 Swap（除非内存实在不够）
• 如果必须：

vm.swappiness = 1

编辑：

vi /etc/sysctl.conf

vm.swappiness = 1

sysctl -p

2. 内核参数优化

# /etc/sysctl.conf
kernel.shmmax = 17179869184
kernel.shmall = 4194304
net.core.somaxconn = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1

sysctl -p

3. 文件描述符

ulimit -n

建议 ≥ 65535

编辑：

vi /etc/security/limits.conf

postgres soft nofile 65535
postgres hard nofile 65535

4. 磁盘与文件系统

• 使用 XFS / ext4
• 挂载参数（示例）：

noatime,nodiratime,data=writeback

二、PostgreSQL 核心参数调优（postgresql.conf）

假设服务器内存 16GB，生产环境示例

1. 内存相关（最重要）

# 内存配置
shared_buffers = 4GB              # 25% 内存
work_mem = 32MB                   # 复杂查询排序
maintenance_work_mem = 512MB      # VACUUM / CREATE INDEX

# 缓存
effective_cache_size = 12GB       # 留给 OS + PG 的缓存

2. WAL 与写入优化

wal_buffers = 16MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 512MB

3. 连接数

max_connections = 200

⚠️ 不要盲目调大

• 高并发建议用 pgbouncer

4. 日志与监控

logging_collector = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = off
log_disconnections = off

5. 大页（可选）

cat /proc/meminfo | grep Huge

如启用：

huge_pages = try

三、索引与 SQL 优化（最关键）

1. 查看慢 SQL

SELECT * FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

需开启：

shared_preload_libraries = 'pg_stat_statements'

2. 索引优化原则

✅ 适合加索引：

• WHERE
• JOIN
• ORDER BY
• GROUP BY

❌ 不适合：

• 低选择性字段（如 gender）
• 频繁 UPDATE 的小表

常见索引类型：

CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(created_at DESC);

3. 避免常见 SQL 问题

• 不要 SELECT *
• 避免 LIKE '%abc%'
• 避免函数包字段：

WHERE DATE(create_time) = '2024-01-01' ❌
WHERE create_time >= '2024-01-01' ❌

四、表与数据库维护

1. 定期 VACUUM & ANALYZE

VACUUM ANALYZE;

或自动：

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1

2. 大表分区（10GB+）

CREATE TABLE orders PARTITION BY RANGE (created_at);

五、连接池（强烈推荐）

pgbouncer 示例

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

六、快速调优模板（16GB 内存）

shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB
max_connections = 200
checkpoint_timeout = 15min
max_wal_size = 2GB

七、调优顺序（经验总结）

1. ✅ 先优化 SQL 和索引（收益最大）
2. ✅ 再调 PostgreSQL 参数
3. ✅ 最后调系统层
4. ✅ 高并发必须加连接池

八、如果你愿意，我可以帮你：

• ✅ 根据你 内存 / CPU / 并发量 给出精确参数
• ✅ 分析 pg_stat_statements 慢 SQL
• ✅ 针对 读写分离 / 高并发 / 大数据量 场景定制方案

你可以直接告诉我：

服务器内存多少？PostgreSQL 版本？主要用途（OLTP / 报表 / 日志）？