PG参数深入了解

喝醉酒的小白

已于 2024-07-16 11:57:19 修改

阅读量1.2k

点赞数 41

分类专栏： PG 文章标签：运维

于 2024-07-16 10:40:44 首次发布

本文链接：https://blog.csdn.net/hezuijiudexiaobai/article/details/140457656

版权

PG 专栏收录该内容

140 篇文章 35 订阅

订阅专栏

shared_buffers

shared_buffers 是 PostgreSQL 数据库配置文件 postgresql.conf 中的一个重要参数，它用于设置数据库服务器用于缓存数据和索引的内存量。这是数据库性能调优中最关键的参数之一。

作用：

shared_buffers 参数控制着 PostgreSQL 用于共享内存的总量，这些内存用于存储数据和索引的缓存。
缓存可以显著提高数据库查询性能，因为它减少了对磁盘的访问需求。

配置建议：

起始配置：
- 默认情况下，PostgreSQL 会根据可用内存设置 shared_buffers 的大小。通常，这个默认值是足够的，但根据工作负载和系统资源，可能需要进一步调整。
内存大小：
- 一般建议将 shared_buffers 设置为系统总内存的 25% 到 50%。例如，如果你的服务器有 16GB 的 RAM，shared_buffers 可以设置为 4GB 到 8GB。
内存单位：
- 参数值应该以 MB 为单位提供。例如：
```
shared_buffers = 8GB
```
监控和调整：
- 监控数据库的性能指标，如缓存命中率，以评估当前设置是否合适。如果缓存命中率很高（通常高于 90%），可能需要增加 shared_buffers 的大小。
考虑 I/O 性能：
- 如果你的存储系统的 I/O 性能较低，增加 shared_buffers 可能带来更大的性能提升。
避免设置过高：
- 不要将 shared_buffers 设置得过高，以至于消耗掉服务器上所有的可用内存。这可能会影响到操作系统和其他应用程序的正常运行。
操作系统限制：
- 考虑操作系统对共享内存的限制。在某些系统上，可能需要调整操作系统的配置以允许 PostgreSQL 使用更多的共享内存。
重启生效：
- 修改 shared_buffers 后，需要重启 PostgreSQL 服务以使更改生效。
查看文档：
- 查阅 PostgreSQL 的官方文档，了解 shared_buffers 参数的更多细节和推荐的最佳实践。
测试环境验证：
- 在生产环境中应用更改之前，先在测试环境中验证更改的效果。

示例配置：

shared_buffers = 4GB  # 设置为 4GB

请记住，合理的 shared_buffers 设置可以显著提高数据库性能，但也需要根据实际的系统资源和工作负载进行细致调整。

work_mem

work_mem 是 PostgreSQL 数据库中的一个配置参数，用于指定数据库服务器为每个会话（session）动态分配的工作内存的总量。这个参数对于数据库查询的性能至关重要，因为它决定了 PostgreSQL 可以为排序（sort）、哈希表（hash tables）、聚合（aggregates）等操作在内存中使用的数据量。

以下是 work_mem 参数的一些关键点：

单位: work_mem 的单位是 kilobytes (KB)。例如，如果设置为 4MB，应该写为 4096（因为 1MB = 1024KB）。
默认值: PostgreSQL 的默认 work_mem 值通常设置得比较低，以避免内存消耗过快。在不同版本中，这个值可能有所不同。
影响: 增加 work_mem 的值可以提高查询性能，尤其是在处理大量数据时。但是，如果设置得太高，可能会导致内存使用过高，影响系统的整体性能和稳定性。
并行处理: 当启用了并行查询时（通过 max_parallel_workers 或 max_parallel_workers_per_gather 参数），每个工作进程（worker）都会分配 work_mem 的内存。因此，在并行环境中，总的内存需求会随着 work_mem 值的增加而成倍增长。
配置方法: 可以在 postgresql.conf 文件中设置 work_mem 参数，也可以在会话级别或角色级别使用 SET 命令动态设置。
监控: 可以使用诸如 pg_stat_activity 这样的系统视图来监控当前会话的内存使用情况。
内存压力: 如果数据库服务器遇到内存压力，PostgreSQL 会使用临时文件进行操作，这会显著降低查询性能。合理配置 work_mem 可以减少这种情况的发生。
与其他参数的关系: work_mem 与 maintenance_work_mem（用于维护操作，如 VACUUM）不同，后者专门用于数据库维护任务。

