OLTP 模板
针对在线事务处理负载优化的 PostgreSQL 配置模板
针对在线事务处理负载优化的 PostgreSQL 配置模板
oltp.yml 是 Pigsty 的默认配置模板，针对 在线事务处理（OLTP）负载进行了优化。适用于 4-128 核 CPU 的服务器，特点是高并发连接、低延迟响应、高事务吞吐量。
建议同时使用 node_tune = oltp 进行操作系统级别的配套调优。
适用场景
OLTP 模板适用于以下场景：
- 电商系统：订单处理、库存管理、用户交易
- 社交应用：用户动态、消息推送、关注关系
- 游戏后端：玩家数据、排行榜、游戏状态
- SaaS 应用：多租户业务系统
- Web 应用：常规的 CRUD 操作密集型应用
特征负载：
- 大量短事务（毫秒级）
- 高并发连接（数百到数千）
- 读写比例通常在 7:3 到 9:1
- 对延迟敏感，要求快速响应
- 数据一致性要求高
使用方法
oltp.yml 是默认模板，无需显式指定：
pg-oltp:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
vars:
pg_cluster: pg-oltp
# pg_conf: oltp.yml # PostgreSQL 配置模板（默认值）
# node_tune: oltp # 操作系统调优模板（默认值）
或显式指定：
pg-oltp:
vars:
pg_conf: oltp.yml # PostgreSQL 配置模板
node_tune: oltp # 操作系统调优模板
参数详解
连接管理
max_connections: 500/1000 # 取决于是否使用 pgbouncer
superuser_reserved_connections: 10
内存配置
OLTP 模板的内存分配策略：
work_mem 计算逻辑：
work_mem = min(max(shared_buffers / max_connections, 64MB), 1GB)
这确保每个连接有足够的排序/哈希内存，但不会过度分配。
并行查询
OLTP 模板对并行查询做了适度限制，以避免并行查询抢占过多资源影响其他事务：
max_worker_processes: max(cpu + 16, 24)
max_parallel_workers: 50% × cpu (最小2)
max_parallel_workers_per_gather: 20% × cpu (2-8)
max_parallel_maintenance_workers: 33% × cpu (最小2)
同时提高了并行查询的成本估算，让优化器倾向于串行执行：
parallel_setup_cost: 2000 # 默认值 1000 的两倍
parallel_tuple_cost: 0.2 # 默认值 0.1 的两倍
min_parallel_table_scan_size: 32MB # 默认值 8MB 的四倍，倾向于不使用并行扫描
min_parallel_index_scan_size: 2MB # 默认值 512kB 的四倍，倾向于不使用并行扫描
WAL 配置
min_wal_size: 磁盘/20 (实际最大100GB)
max_wal_size: 磁盘/5 (实际最大400GB)
max_slot_wal_keep_size: 磁盘×3/10 (实际最大600GB)
wal_buffers: 16MB
wal_writer_delay: 20ms
wal_writer_flush_after: 1MB
commit_delay: 20
commit_siblings: 10
checkpoint_timeout: 15min
checkpoint_completion_target: 0.80
这些设置平衡了数据安全性和写入性能。
Vacuum 配置
vacuum_cost_delay: 20ms # 每轮 vacuum 后休眠
vacuum_cost_limit: 2000 # 每轮 vacuum 的代价上限
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08 # 8% 表变化触发 vacuum
autovacuum_analyze_scale_factor: 0.04 # 4% 表变化触发 analyze
autovacuum_freeze_max_age: 1000000000
OLTP 模板使用保守的 vacuum 设置，避免 vacuum 操作影响在线事务性能。
查询优化
random_page_cost: 1.1 # SSD 优化
effective_io_concurrency: 200 # SSD 并发 IO
default_statistics_target: 400 # 统计信息精度
这些设置让优化器能够生成更好的查询计划。
日志与监控
log_min_duration_statement: 100 # 记录超过 100ms 的慢查询
log_statement: ddl # 记录 DDL 语句
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024 # 记录超过 1MB 的临时文件
log_autovacuum_min_duration: 1s
track_io_timing: on
track_functions: all
track_activity_query_size: 8192
客户端超时
deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 10min
10 分钟的空闲事务超时可以防止长时间持有锁的僵尸事务。
扩展配置
shared_preload_libraries: 'pg_stat_statements, auto_explain'
# auto_explain
auto_explain.log_min_duration: 1s
auto_explain.log_analyze: on
auto_explain.log_verbose: on
auto_explain.log_timing: on
auto_explain.log_nested_statements: true
# pg_stat_statements
pg_stat_statements.max: 10000
pg_stat_statements.track: all
pg_stat_statements.track_utility: off
pg_stat_statements.track_planning: off
与其他模板的对比
为什么选择 OLTP 而非 OLAP？
- 您的查询大多数是简单的点查和范围查询
- 事务响应时间要求在毫秒级
- 有大量并发连接
- 不需要执行复杂的分析查询
为什么选择 OLTP 而非 CRIT？
- 可以接受极小概率的数据丢失（异步复制）
- 不需要完整的审计日志
- 希望获得更好的写入性能
性能调优建议
连接池
对于高并发场景，强烈建议使用 PgBouncer 连接池：
pg-oltp:
vars:
pg_default_service_dest: pgbouncer # 默认值
pgbouncer_poolmode: transaction # 事务级池化
只读分离
使用只读从库分担读取负载：
pg-oltp:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
10.10.10.13: { pg_seq: 3, pg_role: replica }
监控指标
关注以下监控指标：
- 连接数：活跃连接数、等待连接数
- 事务率：TPS、提交/回滚比例
- 响应时间：查询延迟百分位（p50/p95/p99）
- 锁等待：锁等待时间、死锁次数
- 复制延迟：从库延迟时间和字节数
参考资料