如何根据系统配置确定Patroni集群的正确配置？

环境背景 #

服务器配置：124GB内存、31vCPU、1TB SSD，为纯Patroni集群节点，无其他运行实例；当前日访问量约28万次，使用ChatGPT生成的配置直接部署后出现共享内存耗尽错误。

当前Patroni配置 #

loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    checkpoint_completion_target: 0.9
    checkpoint_timeout: 30min
    citus.node_conninfo: sslrootcert=/etc/ssl/certs/ssl-cert-snakeoil.pem sslkey=/etc/ssl/private/ssl-cert-snakeoil.key sslcert=/etc/ssl/certs/ssl-cert-snakeoil.pem sslmode=verify-ca
    effective_cache_size: 96GB
    effective_io_concurrency: 200
    maintenance_work_mem: 2GB
    max_connections: 3000
    max_parallel_workers_per_gather: 8
    random_page_cost: 1.1
    shared_buffers: 32GB
    ssl: 'on'
    ssl_ca_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
    ssl_cert_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
    ssl_key_file: /etc/ssl/private/ssl-cert-snakeoil.key
    synchronous_commit: false
    wal_buffers: 32MB
    wal_level: replica
    work_mem: 64MB
  pg_hba:
  - local all all trust
  - hostssl replication replicator all md5 clientcert=verify-ca
  - hostssl all all all md5 clientcert=verify-ca
  use_pg_rewind: true
retry_timeout: 10
synchronous_mode: quorum
ttl: 30

报错信息 #

1|index  | PrismaClientKnownRequestError:
1|index  | Invalid `prisma.$queryRawUnsafe()` invocation:
1|index  | Raw query failed. Code: `53100`. Message: `ERROR: could not resize shared memory segment "/PostgreSQL.2217184920" to 610912 bytes: No space left on device`
1|index  |     at _n.handleRequestError (/home/smsflash/smsflash__node/.build/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:7749)
1|index  |     at _n.handleAndLogRequestError (/home/smsflash/smsflash__node/.build/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:7057)
1|index  |     at _n.request (/home/smsflash/smsflash__node/.build/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:6741)
1|index  |     at async l (/home/smsflash/smsflash__node/.build/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:130:9355) {
1|index  |   code: 'P2010',
1|index  |   clientVersion: '5.18.0',
1|index  |   meta: {
1|index  |     code: '53100',
1|index  |     message: 'ERROR: could not resize shared memory segment "/PostgreSQL.2217184920" to 610912 bytes: No space left on device'
1|index  |   }
1|index  | }

错误本质为共享内存资源耗尽。

问题排查与修复 #

1. 系统级共享内存限制调整 #

PostgreSQL的共享内存使用受操作系统内核参数约束，需确保参数满足集群需求：

• 执行命令查看当前参数：

  sysctl -a | grep shm
  

• 修改/etc/sysctl.conf文件，添加/更新以下配置：

  kernel.shmmax = 68719476736  # 64GB，大于shared_buffers的32GB，建议设为物理内存的50%
  kernel.shmall = 16777216     # 对应64GB（每页4KB）
  kernel.shmmni = 4096         # 共享内存段最大数量，满足多连接场景
  

• 生效配置：

  sysctl -p
  

2. PostgreSQL内存参数核心优化 #

当前配置的max_connections=3000+work_mem=64MB会导致内存过载（单连接最多可占用数倍work_mem，3000连接理论内存占用远超124GB），需调整：

• max_connections：降至800以内，配合Pgbouncer连接池复用连接（PostgreSQL进程模型不适合高并发直连）
• work_mem：降至32MB，避免单连接占用过多内存
• maintenance_work_mem：提升至4GB，给维护操作（vacuum、建索引）分配足够资源
• 新增参数控制全局并行任务与临时内存：

  max_parallel_workers: 16        # 全局并行任务数，设为CPU核心数的50%
  autovacuum_work_mem: 2GB        # 单独控制autovacuum内存，避免影响业务
  temp_buffers: 1GB              # 限制临时表内存占用
  

适配124GB/31vCPU的Patroni优化配置 #

loop_wait: 10
maximum_lag_on_failover: 1048576
retry_timeout: 10
synchronous_mode: quorum
ttl: 30
postgresql:
  parameters:
    checkpoint_completion_target: 0.9
    checkpoint_timeout: 30min
    citus.node_conninfo: sslrootcert=/etc/ssl/certs/ssl-cert-snakeoil.pem sslkey=/etc/ssl/private/ssl-cert-snakeoil.key sslcert=/etc/ssl/certs/ssl-cert-snakeoil.pem sslmode=verify-ca
    effective_cache_size: 96GB
    effective_io_concurrency: 200
    maintenance_work_mem: 4GB
    max_connections: 800
    max_parallel_workers: 16
    max_parallel_workers_per_gather: 8
    random_page_cost: 1.1
    shared_buffers: 32GB
    ssl: 'on'
    ssl_ca_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
    ssl_cert_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
    ssl_key_file: /etc/ssl/private/ssl-cert-snakeoil.key
    synchronous_commit: false
    wal_buffers: 32MB
    wal_level: replica
    work_mem: 32MB
    autovacuum_work_mem: 2GB
    temp_buffers: 1GB
  pg_hba:
  - local all all trust
  - hostssl replication replicator all md5 clientcert=verify-ca
  - hostssl all all all md5 clientcert=verify-ca
  use_pg_rewind: true

服务器流量上限评估与配置原则 #

流量上限判断维度 #

1. 请求复杂度：简单查询（单表CRUD）可支撑更高并发，复杂聚合/关联查询会快速耗尽CPU/内存
2. 资源瓶颈：监控CPU使用率（不应持续超80%）、内存使用率（预留10-20GB给OS）、磁盘IOPS（SSD可支撑数万IOPS）
3. 连接池效率：使用Pgbouncer后，可支撑数倍于max_connections的并发请求

配置核心原则 #

1. 内存总量控制：PostgreSQL总内存占用不超过物理内存的80%，预留资源给系统缓存
2. 连接数管控：必须用连接池，max_connections设为500-1000，避免进程模型内存过载
3. SSD适配：保持random_page_cost=1.1、effective_io_concurrency=200，延长checkpoint间隔降低IO波动
4. 一致性平衡：synchronous_mode=quorum兼顾集群一致性与性能，业务强一致需求可改为on

内容的提问来源于stack exchange，提问作者dotsinspace