PostgreSQL 是一款功能强大、性能出色的开源关系型数据库管理系统。然而，如同许多复杂的系统一样，如果数据库参数配置不当，可能会导致严重的性能问题。在运行 PostgreSQL 数据库时，合理的参数配置是确保其高效、稳定运行的关键因素之一。

一、理解 PostgreSQL 中的关键参数

在深入探讨如何应对参数配置不当带来的性能问题之前，我们需要先了解一些 PostgreSQL 中常见且关键的参数。

（一） shared_buffers

shared_buffers 是 PostgreSQL 用于缓存数据页的内存区域。增加此参数值可以提高数据的访问速度，减少磁盘 I/O 操作，但过大的值可能会导致内存竞争和系统开销增加。

（二） work_mem

work_mem 用于排序和哈希等操作的内存分配。适当增加它可以加快复杂查询的处理速度，但同样也需要注意避免占用过多内存。

（三） effective_cache_size

effective_cache_size 告诉优化器系统可用的缓存总量（包括操作系统缓存），以便优化器能够做出更准确的查询计划。

（四） maintenance_work_mem

用于执行维护操作（如 VACUUM、CREATE INDEX 等）的内存分配。

二、参数配置不当导致的性能问题

（一） 内存不足

1. 如果 shared_buffers 设置过高，可能会导致系统内存不足，影响其他进程的正常运行。例如，在一个内存资源有限的服务器上，将 shared_buffers 设置为 8GB，而服务器总共只有 16GB 内存，系统和其他应用程序可能会因为缺乏内存而性能下降。
2. 当 work_mem 设置过高，且存在多个并发的复杂查询（涉及排序、哈希等操作）时，可能会迅速耗尽可用内存。假设系统中同时进行 10 个大型的排序操作，每个排序操作所需的 work_mem 为 512MB，而总内存无法满足需求，可能导致系统频繁进行内存交换，性能急剧下降。

（二） 磁盘 I/O 过高

1. 若 shared_buffers 设置过低，数据经常需要从磁盘读取，增加了磁盘 I/O 操作，从而降低了数据库的响应速度。比如，在一个高并发的读密集型系统中，由于 shared_buffers 过小，许多热点数据无法在内存中找到，需要从磁盘读取，导致磁盘 I/O 成为系统的瓶颈。
2. 不合适的 effective_cache_size 会导致优化器做出错误的查询计划，例如低估可用缓存，从而选择了全表扫描而不是索引扫描，引起不必要的磁盘 I/O 操作。

（三） 并发性能差

1. 如果 max_connections（最大连接数）设置过低，在高并发场景下，新的连接请求可能会被拒绝，导致应用程序无法正常访问数据库。
2. 配置不当的 wal_buffers （WAL 缓冲区大小）可能导致 WAL （Write-Ahead Logging）写入磁盘的频率过高，影响并发写操作的性能。

三、检测参数配置不当的方法

（一） 性能监控工具

1. pg_stat_statements：它可以帮助跟踪查询的执行统计信息，包括执行时间、调用次数、共享内存使用等。通过分析这些信息，可以发现执行效率低下的查询，并进一步检查相关的参数配置。

CREATE EXTENSION pg_stat_statements;

2. pg_stat_activity：提供有关当前正在进行的后端进程和会话的信息，包括正在执行的查询、等待的事件等。

SELECT * FROM pg_stat_activity;

（二） 系统性能指标监控

1. 内存使用情况：通过操作系统的监控工具（如 top、free 等命令）观察 PostgreSQL 进程的内存占用以及系统的可用内存。
2. 磁盘 I/O 指标：使用工具如 iostat 来监测磁盘的读写操作次数和吞吐量。
3. CPU 使用率：借助系统工具查看数据库进程对 CPU 的使用情况。

（三） 解释查询计划

使用 EXPLAIN 和 EXPLAIN ANALYZE 命令来查看查询的执行计划和实际执行的性能信息，有助于发现是否因为参数配置问题导致优化器选择了不理想的执行计划。

EXPLAIN SELECT * FROM your_table;
EXPLAIN ANALYZE SELECT * FROM your_table;