合理调整 work_mem 参数可以帮助优化数据库性能，但需要根据具体的工作负载、数据量和硬件资源进行细致的调整和测试。在生产环境中更改此类参数之前，建议进行充分的测试。

maintenance_work_mem

maintenance_work_mem 是 PostgreSQL 数据库中的一个配置参数，它专门用于维护操作期间的内存分配。这些维护操作包括但不限于：

VACUUM：清理数据库中的死元组（dead tuples），回收空间，并更新索引统计信息。
ANALYZE：收集表和索引的统计信息，以帮助查询优化器选择更好的查询计划。
REINDEX：重建表或索引。
CLUSTER：根据特定的索引对表进行物理重新排序。

以下是 maintenance_work_mem 参数的一些关键点：

单位: maintenance_work_mem 的单位是 kilobytes (KB)。
默认值: PostgreSQL 的默认 maintenance_work_mem 值通常比 work_mem 要大，以便在执行资源密集型的维护任务时有足够的内存可用。
影响: 增加 maintenance_work_mem 的值可以提高维护操作的性能，特别是在处理大型表和索引时。但是，如果设置得太高，可能会占用大量内存，影响系统的整体性能。
内存压力: 与 work_mem 类似，如果 maintenance_work_mem 设置得太高，数据库可能会在内存不足时使用临时文件进行操作，这会降低性能。
配置方法: 可以在 postgresql.conf 文件中设置 maintenance_work_mem 参数。设置后，需要重启数据库服务器以使更改生效。
监控: 可以使用 PostgreSQL 的系统视图，如 pg_stat_activity，来监控当前的内存使用情况。
与其他参数的关系: maintenance_work_mem 与 work_mem 是分开的，因为它们用于不同类型的操作。work_mem 主要用于常规的查询和数据处理操作，而 maintenance_work_mem 专门用于维护任务。
性能调优: 在进行性能调优时，可能需要根据系统的工作负载和可用资源来调整 maintenance_work_mem。在某些情况下，为了加快长时间的维护操作，可能需要增加这个值。但是，这应该在监控和测试的基础上进行。
考虑系统内存: 在多用户环境中，需要考虑所有并发维护操作可能同时需要的内存总量。如果系统内存有限，可能需要适当降低 maintenance_work_mem 的值。

合理配置 maintenance_work_mem 可以帮助提高数据库维护任务的效率，但需要根据具体的系统资源和工作负载进行调整。在生产环境中更改此类参数之前，建议进行充分的测试以避免潜在的性能问题。

autovacuum_work_mem

在 PostgreSQL 中，autovacuum_work_mem 参数用于设置自动真空（autovacuum）操作可以使用的工作内存量。自动真空是 PostgreSQL 中的一个维护进程，用于回收已删除的行所占用的空间，并防止表的膨胀。以下是关于如何设置 autovacuum_work_mem 参数的一些建议：

默认值 -1：
- 当设置为 -1 时，自动真空操作将使用 maintenance_work_mem 参数的值作为其工作内存限制。
使用 maintenance_work_mem：
- 如果你希望自动真空使用与手动维护操作（如 VACUUM 和 ANALYZE）相同的内存量，可以保持 autovacuum_work_mem 为 -1。
最小值：
- autovacuum_work_mem 的最小值是 1MB。如果你设置一个小于 1MB 的值，PostgreSQL 将使用默认的最小值。
资源充足时的设置：
- 如果你的数据库服务器有足够的内存，并且你希望自动真空操作运行得更快一些，可以增加这个值。
资源受限时的设置：
- 如果服务器的内存资源有限，或者你希望限制自动真空对系统内存的影响，可以设置一个较小的值，或者保持为 -1。
监控和调整：
- 监控数据库的性能和内存使用情况，根据实际表现调整 autovacuum_work_mem 的值。
设置示例：
- 如果你想要为自动真空设置一个具体的内存值，比如 128MB，你可以在 postgresql.conf 文件中设置：
```
autovacuum_work_mem = 128MB
```
- 如果你想要自动真空使用与 maintenance_work_mem 相同的内存量，你可以这样设置：
```
autovacuum_work_mem = -1
maintenance_work_mem = 256MB  # 例如，设置为 256MB
```
考虑 I/O 性能：
- 除了内存使用外，自动真空操作还可能对 I/O 性能产生影响。确保你的系统有足够的 I/O 能力来处理自动真空和常规数据库操作。
查看文档：
- 查阅 PostgreSQL 的官方文档，了解 autovacuum_work_mem 和 maintenance_work_mem 的更多细节和推荐设置。
测试：
- 在生产环境中更改配置之前，最好在测试环境中模拟工作负载并测试配置的效果。

