pg参数之 max_worker_processes

郑全

在 PostgreSQL 中，`max_worker_processes` 是一个配置参数，用于指定 PostgreSQL 实例中允许的最大工作进程（worker processes）数量。工作进程是后台任务或辅助进程，支持并行查询、逻辑复制、后台清理等任务。

### `max_worker_processes` 参数详解

- **参数作用**: 该参数决定了数据库中最大并发工作进程的数量。工作进程广泛应用于各种任务，如并行查询、逻辑复制工作者、以及特定扩展所使用的后台进程。

- **默认值**: 通常是 8，但可以根据需要进行调整。

- **最大值**: 理论上可以设置得非常高，但实际上会受到系统资源（如 CPU 和内存）的限制。

### 配置示例
你可以在 `postgresql.conf` 文件中设置 `max_worker_processes`：

```plaintext
max_worker_processes = 16
```

或者通过 SQL 动态修改（需要超级用户权限）：

```sql
ALTER SYSTEM SET max_worker_processes TO 16;
```

更改此参数后，必须重启 PostgreSQL 服务器以使其生效。

### 使用场景
1. **并行查询**: 并行查询依赖于工作进程来分担计算任务。如果你的系统运行许多并行查询，可以提高这个值以支持更多并行工作进程。

2. **逻辑复制**: 每个逻辑复制订阅通常需要一个工作进程，因此在使用大量逻辑复制订阅时需要增加这个值。

3. **扩展**: 一些 PostgreSQL 扩展（如背景作业调度器）可能会启动自己的工作进程，因此也会消耗这些资源。

### 关联参数
- **`max_parallel_workers`**: 控制整个数据库实例中允许的并行工作进程数量。此参数受限于 `max_worker_processes`。
- **`max_parallel_workers_per_gather`**: 控制每个并行查询中可以使用的工作进程数。

### 如何选择合适的值
- 该参数应根据你的数据库实例中需要支持的并行查询、逻辑复制和其他工作负载来设置。
- 如果系统资源足够且有许多并行任务，可以增加此参数值以提高性能。
- 需要注意，增加 `max_worker_processes` 会占用更多的系统资源，可能对系统稳定性产生影响，因此需要根据实际负载进行调优。

### 注意事项
- 虽然可以设置较大的值，但如果系统中没有足够的资源，可能会导致性能下降或资源不足的情况。
- 如果有大量的并行任务但未合理设置此参数，可能会导致查询性能不佳，因为并行任务的工作进程数会受到限制。

通过合理设置 `max_worker_processes`，可以在并行查询和逻辑复制等任务中充分利用系统资源，提升 PostgreSQL 的性能。

郑全

在 PostgreSQL 中，`max_parallel_workers` 是一个配置参数，用于控制数据库实例中所有并行查询允许使用的最大工作进程数量。它决定了在数据库中可以同时运行的并行工作进程（parallel workers）的数量上限。

### `max_parallel_workers` 参数详解

- **参数作用**: 该参数限制了整个数据库实例中可以使用的并行工作进程的总数。这些工作进程用于加速查询的执行，例如在进行大表的扫描、聚合和排序时，通过多个 CPU 核心并行处理任务。

- **默认值**: 通常是 8。

- **可配置范围**: 从 0 到 `max_worker_processes`。`max_parallel_workers` 的值不能超过 `max_worker_processes`，因为并行工作进程是工作进程的一部分。

### 配置示例
你可以在 `postgresql.conf` 文件中设置 `max_parallel_workers`：

```plaintext
max_parallel_workers = 16
```

也可以通过 SQL 动态修改（需要超级用户权限）：

```sql
ALTER SYSTEM SET max_parallel_workers TO 16;
```

更改此参数后，必须重启 PostgreSQL 服务器以使其生效。

### 使用场景
1. **并行查询**: 并行查询依赖工作进程来执行查询的不同部分。`max_parallel_workers` 限制了整个数据库实例中可以并行执行的工作进程总数，因此影响了并行查询的数量和效率。

2. **多用户环境**: 在多用户环境中，多个并行查询可能会同时运行。如果 `max_parallel_workers` 设得过低，查询的并行能力会受到限制，导致查询运行速度变慢。

