调优您的 PostgreSQL Server 以提升性能
PostgreSQL 是一个开源的企业级关系数据库管理系统，以其强大的稳定性著称。许多组织选择 PostgreSQL，因为它支持高级数据类型，符合 ACID 模型，易于扩展且具有强大的安全功能。由于许多关键任务应用程序使用 PostgreSQL，数据库管理员必须确保拥有监控系统来帮助识别痛点，并在必要时调优数据库以提升性能。
让我们来看看调优 PostgreSQL 数据库服务器时一些最重要的配置参数：
内存调优
| 设置 | shared_buffers |
|---|---|
| 描述 | 共享缓冲区是内存中的特定部分，用于缓存经常访问的索引块和数据。通过利用共享缓冲区，数据库系统可以减少从磁盘读取数据的需求（磁盘访问通常较慢），从而提升性能。 注意： 增加此配置要求同时增加 max_wal_size，以便在发生检查点时处理更多的数据。 |
| 默认值 | 128 MB |
| 推荐值 | 25% 的 RAM |
| 设置 | work_mem |
|---|---|
| 描述 | PostgreSQL 中的工作内存是为执行单个操作（如排序或哈希）分配的内存。该内存存储临时数据结构和操作过程中生成的中间结果。所需的工作内存大小取决于操作的复杂性和输入数据的大小，以及系统内存的可用性。工作内存不足可能导致操作使用基于磁盘的临时文件，性能下降。 注意： Postgres 13 引入了一个名为 hash_mem_multiplier 的新配置，作为优化内存分配的额外选项。 |
| 默认值 | 4 MB |
| 推荐值 | 可以根据查询并加载到内存中的数据量选择一个合理值。在设置值时，你还需要考虑 max_connections 参数，因为可能有多个连接并行运行。（总使用内存 = work_mem * 并发连接数）。找到 work_mem 的最佳值可能具有挑战性，但一个普遍合理的默认值大约为 64 MB。 |
| 设置 | maintenance_work_mem |
|---|---|
| 描述 | maintenance_work_mem 参数控制用于维护操作（如 VACUUM、索引创建和添加外键）的内存量。由于在一个数据库会话中一次只能执行这些操作中的一个，因此可以安全地将此值设置为大于 work_mem。较大设置可能提升 VACUUM 和恢复数据库转储的性能。 |
| 默认值 | 64 MB |
| 推荐值 | 需要考虑的一点是，当 autovacuum 运行时，内存消耗 = autovacuum_max_workers x maintenance_work_mem。该值可达 512 MB。 |
缓存调优
| 设置 | effective_cache_size |
|---|---|
| 描述 | effective_cache_size 参数指定数据库服务器可用于缓存数据和索引块的内存量。该参数代表了操作系统文件系统缓存和任何其他在同一系统上运行的进程使用的估算内存量。 effective_cache_size 参数被查询规划器用来计算各种查询执行计划的代价。该设置允许规划器合理估计预期缓存于内存中的数据量，从而作出优化查询执行计划选择的决策。 |
| 默认值 | 4 GB |
| 推荐值 | 50% 的内存 |
工作参数
| 设置 | max_worker_processes |
|---|---|
| 描述 | max_worker_processes 参数设置 PostgreSQL 中同时运行的后台工作进程的最大数量。这些工作进程执行各种功能，如并行查询执行、处理连接和后台任务。 |
| 默认值 | 8 |
| 推荐值 | 建议使用 50% 的处理器资源，以确保系统正常运行。 |
| 设置 | max_parallel_workers |
|---|---|
| 描述 | max_parallel_workers 参数限制 PostgreSQL 中可同时激活的并行工作进程总数。这些工作进程用于在并行查询执行及其他操作中提升系统性能。此参数的配置非常关键，因为它显著影响资源利用率和系统整体性能。 |
| 默认值 | 2 |
| 推荐值 | 建议使用 50% 的处理器资源，以确保系统最佳运行状态。 |
| 设置 | max_parallel_workers_per_gather |
|---|---|
| 描述 | max_parallel_workers_per_gather 参数控制单个操作中可参与的最大并行工作进程数。该参数对可执行并行表扫描和查询计划的工作进程数量设定上限。它对并行查询性能有显著影响，配置时需考虑系统资源和工作负载特性。 |
| 默认值 | 2 |
| 推荐值 | 并行工作进程从 max_worker_processes 设定的处理器池中选取，并受 max_parallel_workers 限制。 |
其他参数
| 设置 | max_wal_size |
|---|---|
| 描述 | max_wal_size 参数设定 PostgreSQL 中写前日志（WAL）的最大允许大小。WAL 是 PostgreSQL 事务管理机制中的关键部分，负责在系统崩溃或故障时保持数据一致性和持久性。 配置此参数可设置自动检查点期间 WAL 增长的最大大小。在特殊情况下，如高负载、archive_command 失败或设置了较高的 wal_keep_size，WAL 大小可能超过 max_wal_size。 |
| 默认值 | 1 GB |
| 推荐值 | 增加此参数可能会延长崩溃恢复所需时间。 |
| 设置 | default_statistics_target |
|---|---|
| 描述 | default_statistics_target 配置参数指定查询优化器收集数据库统计信息时的详细程度。该参数控制优化器为确定最优查询执行计划而检查的行数。它决定了分析表时优化器使用的样本大小。 |
| 默认值 | 样本大小 = 300 * default_statistics_target |
| 推荐值 | 较大数值会增加 ANALYZE 所需时间，但可能提升规划器估算的质量。 |
警告： 更改配置会对系统产生影响。建议在将更改应用于生产环境前，先在测试负载下测试系统。
Applications Manager 是监控 PostgreSQL 数据库性能的高效工具。凭借其广泛的监控能力，帮助数据库管理员确保数据库平稳运行，长期内实现最小停机时间和可预测的开销。您可以为关键指标配置警报，并在阈值被触发时收到通知。这可以作为调优数据库以提升性能、确保用户体验顺畅的预警！
想亲自体验使用 Applications Manager 调优 PostgreSQL 性能
