印刻数据库

外观

PostgreSQL参数优化 ​

PostgreSQL参数是影响数据库性能的关键因素，合理调整这些参数可以显著提高数据库的性能和稳定性。本文将详细介绍PostgreSQL的参数优化方法，包括核心参数、内存参数、I/O参数、并发参数等的优化配置和最佳实践，帮助DBA在实际生产环境中优化PostgreSQL参数，兼顾不同PostgreSQL版本的特性差异。

参数文件结构 ​

PostgreSQL的主要参数文件是postgresql.conf，位于数据目录中。该文件包含了PostgreSQL的所有配置参数，按照功能分为多个部分。PostgreSQL 12+对参数文件进行了优化，增加了更多的分类和说明：

  • 连接与认证
  • 资源使用
  • 预写式日志
  • 查询规划器
  • 垃圾回收
  • 客户端连接默认值
  • 统计信息收集
  • 自动清理
  • 客户端认证
  • 锁管理
  • 版本与平台兼容性
  • 错误报告与日志
  • 进程管理
  • 复制
  • 恢复
  • 监控
  • 预设选项

核心参数优化 ​

监听地址与端口 ​

ini
# postgresql.conf
# 配置监听地址，允许所有IP地址连接
listen_addresses = '*'

# 配置PostgreSQL端口
port = 5432

# 配置最大连接数，PostgreSQL 13+支持动态调整
# 根据系统内存和CPU资源调整，建议不超过500
max_connections = 200

内存参数 ​

内存参数是PostgreSQL性能优化的关键，合理配置内存参数可以减少磁盘I/O，提高查询性能。不同PostgreSQL版本对内存管理的优化有所不同。

shared_buffers ​

shared_buffers是PostgreSQL用于缓存数据块的内存区域：

ini
# postgresql.conf
# PostgreSQL 10-11：建议设置为系统内存的25%
# PostgreSQL 12+：建议根据存储类型调整
# NVMe SSD：15-25% of system memory
# SATA SSD：20-30% of system memory
# HDD：25-30% of system memory
shared_buffers = 32GB  # 128GB内存的系统，NVMe SSD设置为32GB

work_mem ​

work_mem是每个查询操作的工作内存，根据系统内存和并发连接数调整：

ini
# postgresql.conf
# 计算公式：work_mem = (system_memory * 0.2) / (max_connections * max_parallel_workers_per_gather)
# PostgreSQL 12+支持基于查询类型的work_mem调整
work_mem = 64MB  # 每个操作的工作内存
maintenance_work_mem = 4GB  # 维护操作的内存，建议不超过16GB

effective_cache_size ​

effective_cache_size是PostgreSQL估计的操作系统缓存大小，影响查询计划的生成：

ini
# postgresql.conf
# PostgreSQL 10+建议设置为系统内存的50-75%
# 更高的值可以鼓励规划器使用索引扫描
# 128GB内存的系统设置为96GB
effective_cache_size = 96GB

I/O参数 ​

I/O参数直接影响PostgreSQL的I/O性能，合理调整这些参数可以提高数据库的I/O性能。PostgreSQL 14+优化了WAL写入，对I/O参数更加敏感。

wal_buffers ​

wal_buffers是WAL日志的缓冲区大小：

ini
# postgresql.conf
# PostgreSQL 10+建议设置为shared_buffers的1/4或自动调整
# 自动调整（推荐）：
wal_buffers = -1  # PostgreSQL 14+自动调整为shared_buffers的1/4，最大16MB
# 手动调整：
# wal_buffers = 8GB  # 32GB shared_buffers的系统设置为8GB

checkpoint_timeout与checkpoint_completion_target ​

checkpoint参数控制WAL检查点的行为，影响I/O峰值：

ini
# postgresql.conf
# PostgreSQL 12+优化了checkpoint机制，建议增大timeout
checkpoint_timeout = 15min  # 检查点之间的时间间隔，最大1h
checkpoint_completion_target = 0.9  # 检查点完成的目标时间比例
# PostgreSQL 14+新增参数，控制检查点I/O速率
# checkpoint_flush_after = 256kB  # 每写入256kB后主动刷新

random_page_cost与seq_page_cost ​

这些参数控制查询规划器对不同I/O操作的成本估计：

ini
# postgresql.conf
# 根据存储设备调整，PostgreSQL 13+对SSD/NVMe优化更好
# SSD/NVMe设备：
random_page_cost = 1.1  # 建议值：1.0-2.0
seq_page_cost = 1.0  # 建议值：0.8-1.2
# HDD设备：
# random_page_cost = 4.0
# seq_page_cost = 1.0

并发参数 ​

并发参数直接影响PostgreSQL的并发处理能力，合理调整这些参数可以提高数据库的并发性能。PostgreSQL 10+引入了并行查询，13+增强了并行查询能力。