3. **大型表操作**: 在进行大表扫描或复杂聚合时，较高的并行工作进程数可以显著缩短查询时间。

### 关联参数
- **`max_worker_processes`**: `max_parallel_workers` 受其限制，不能设置得比 `max_worker_processes` 大。
- **`max_parallel_workers_per_gather`**: 控制单个查询（尤其是 `Gather` 操作）中允许使用的并行工作进程数。
- **`max_parallel_maintenance_workers`**: 控制并行维护操作（如并行索引构建）中允许的最大工作进程数。

### 如何选择合适的值
- 对于多用户、多查询的环境，建议设置一个适中的值，以确保并行查询的总数不会过低影响性能。
- 如果有足够的 CPU 核心和内存资源，并且并行查询较多，可以考虑将此参数值设置得较高。
- 需要根据实际负载和硬件资源调优，避免设置过高导致系统资源争用过度，影响整体性能。

### 注意事项
- 如果系统中的并行查询较少，设置过高的 `max_parallel_workers` 可能不会带来明显的性能提升，但会消耗系统资源。
- 此参数与 `max_worker_processes` 直接相关。如果 `max_worker_processes` 设置得太低，可能会限制并行查询的执行。

`max_parallel_workers` 是在优化并行查询性能时需要关注的关键参数，通过合理设置它，可以提升 PostgreSQL 在多核服务器上的查询性能。

郑全

郑全 发表于 2024-8-17 10:38
在 PostgreSQL 中，`max_parallel_workers` 是一个配置参数，用于控制数据库实例中所有并行查询允许使用的 ...

在 PostgreSQL 中，`max_parallel_workers_per_gather` 是一个配置参数，用于控制单个查询中可以使用的最大并行工作进程（parallel workers）数量，尤其是涉及到 `Gather` 或 `Gather Merge` 操作时。

### `max_parallel_workers_per_gather` 参数详解

- **参数作用**: 该参数限制了每个查询中 `Gather` 或 `Gather Merge` 操作所能使用的并行工作进程的数量。`Gather` 操作会启动多个并行工作进程以处理查询的不同部分，然后将结果汇总。这在进行大规模表扫描、复杂聚合或排序时非常有用。

- **默认值**: 2。默认情况下，单个并行查询最多可以使用两个工作进程。

- **可配置范围**: 0 到 `max_parallel_workers`。`max_parallel_workers_per_gather` 的值不能超过 `max_parallel_workers`，因为并行工作进程的总数受 `max_parallel_workers` 的限制。

### 配置示例
你可以在 `postgresql.conf` 文件中设置 `max_parallel_workers_per_gather`：

```plaintext
max_parallel_workers_per_gather = 4
```

或者通过 SQL 动态修改（需要超级用户权限）：

```sql
ALTER SYSTEM SET max_parallel_workers_per_gather TO 4;
```

更改此参数不需要重启 PostgreSQL 服务器即可生效。

### 使用场景
1. **大规模表扫描**: 当查询需要扫描大量数据时，增加 `max_parallel_workers_per_gather` 可以提高性能。并行工作进程可以加速表扫描，使查询更快返回结果。

2. **复杂聚合和排序**: 复杂的聚合和排序操作可以受益于并行处理。将多个工作进程用于这些操作可以显著缩短查询时间。

3. **批量数据处理**: 在进行大数据处理或分析时，并行化查询可以充分利用多核 CPU 资源，提升处理效率。

### 关联参数
- **`max_worker_processes`**: 限制整个数据库实例中所有类型工作进程的数量。
- **`max_parallel_workers`**: 限制整个数据库实例中所有并行工作进程的总数量。`max_parallel_workers_per_gather` 的值不能超过 `max_parallel_workers`。
- **`parallel_tuple_cost` 和 `parallel_setup_cost`**: 这些参数影响 PostgreSQL 在决定是否使用并行执行计划时的成本计算。如果并行执行计划的成本过高，可能不会启用并行查询。

### 如何选择合适的值
- **查询负载**: 如果你的查询经常涉及大规模表扫描或复杂聚合，可以考虑增加 `max_parallel_workers_per_gather` 的值以提高性能。
- **硬件资源**: 在多核服务器上，可以设置更高的值以充分利用 CPU 资源，但在 CPU 核心数有限的系统上，设置过高可能导致资源争用。
- **实际效果**: 通过实验观察查询性能，在设置过高时可能出现资源争用或过度上下文切换，反而导致性能下降。

