跳转到主要内容

参数优化

调整 postgres 参数
调整 postgres 参数

Pigsty 默认提供了四套场景化参数模板，可以通过 pg_conf 参数指定并使用。

  • tiny.yml：为小节点、虚拟机、小型演示优化（1-8核，1-16GB）
  • oltp.yml：为OLTP工作负载和延迟敏感应用优化（4C8GB+）（默认模板）
  • olap.yml：为OLAP工作负载和吞吐量优化（4C8G+）
  • crit.yml：为数据一致性和关键应用优化（4C8G+）

Pigsty 会针对这四种默认场景，采取不同的参数优化策略，如下所示：

内存参数调整

Pigsty 默认会检测系统的内存大小，并以此为依据设定最大连接数量与内存相关参数。

默认情况下，Pigsty 使用 25% 的内存作为 PostgreSQL 共享缓冲区，剩余的 75% 作为操作系统缓存。

默认情况下，如果用户没有设置一个 pg_max_conn 最大连接数，Pigsty 会根据以下规则使用默认值：

  • oltp: 500 (pgbouncer) / 1000 (postgres)
  • crit: 500 (pgbouncer) / 1000 (postgres)
  • tiny: 300
  • olap: 300

其中对于 OLTP 与 CRIT 模版来说，如果服务没有指向 pgbouncer 连接池，而是直接连接 postgres 数据库，最大连接会翻倍至 1000 条。

决定最大连接数后，work_mem 会根据共享内存数量 / 最大连接数计算得到，并限定在 64MB ~ 1GB 的范围内。

{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}

CPU参数调整

在 PostgreSQL 中，有 4 个与并行查询相关的重要参数，Pigsty 会自动根据当前系统的 CPU 核数进行参数优化。 在所有策略中，总并行进程数量（总预算）通常设置为 CPU 核数 + 8，且保底为 16 个，从而为逻辑复制与扩展预留足够的后台 worker 数量，OLAP 和 TINY 模板根据场景略有不同。

OLTP 设置逻辑 范围限制
max_worker_processes max(100% CPU + 8, 16) 核数 + 4，保底 12，
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather min(max(ceil(20% CPU), 2),8) 1/5 CPU 下取整，最少两个，最多 8 个
OLAP 设置逻辑 范围限制
max_worker_processes max(100% CPU + 12, 20) 核数 + 12，保底 20，
max_parallel_workers max(ceil(80% CPU, 2)) 4/5 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather max(floor(50% CPU), 2) 1/2 CPU 上取整，最少两个
CRIT 设置逻辑 范围限制
max_worker_processes max(100% CPU + 8, 16) 核数 + 8，保底 16，
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather 0, 按需启用
TINY 设置逻辑 范围限制
max_worker_processes max(100% CPU + 4, 12) 核数 + 4，保底 12，
max_parallel_workers max(ceil(50% CPU) 1) 50% CPU 下取整，最少1个
max_parallel_maintenance_workers max(ceil(33% CPU), 1) 33% CPU 下取整，最少1个
max_parallel_workers_per_gather 0, 按需启用

请注意，CRIT 和 TINY 模板直接通过设置 max_parallel_workers_per_gather = 0 关闭了并行查询。 用户可以按需在需要时设置此参数以启用并行查询。

OLTP 和 CRIT 模板都额外设置了以下参数，将并行查询的 Cost x 2，以降低使用并行查询的倾向。

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 16MB  # double from 8MB to increase parallel cost
min_parallel_index_scan_size: 1024  # double from 512 to increase parallel cost

请注意 max_worker_processes 参数的调整必须在重启后才能生效。此外，当从库的本参数配置值高于主库时，从库将无法启动。 此参数必须通过 patroni 配置管理进行调整，该参数由 Patroni 管理，用于确保主从配置一致，避免在故障切换时新从库无法启动。

存储空间参数

Pigsty 默认检测 /data/postgres 主数据目录所在磁盘的总空间，并以此作为依据指定下列参数：

min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB
  • temp_file_limit 默认为磁盘空间的 5%，封顶不超过 200GB。
  • min_wal_size 默认为磁盘空间的 5%，封顶不超过 200GB。
  • max_wal_size 默认为磁盘空间的 20%，封顶不超过 2TB。
  • max_slot_wal_keep_size 默认为磁盘空间的 30%，封顶不超过 3TB。

作为特例， OLAP 模板允许 20% 的 temp_file_limit ，封顶不超过 2TB