并行查询参数 ​

ini
# postgresql.conf
# 最大工作进程数，建议等于CPU核心数
max_worker_processes = 16  # 16核心CPU设置为16

# 每个查询的最大并行工作进程数
# PostgreSQL 10-12：建议为CPU核心数的1/2
# PostgreSQL 13+：建议为CPU核心数的1/2-1/4
max_parallel_workers_per_gather = 8

# 最大并行工作进程数，建议等于CPU核心数
max_parallel_workers = 16

# PostgreSQL 15+：并行真空参数
max_parallel_maintenance_workers = 4  # 并行维护操作的最大工作进程数

查询优化参数 ​

规划器参数 ​

规划器参数直接影响查询计划的生成，合理调整这些参数可以提高查询计划的质量。PostgreSQL 12+对规划器进行了优化，增加了更多的智能决策。

规划器成本参数 ​

ini
# postgresql.conf
# 调整规划器成本参数，适应存储设备
# PostgreSQL 14+优化了规划器，对SSD/NVMe更友好
cpu_tuple_cost = 0.01  # 处理每个元组的成本
cpu_index_tuple_cost = 0.005  # 处理每个索引元组的成本
cpu_operator_cost = 0.0025  # 处理每个操作符的成本

# 禁用或启用特定的扫描方式（谨慎使用）
# 建议仅在特定查询场景下临时调整，不要全局禁用
enable_seqscan = on  # 允许顺序扫描，小表高效
enable_indexscan = on  # 允许索引扫描
enable_bitmapscan = on  # 允许位图扫描
enable_hashjoin = on  # 允许哈希连接

优化器统计信息 ​

优化器统计信息直接影响查询计划的生成，合理调整这些参数可以提高查询计划的质量。PostgreSQL 12+增强了统计信息收集。

ini
# postgresql.conf
# 默认统计信息收集目标，PostgreSQL 13+建议提高
default_statistics_target = 100  # 范围：1-10000，建议：100-500

# 自动分析的触发条件
autovacuum_analyze_scale_factor = 0.05  # 自动分析的缩放因子，建议：0.05-0.1
# 对于大表，可结合autovacuum_analyze_threshold使用

预写式日志（WAL）参数 ​

WAL参数直接影响数据库的可靠性和性能，合理调整这些参数可以平衡可靠性和性能。PostgreSQL 13+对WAL机制进行了优化，增加了并行WAL写入支持。

wal_level ​

wal_level控制WAL日志的详细程度：

ini
# postgresql.conf
# PostgreSQL 10+支持replica和logical级别
# replica：支持物理复制
# logical：支持逻辑复制
wal_level = replica  # 支持复制的WAL级别

synchronous_commit ​

synchronous_commit控制WAL日志的同步提交方式，影响事务的可靠性和性能：

ini
# postgresql.conf
# 可选值：on, remote_write, local, off
# on：最高可靠性，等待WAL写入磁盘
# remote_write：等待WAL写入远程存储
# local：只等待WAL写入本地缓冲区
# off：最低可靠性，最高性能
# PostgreSQL 14+优化了synchronous_commit的性能
synchronous_commit = on  # 生产环境建议on或remote_write

archive_mode ​

archive_mode控制是否启用WAL归档，用于PITR（时间点恢复）：

ini
# postgresql.conf
archive_mode = on  # 启用WAL归档
archive_command = 'cp %p /archive/%f'  # WAL归档命令，建议使用更可靠的归档方案
# PostgreSQL 13+支持archive_library，可使用外部库进行归档

wal_compression ​

wal_compression控制是否压缩WAL日志，PostgreSQL 14+优化了WAL压缩算法：

ini
# postgresql.conf
wal_compression = on  # 启用WAL压缩，PostgreSQL 14+建议启用

并行WAL写入 ​

PostgreSQL 13+支持并行WAL写入，可提高WAL写入性能：

ini
# postgresql.conf
# 启用并行WAL写入（PostgreSQL 13+）
# wal_init_zero = on  # 初始化WAL文件为零
# wal_recycle = on  # 回收WAL文件

自动清理参数 ​

自动清理参数控制PostgreSQL的自动清理进程（Autovacuum），用于回收死元组。PostgreSQL 12+增强了自动清理机制，提高了清理效率。

autovacuum ​

autovacuum控制是否启用自动清理：

ini
# postgresql.conf
autovacuum = on  # 生产环境建议启用

autovacuum_max_workers ​

autovacuum_max_workers是自动清理的最大工作进程数：

ini
# postgresql.conf
# 根据CPU核心数调整，建议：3-5
autovacuum_max_workers = 5  # 自动清理的最大工作进程数

autovacuum_naptime ​

autovacuum_naptime是自动清理的间隔时间：