记住，自动真空的内存使用不会影响其他数据库操作的内存使用，因为它运行在后台。但是，如果自动真空占用了太多内存，可能会影响数据库的总体性能，因此需要根据实际情况进行适当的调整。

effective_cache_size

在 PostgreSQL 中，effective_cache_size 参数用于估计数据库服务器的总可用缓存大小，包括内存中的共享缓冲区以及其他形式的系统缓存。这个参数对于数据库查询优化器选择查询计划（execution plan）非常重要，尤其是在涉及到大量数据扫描（如顺序扫描）和索引扫描的情况下。

计算规格：

理解参数作用：
- 查询优化器使用 effective_cache_size 来估计在不访问磁盘的情况下，可以缓存多少数据。这有助于优化器决定是使用索引扫描还是顺序扫描。
默认值：
- 如果不显式设置 effective_cache_size，PostgreSQL 会假设所有数据都可以被缓存，这在有足够内存的情况下可能是合理的。
计算方法：
- 通常，effective_cache_size 应该设置为数据库服务器的物理内存大小加上操作系统的文件系统缓存（如果已知）。例如，如果你的数据库服务器有 128GB 的 RAM，并且你知道操作系统的文件系统缓存大约占用了 20GB，那么 effective_cache_size 可以设置为 148GB。
考虑系统其他使用：
- 在计算时，需要考虑到系统上运行的其他应用程序也需要内存，因此可能需要从总内存中减去一部分。
监控系统缓存：
- 监控系统的实际缓存使用情况，确保 effective_cache_size 的设置接近实际可用的缓存大小。
设置示例：
- 如果你的服务器有 256GB 的内存，并且你决定为系统其他部分保留 10% 的内存，你可以这样设置：
```
effective_cache_size = 230GB  # 256GB 的 90%
```
调整查询性能：
- 根据查询性能监控和实际的缓存命中率来调整 effective_cache_size 的值。
文档参考：
- 查阅 PostgreSQL 的官方文档，了解 effective_cache_size 参数的更多信息和推荐的最佳实践。
测试更改：
- 在将更改应用到生产环境之前，在测试环境中进行更改，并观察其对性能的影响。
操作系统缓存：
- 如果你的操作系统配置为积极地缓存数据库文件，可能需要将这部分缓存考虑在内。

注意事项：

设置 effective_cache_size 过大可能会导致优化器过于乐观地估计缓存命中率，从而选择不是最优的查询计划。
设置过小可能会使优化器过于保守，导致不必要的索引扫描而不是顺序扫描。

正确设置 effective_cache_size 可以帮助提高查询性能，但是需要根据实际的系统资源和工作负载进行细致调整。

wal_buffers

在 PostgreSQL 中，wal_buffers 参数用于配置 WAL (Write-Ahead Logging) 缓冲区的大小。这个缓冲区是用于存储在事务提交之前必须写入磁盘的 WAL 数据的内存区域。wal_buffers 的设置会影响 PostgreSQL 的性能，尤其是在高并发写入的场景下。

参数说明：

当 wal_buffers 设置为 -1 时，PostgreSQL 会根据 shared_buffers 参数的大小自动计算 WAL 缓冲区的大小。计算公式通常是 shared_buffers 的 1/32。
wal_buffers 的最小值是 32kB。
如果你希望手动设置 WAL 缓冲区的大小，可以给它一个正整数值，例如 wal_buffers = 16MB。

多少合适：

这取决于你的工作负载和系统资源。如果你的系统有较多的内存并且你的工作负载需要高并发写入，增加 wal_buffers 可能会提高性能。
通常，设置 wal_buffers 为 shared_buffers 的 1/32 到 1/16 是一个起点。例如，如果你有 1GB 的 shared_buffers，那么 wal_buffers 可以设置为大约 32MB 到 64MB。
但是，这只是一个粗略的估计。你应该根据实际的系统监控和性能测试来调整这个值。
需要注意的是，WAL 缓冲区太大可能会增加事务的提交时间，因为它需要更多的时间来将数据刷新到磁盘。
另外，wal_buffers 不应设置得太大以至于消耗过多内存，影响数据库的其他操作。