印刻数据库

外观

PostgreSQL 内存配置规范 ​

共享内存配置 ​

shared_buffers 参数 ​

shared_buffers 是 PostgreSQL 最重要的内存参数之一，用于设置数据库服务器用于缓存数据页的内存大小。

配置建议 ​

  • 物理内存小于 4GB：设置为物理内存的 25%
  • 物理内存 4GB-16GB：设置为物理内存的 30%-35%
  • 物理内存 16GB-64GB：设置为物理内存的 35%-40%
  • 物理内存大于 64GB：设置为 24GB-32GB（过大的 shared_buffers 可能导致性能下降）

配置示例 ​

bash
# 在 postgresql.conf 中设置
shared_buffers = 8GB

注意事项 ​

  1. shared_buffers 必须小于操作系统的 SHMMAX 参数
  2. 在 Windows 系统上，shared_buffers 的默认值较低，需要手动调整
  3. 过大的 shared_buffers 可能导致操作系统缓存减少，从而影响整体性能

工作内存配置 ​

work_mem 参数 ​

work_mem 用于设置每个查询在执行排序、哈希等操作时可以使用的内存大小。

配置建议 ​

  • OLTP 系统：设置为 4MB-16MB
  • OLAP 系统：设置为 32MB-128MB
  • 混合工作负载：设置为 16MB-64MB

配置示例 ​

bash
# 在 postgresql.conf 中设置
work_mem = 16MB

注意事项 ​

  1. work_mem 是每个查询操作的内存限制，不是每个连接
  2. 并发查询可能会使用大量内存，需要根据并发连接数调整
  3. 可以通过会话级别的设置为特定查询调整 work_mem

maintenance_work_mem 参数 ​

maintenance_work_mem 用于设置维护操作（如 VACUUM、CREATE INDEX 等）可以使用的内存大小。

配置建议 ​

  • 物理内存小于 4GB：设置为 128MB-256MB
  • 物理内存 4GB-16GB：设置为 256MB-1GB
  • 物理内存大于 16GB：设置为 1GB-4GB

配置示例 ​

bash
# 在 postgresql.conf 中设置
maintenance_work_mem = 1GB

注意事项 ​

  1. maintenance_work_mem 是每个维护进程的内存限制
  2. 同时运行多个维护操作时，会消耗多倍的 maintenance_work_mem 内存
  3. 可以根据系统负载情况调整该参数

有效缓存大小配置 ​

effective_cache_size 参数 ​

effective_cache_size 用于告诉 PostgreSQL 数据库系统预期可以使用的操作系统缓存大小。

配置建议 ​

  • 一般建议：设置为物理内存的 50%-75%
  • OLAP 系统：可以设置为物理内存的 75%-85%
  • 内存密集型应用：可以根据实际情况调整

配置示例 ​

bash
# 在 postgresql.conf 中设置
effective_cache_size = 24GB

注意事项 ​

  1. effective_cache_size 只是一个提示，不实际分配内存
  2. 合理设置 effective_cache_size 可以帮助 PostgreSQL 优化查询计划
  3. 该参数的设置应该考虑到系统上其他进程的内存使用情况

其他内存相关参数 ​

temp_buffers 参数 ​

temp_buffers 用于设置每个会话用于临时表的内存大小。

配置建议 ​

  • 默认值：8MB
  • 一般建议：不需要频繁调整，除非有大量临时表操作
  • 临时表密集型应用：可以调整为 32MB-128MB

配置示例 ​

bash
# 在 postgresql.conf 中设置
temp_buffers = 16MB

max_stack_depth 参数 ​

max_stack_depth 用于设置服务器执行堆栈的最大深度。

配置建议 ​

  • 默认值：2MB
  • 一般建议：保持默认值或根据实际需要调整
  • 注意：该值必须小于操作系统的堆栈大小限制

配置示例 ​

bash
# 在 postgresql.conf 中设置
max_stack_depth = 2MB

wal_buffers 参数 ​

wal_buffers 用于设置 WAL（预写式日志）缓冲区的大小。

配置建议 ​

  • 默认值：-1（自动调整，通常为 shared_buffers 的 1/32，最大 16MB）
  • 一般建议：保持默认值即可
  • 高写入负载系统：可以手动设置为 32MB-64MB

配置示例 ​

bash
# 在 postgresql.conf 中设置
wal_buffers = 32MB

内存配置最佳实践 ​

1. 根据工作负载类型调整 ​

  • OLTP 系统：优化 shared_buffers 和 work_mem，适当减少 maintenance_work_mem
  • OLAP 系统：增加 work_mem 和 maintenance_work_mem，优化 effective_cache_size
  • 混合工作负载：平衡各参数设置，考虑使用资源组或会话级参数调整

2. 考虑系统总内存 ​

  • 内存配置总和不应超过系统物理内存的 80%-90%
  • 预留足够内存给操作系统和其他进程
  • 考虑使用大页内存（Huge Pages）减少 TLB 压力

3. 监控内存使用情况 ​

  • 使用 pg_stat_bgwriter 视图监控缓冲区写回情况
  • 使用 pg_stat_statements 扩展监控查询内存使用
  • 监控操作系统级别的内存使用情况
  • 定期分析内存配置对性能的影响

4. 逐步调整参数 ​

  • 每次只调整一个或少数几个参数
  • 调整后进行性能测试，验证效果
  • 记录每次调整的参数值和性能指标
  • 根据测试结果决定是否保留调整