四、解决方案和优化策略

（一） 基于系统资源进行配置

1. 评估系统的硬件资源，包括内存总量、CPU 核心数、磁盘类型和带宽等。例如，对于具有 64GB 内存、8 核 CPU 的服务器，可以根据经验将 shared_buffers 初始设置为 16GB，work_mem 为 32MB - 64MB 之间，并根据实际性能调整。
2. 根据系统的主要负载类型（读密集型或写密集型）进行针对性的优化。如果系统以读操作为主，可以适当增大 shared_buffers ；如果是写密集型，确保 wal_buffers 和 commit_delay 等参数的合理配置。

（二） 逐步调整和测试

1. 不要一次性对多个参数进行大幅度的更改，而是每次调整一个关键参数，然后观察系统性能的变化。例如，首先将 shared_buffers 增加 10% ，经过一段时间的负载测试后，检查性能指标是否有所改善。
2. 使用基准测试工具（如 pgbench ）创建模拟实际负载的测试场景，在调整参数前后进行测试，并对比结果。

（三） 参考最佳实践和社区经验

1. 查阅 PostgreSQL 官方文档中的参数推荐值和配置建议，了解不同版本和使用场景下的最佳参数设置。
2. 参与 PostgreSQL 社区，与其他用户交流经验，参考他们在类似环境中的参数配置和解决方案。

（四） 利用动态配置

PostgreSQL 支持在服务器运行时动态修改某些参数。例如，可以在遇到临时高并发负载时，动态增加 max_connections 参数的值，以适应更多的连接。

五、具体示例

假设我们有一个运行在 8GB 内存服务器上的 PostgreSQL 数据库，主要用于处理在线交易业务，读写比例接近 1:1。

初始配置如下：

shared_buffers = 1GB
work_mem = 64MB
effective_cache_size = 2GB

通过性能监控发现，系统存在较高的磁盘 I/O 等待时间，且许多查询的响应时间较长。

首先，考虑增大 shared_buffers ，将其调整为 2GB：

ALTER SYSTEM SET shared_buffers = '2GB';

然后重新启动 PostgreSQL 服务以使更改生效，经过一段时间的观察，发现磁盘 I/O 等待时间有所降低，但还有一些复杂的排序查询性能不佳。

接下来，增加 work_mem ，例如设置为 128MB ：

ALTER SYSTEM SET work_mem = '128MB';

再次观察性能指标，发现排序查询的性能得到了显著提升。

然而，如果进一步增大 work_mem 到 256MB ，并且同时有多个并发的此类查询执行时，通过 pg_stat_activity 观察到服务器的内存使用急剧上升，可能导致系统出现不稳定。此时，需要回退 work_mem 的设置到较为合理的值，例如 192MB 。

通过不断的调整和测试，结合系统的性能监控和查询计划分析，最终找到一组适合当前系统负载和硬件资源的参数配置。

六、性能优化的注意事项

（一） 数据备份

在进行任何重要的参数调整之前，务必对数据库进行完整的备份。以防参数更改导致不可预料的问题，可以快速恢复到之前的正常状态。

（二） 记录更改

详细记录每次参数更改的内容、时间和更改的原因。这有助于在出现问题时能够追溯并分析问题的根源。

（三） 性能回归测试

更改参数后，进行全面的性能回归测试，确保系统在各种典型负载下的性能都得到了预期的改善，并且没有引入新的问题。

（四） 多人协作和审批

对于生产环境的重要参数更改，应采用多人协作的方式，经过相关审批流程，确保更改的合理性和安全性。

PostgreSQL 中参数配置不当可能导致多种性能问题，但通过合理的检测方法、优化策略和实践经验，可以有效地解决这些问题，提升数据库的性能和稳定性。需要不断地学习和实践，结合具体的业务需求和系统环境，找到最适合的参数配置方案。

🎉相关推荐

• 🍅关注博主🎗️ 带你畅游技术世界，不错过每一次成长机会！
• 📚领书：PostgreSQL 入门到精通.pdf
• 📙PostgreSQL 中文手册
• 📘PostgreSQL 技术专栏