跳转到主要内容

OLTP 模板

针对在线事务处理负载优化的 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
  • 当 pg_default_service_dest 为 pgbouncer 时，max_connections 设为 500
  • 当流量直连 PostgreSQL 时，max_connections 设为 1000
  • 可通过 pg_max_conn 参数覆盖

内存配置

OLTP 模板的内存分配策略：

参数 计算公式 说明
shared_buffers 内存 × pg_shared_buffer_ratio 默认比例 0.25
maintenance_work_mem shared_buffers × 25% 用于 VACUUM、CREATE INDEX
work_mem 64MB - 1GB 根据 shared_buffers/max_connections 计算
effective_cache_size 总内存 - shared_buffers 可用于缓存的预估内存

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 CRIT
max_connections 500-1000 500 500-1000
work_mem 64MB-1GB 64MB-8GB 64MB-1GB
并行查询 适度限制 激进启用 禁用
vacuum 激进度 保守 激进 保守
事务超时 10min 禁用 1min
慢查询阈值 100ms 1000ms 100ms

为什么选择 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）
  • 锁等待：锁等待时间、死锁次数
  • 复制延迟：从库延迟时间和字节数

参考资料