ini
# postgresql.conf
# PostgreSQL 12+建议缩短间隔，及时清理
autovacuum_naptime = 1min  # 自动清理的间隔时间，范围：10s-1h

autovacuum_vacuum_scale_factor ​

autovacuum_vacuum_scale_factor是自动清理的缩放因子：

ini
# postgresql.conf
# PostgreSQL 12+建议降低，及时回收死元组
autovacuum_vacuum_scale_factor = 0.05  # 自动清理的缩放因子，建议：0.05-0.1
# 结合autovacuum_vacuum_threshold使用
autovacuum_vacuum_threshold = 50  # 自动清理的阈值

日志参数 ​

日志参数控制PostgreSQL的日志记录方式，合理调整这些参数可以提高数据库的可维护性和故障排查能力。PostgreSQL 12+增强了日志功能，提供了更多的日志格式和配置选项。

日志收集配置 ​

ini
# postgresql.conf
# 日志输出目标，PostgreSQL 12+支持多目标
log_destination = 'stderr'  # 日志输出目标
logging_collector = on  # 启用日志收集器
log_directory = 'pg_log'  # 日志目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # 日志文件名

慢查询日志 ​

慢查询日志用于记录执行时间超过指定值的语句，PostgreSQL 13+增强了慢查询日志功能：

ini
# postgresql.conf
# 记录执行时间超过指定值的语句，单位：毫秒
# PostgreSQL 14+支持log_min_duration_sample参数，可采样记录
log_min_duration_statement = 1000  # 记录执行时间超过1秒的语句
# 记录所有语句，用于调试
# log_statement = 'all'  # 可选值：none, ddl, mod, all

日志行前缀 ​

日志行前缀控制日志行的格式，包含时间、进程ID、用户、数据库等信息：

ini
# postgresql.conf
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  # 日志行前缀

复制参数 ​

复制参数控制PostgreSQL的复制行为，合理调整这些参数可以提高复制的可靠性和性能。PostgreSQL 12+增强了复制功能，增加了更多的复制监控指标。

主库复制参数 ​

ini
# postgresql.conf
# 最大WAL发送进程数，根据从库数量调整
max_wal_senders = 10  # 建议：10-20

# 保留的WAL日志大小，防止从库跟不上
# PostgreSQL 13+建议使用wal_keep_size替代wal_keep_segments
wal_keep_size = 10GB  # 保留的WAL日志大小，建议：10GB-100GB

# 复制超时参数
wal_sender_timeout = 60s  # WAL发送超时

从库复制参数 ​

ini
# postgresql.conf
# 允许热备库查询
hot_standby = on  # 允许热备库查询

# 备库延迟的最大时间
max_standby_streaming_delay = 30s  # 建议：30s-5min

# 备库应用WAL的最大延迟
hot_standby_feedback = on  # 启用热备反馈，防止主库清理备库需要的WAL

JIT编译参数 ​

PostgreSQL 12+引入了JIT（Just-In-Time）编译功能，可以加速复杂查询的执行。

ini
# postgresql.conf
# 启用JIT编译，PostgreSQL 13+优化了JIT性能
jit = on  # 建议在OLAP场景启用

# JIT编译的触发阈值，单位：成本
jit_above_cost = 100000  # 建议：100000-500000

# JIT内联的触发阈值
jit_inline_above_cost = 500000  # 建议：500000-1000000

# 启用JIT优化
jit_optimize_above_cost = 500000

参数调优最佳实践 ​

针对不同场景的参数调优 ​

OLTP场景 ​

OLTP场景以高并发、小事务为主，PostgreSQL 14+对OLTP场景进行了优化：

  • 调整shared_buffers为系统内存的15-25%
  • 降低work_mem，避免内存耗尽
  • 优化checkpoint参数，减少I/O峰值
  • 启用WAL压缩
  • 调整自动清理参数，及时回收死元组
  • 启用parallel vacuum，加速清理

OLAP场景 ​

OLAP场景以复杂查询、大数据量为主，PostgreSQL 13+增强了OLAP场景的性能：

  • 调整shared_buffers为系统内存的20-30%
  • 增加work_mem，提高复杂查询性能
  • 启用并行查询，增加并行工作进程数
  • 调整random_page_cost和seq_page_cost，适应存储设备
  • 提高default_statistics_target，提高统计信息质量
  • 启用JIT编译，加速复杂查询

混合场景 ​

混合场景需要平衡OLTP和OLAP的需求：

  • 平衡shared_buffers和work_mem的配置
  • 根据实际负载调整并行查询参数
  • 启用自动清理和自动分析
  • 结合资源组（PostgreSQL 10+）对不同查询进行资源隔离
  • 监控系统性能，及时调整参数