5. 考虑使用资源管理 ​

  • PostgreSQL 14+ 支持资源组功能，可以按用户或角色分配内存资源
  • 可以使用 cgroups 等操作系统工具限制 PostgreSQL 进程的内存使用
  • 考虑使用连接池限制并发连接数，减少内存压力

不同版本的内存配置差异 ​

PostgreSQL 12 及以上版本 ​

  • 引入了 jit_worker_processes 参数，用于控制 JIT 编译的工作进程数
  • 改进了内存使用统计，提供更详细的内存使用信息
  • 支持更大的 shared_buffers 设置

PostgreSQL 13 及以上版本 ​

  • 引入了增量排序功能，可能减少某些查询的内存使用
  • 改进了并行查询的内存管理
  • 引入了 vacuum_cleanup_index_scale_factor 参数，影响 VACUUM 操作的内存使用

PostgreSQL 14 及以上版本 ​

  • 引入了资源组功能，可以更精细地控制内存资源分配
  • 改进了哈希连接的内存使用
  • 引入了 wal_consistency_checking 参数，可能增加内存使用

常见问题（FAQ） ​

Q1: shared_buffers 设置得越大越好吗？ ​

A1: 不是。shared_buffers 设置过大可能导致以下问题：

  1. 减少操作系统缓存，从而影响整体性能
  2. 增加内存管理开销
  3. 延长数据库启动时间
  4. 增加检查点的开销

建议根据物理内存大小和工作负载类型合理设置 shared_buffers。

Q2: 如何计算合适的 work_mem 值？ ​

A2: 可以使用以下公式作为参考：

work_mem = (系统可用内存 × 0.2) / 最大并发查询数

其中，系统可用内存是指扣除 shared_buffers、maintenance_work_mem 和其他进程内存使用后的内存。

Q3: 如何监控 PostgreSQL 的内存使用情况？ ​

A3: 可以使用以下方法监控 PostgreSQL 的内存使用：

  1. 使用 ps 或 top 命令查看 PostgreSQL 进程的内存使用
  2. 查询 pg_stat_bgwriter 视图了解缓冲区活动情况
  3. 使用 pg_stat_statements 扩展分析查询的内存使用
  4. 查看 PostgreSQL 日志中的内存相关警告
  5. 使用 free 或 vmstat 命令监控系统级内存使用

Q4: 为什么设置了较大的 work_mem，但查询性能没有提升？ ​

A4: 可能的原因包括：

  1. 查询没有使用需要大量内存的操作（如排序、哈希连接）
  2. 查询计划没有选择使用内存操作
  3. 并发查询过多，导致实际可用内存不足
  4. 其他瓶颈（如 I/O、CPU）限制了性能提升

建议分析查询计划，确认查询是否使用了内存密集型操作。

Q5: 在高并发环境下如何优化内存配置？ ​

A5: 在高并发环境下，可以考虑以下优化措施：

  1. 适当降低 work_mem 值，避免单个查询占用过多内存
  2. 增加 shared_buffers，提高数据缓存命中率
  3. 使用连接池限制并发连接数
  4. 考虑使用资源组功能，按用户或角色分配内存资源
  5. 监控内存使用情况，及时调整配置

Q6: 如何处理内存不足的问题？ ​

A6: 处理内存不足的问题可以采取以下措施：

  1. 增加系统物理内存
  2. 调整 PostgreSQL 内存参数，减少内存使用
  3. 优化查询，减少内存密集型操作
  4. 限制并发连接数
  5. 考虑使用更高效的算法或数据结构
  6. 监控内存泄漏情况

Q7: 大页内存（Huge Pages）对 PostgreSQL 性能有什么影响？ ​

A7: 使用大页内存可以带来以下好处：

  1. 减少 TLB（Translation Lookaside Buffer）压力，提高内存访问效率
  2. 减少内存碎片
  3. 降低内存管理开销

但配置大页内存需要操作系统和 PostgreSQL 都进行相应设置，建议在高性能要求的环境中使用。

Q8: 如何在不同环境中统一内存配置？ ​

A8: 可以考虑以下方法统一内存配置：

  1. 使用配置管理工具（如 Ansible、Puppet）管理 PostgreSQL 配置
  2. 根据系统内存大小动态生成配置文件
  3. 使用环境变量或参数化配置模板
  4. 建立内存配置规范，根据不同环境类型（开发、测试、生产）制定标准配置
  5. 定期审计和同步配置

配置验证和测试 ​

1. 配置验证 ​

在修改内存配置后，需要验证配置是否正确生效：

bash
# 查看当前配置值
psql -c "SHOW shared_buffers;"
psql -c "SHOW work_mem;"
psql -c "SHOW maintenance_work_mem;"
psql -c "SHOW effective_cache_size;"

2. 性能测试 ​

修改内存配置后，建议进行性能测试，验证配置调整的效果：

  • 使用 pgbench 进行基准测试
  • 运行实际业务查询，比较性能变化
  • 监控系统和数据库的各项性能指标
  • 测试不同负载下的表现

3. 监控和调整 ​

在生产环境中，需要持续监控内存使用情况，并根据实际情况调整配置：

  • 设置合适的监控告警，及时发现内存问题
  • 定期分析内存使用趋势
  • 根据业务量变化调整内存配置
  • 考虑季节性业务波动，提前调整配置