### 注意事项
- 增加并行工作进程并不总是会带来线性性能提升。对于小数据集或简单查询，启用过多的并行工作进程可能带来额外的开销。
- 如果查询的成本低于并行阈值，PostgreSQL 可能不会使用并行查询，即使 `max_parallel_workers_per_gather` 设得很高。
- 设置过高可能影响其他并行查询的可用资源，导致整体系统性能下降。

`max_parallel_workers_per_gather` 是在优化单个查询并行执行时的重要参数，合理设置它可以提升 PostgreSQL 在多核服务器上的查询效率。

郑全

郑全 发表于 2024-8-17 10:40
在 PostgreSQL 中，`max_parallel_workers_per_gather` 是一个配置参数，用于控制单个查询中可以使用的最 ...

在 PostgreSQL 中，`max_parallel_maintenance_workers` 是一个配置参数，用于控制维护操作（如创建索引、VACUUM）中可以使用的最大并行工作进程数量。

### `max_parallel_maintenance_workers` 参数详解

- **参数作用**: 该参数专门用于控制维护任务（例如并行创建索引或并行 `VACUUM`）中允许使用的并行工作进程数量。通过增加并行工作进程的数量，这些操作可以更快完成，尤其是在处理大表时。

- **默认值**: 2。默认情况下，最多允许两个并行工作进程参与维护操作。

- **可配置范围**: 0 到 `max_worker_processes`。`max_parallel_maintenance_workers` 的值不能超过 `max_worker_processes`，因为所有并行工作进程都是由工作进程池中分配的。

### 配置示例
你可以在 `postgresql.conf` 文件中设置 `max_parallel_maintenance_workers`：

```plaintext
max_parallel_maintenance_workers = 4
```

也可以通过 SQL 动态修改（需要超级用户权限）：

```sql
ALTER SYSTEM SET max_parallel_maintenance_workers TO 4;
```

更改此参数不需要重启 PostgreSQL 服务器即可生效。

### 使用场景
1. **并行创建索引**: 在创建大型索引时，增加 `max_parallel_maintenance_workers` 可以加速索引创建过程。

2. **并行 VACUUM**: `VACUUM` 操作是数据库维护的重要部分，特别是在处理大表时，并行化的 `VACUUM` 操作可以提高速度。

3. **数据加载和准备**: 在数据仓库或批量数据处理中，经常需要大量的索引创建和清理操作，并行维护工作进程可以提升这些任务的执行效率。

### 关联参数
- **`max_worker_processes`**: 限制整个数据库实例中所有类型工作进程的数量。`max_parallel_maintenance_workers` 受其限制。
- **`maintenance_work_mem`**: 控制维护操作（如索引创建和 `VACUUM`）使用的内存。如果内存不足，即使增加了并行工作进程，性能提升也可能有限。
- **`max_parallel_workers`**: 限制整个数据库实例中所有并行工作进程的总数量。

### 如何选择合适的值
- 如果你的系统频繁执行大规模索引创建或 `VACUUM` 操作，并且有足够的硬件资源（CPU 和内存），可以适当增加该参数的值以提升维护操作的效率。
- 对于小型数据库或少量数据的系统，默认值通常已经足够。设置过高的值在这些场景下不会带来显著的性能提升，反而可能浪费资源。
- 在大型数据仓库或数据处理环境中，适当增加此值可以缩短维护任务的执行时间。

### 注意事项
- 增加并行工作进程数量会消耗更多的系统资源，尤其是 CPU 和内存资源。
- 在一些情况下，并行执行可能不会带来线性提升，例如当 I/O 成为瓶颈时，增加并行度可能效果不佳。
- 如果其他并行查询和维护任务同时运行，可能会因为争用相同的资源而影响整体性能。

`max_parallel_maintenance_workers` 是在优化大型维护操作（如索引创建和 `VACUUM`）时的重要参数，合理设置它可以加速这些任务的执行，提升 PostgreSQL 的整体性能。