参数调优步骤 ​

  1. 监控当前性能：使用pg_stat_statements、pg_stat_database等视图监控性能
  2. 识别性能瓶颈：分析慢查询、I/O等待、锁等待等
  3. 调整相关参数：根据瓶颈调整相应的参数，每次只调整一个参数
  4. 测试调优效果：使用pgbench等工具测试调优效果
  5. 持续监控和调整：建立监控体系，持续优化参数

参数调优注意事项 ​

  • 理解参数含义：不要盲目调整参数，先理解参数的影响
  • 版本差异：不同PostgreSQL版本对参数的默认值和行为有所不同
  • 测试验证：在测试环境验证调优效果后再应用到生产环境
  • 记录变更：记录参数调优前后的性能指标，便于比较
  • 动态调整：PostgreSQL 12+支持部分参数的动态调整，无需重启

案例分析 ​

OLTP场景参数调优 ​

问题描述 ​

电商系统的PostgreSQL数据库出现性能瓶颈，CPU使用率高，查询响应时间长，I/O延迟高。

系统配置 ​

  • CPU：16核心，3.2GHz
  • 内存：64GB
  • 存储：SATA SSD
  • 网络：1GbE
  • 操作系统：CentOS 7
  • PostgreSQL版本：14

调优方案 ​

  1. 调整内存参数：
ini
# postgresql.conf
shared_buffers = 16GB  # 系统内存的25%
work_mem = 64MB  # 根据并发连接数调整
maintenance_work_mem = 2GB
effective_cache_size = 48GB  # 系统内存的75%
  1. 调整I/O参数：
ini
# postgresql.conf
wal_buffers = -1  # 自动调整
checkpoint_timeout = 15min  # 延长检查点间隔
checkpoint_completion_target = 0.9
random_page_cost = 1.1  # SATA SSD设置为1.1
seq_page_cost = 1.0
  1. 调整并发参数：
ini
# postgresql.conf
max_connections = 200
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
  1. 调整自动清理参数：
ini
# postgresql.conf
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 1min
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05

调优效果 ​

  • 查询响应时间从100ms减少到10ms
  • 并发处理能力从1000 TPS提高到5000 TPS
  • CPU使用率从90%降低到40%
  • I/O延迟从5ms降低到0.5ms

OLAP场景参数调优 ​

问题描述 ​

数据分析系统的PostgreSQL数据库出现性能瓶颈，查询执行时间长，I/O吞吐量不足。

系统配置 ​

  • CPU：32核心，2.8GHz
  • 内存：128GB
  • 存储：NVMe SSD
  • 网络：10GbE
  • 操作系统：CentOS 8
  • PostgreSQL版本：15

调优方案 ​

  1. 调整内存参数：
ini
# postgresql.conf
shared_buffers = 32GB  # 系统内存的25%
work_mem = 256MB  # 提高工作内存
maintenance_work_mem = 4GB
effective_cache_size = 96GB  # 系统内存的75%
  1. 调整I/O参数：
ini
# postgresql.conf
wal_buffers = -1
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
random_page_cost = 1.0  # NVMe SSD设置为1.0
seq_page_cost = 1.0
  1. 调整并行查询参数：
ini
# postgresql.conf
max_worker_processes = 32
max_parallel_workers_per_gather = 16
max_parallel_workers = 32
max_parallel_maintenance_workers = 4  # 并行真空
  1. 调整JIT编译参数：
ini
# postgresql.conf
jit = on
jit_above_cost = 100000
jit_inline_above_cost = 500000

调优效果 ​

  • 查询执行时间从30秒减少到3秒
  • 数据加载速度从100MB/s提高到1GB/s
  • I/O吞吐量从500MB/s提高到5GB/s
  • 并发查询能力从10提高到100

总结 ​

PostgreSQL参数优化是数据库性能调优的重要组成部分，合理调整参数可以显著提高数据库的性能和稳定性。本文介绍了PostgreSQL的核心参数、内存参数、I/O参数、并发参数、查询优化参数、WAL参数、自动清理参数和日志参数的优化配置和最佳实践，包括：

  1. 核心参数：监听地址、端口、最大连接数
  2. 内存参数：shared_buffers、work_mem、effective_cache_size
  3. I/O参数：wal_buffers、checkpoint参数、random_page_cost
  4. 并发参数：并行查询相关参数
  5. 查询优化参数：规划器参数、统计信息收集
  6. WAL参数：wal_level、synchronous_commit、archive_mode
  7. 自动清理参数：autovacuum相关参数
  8. 日志参数：慢查询日志、日志格式
  9. 复制参数：主从复制相关参数
  10. JIT编译参数：JIT相关配置

通过结合PostgreSQL版本特性，针对不同场景进行参数调优，并建立持续监控和优化机制，DBA可以不断提高PostgreSQL数据库的性能，满足业务需求。