PostgreSQL的内存参数

PostgreSQL的内存参数

基础信息
OS版本:Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本:16.2
pg软件目录:/home/pg16/soft
pg数据目录:/home/pg16/data
端口:5777

PostgreSQL 提供了多种内存参数,可以通过调整这些参数来优化数据库的性能。以下是一些主要的内存参数及其简要说明:

主要内存参数概述

  1. shared_buffers
    • 描述:决定 PostgreSQL 用于缓存表和索引数据的内存大小。通常推荐设置为系统内存的 25%-50%。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep shared_buffers
shared_buffers = 128MB                  # min 128kB
  1. work_mem
    • 描述:用于操作(如排序、哈希聚合、联接等)内存的大小。每个操作会单独使用这些内存,可以调多但不能过大。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep work_mem
work_mem = 4MB                          # min 64kB
  1. maintenance_work_mem
    • 描述:用于维护操作(如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等)的内存大小。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep maintenance_work_mem
maintenance_work_mem = 64MB             # min 1MB
  1. temp_buffers
    • 描述:用于每个数据库会话的临时表缓存内存大小。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep temp_buffers
temp_buffers = 8MB                      # min 800kB
  1. max_connections
    • 描述:允许的最大数据库连接数。需要结合 shared_buffers 参数调节。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep max_connections
max_connections = 50                    # (change requires restart)
  1. effective_cache_size
    • 描述:设置 PostgreSQL 认为操作系统内核文件系统缓存及 PostgreSQL 缓存的大小。推荐设置为系统内存的 50%-75%。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep effective_cache_size
effective_cache_size = 1GB
  1. wal_buffers
    • 描述:用于存储写入 WAL (Write-Ahead Log) 的缓冲区大小。通常设置为 shared_buffers 的 3%-4%。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep wal_buffers
wal_buffers = 8MB                       # min 32kB, -1 sets based on shared_buffers
  1. checkpoint_completion_target
    • 描述:设置 checkpoint 完成平均占用时间的比例(0到1之间)。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep checkpoint_completion_target
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0
  1. autovacuum_work_mem
    • 描述:用于自动化 VACUUM 操作的内存大小。
    • 示例
[pg16@test ~]$ cat data/postgresql.conf |grep autovacuum_work_mem
autovacuum_work_mem =64MB

内存参数配置示例

以下是一个示例 postgresql.conf 中内存参数配置:

# 内存配置示例

shared_buffers = 2GB                        # 共享内存缓冲区,设置为系统内存的 25%-50%
work_mem = 64MB                             # 每个操作的工作内存,适当调整以优化查询性能
maintenance_work_mem = 512MB                # 维护任务的工作内存
temp_buffers = 64MB                         # 临时表缓存内存
max_connections = 200                       # 最大连接数,根据并发需求设置
effective_cache_size = 6GB                  # 预期总缓存大小,包括操作系统的文件系统缓存
wal_buffers = 16MB                          # WAL 缓冲区
checkpoint_completion_target = 0.7          # checkpoint 完成时间比例
autovacuum_work_mem = 64MB                  # 自动 `VACUUM` 操作的内存大小

其他相关参数

除了主要内存参数,还有一些其他参数可能间接影响内存使用:

  • sort_mem / work_mem: 不同版本的配置名称不同,但作用相似。
  • maintenance_work_mem: 影响 CREATE INDEX, VACUUM, REINDEX 等操作的性能。
  • hash_mem_multiplier: 影响哈希连接的内存分配。
  • max_worker_processes: 影响并行查询的最大工作进程数。
  • parallel_min_parallel_table_scan_size: 影响并行查询的触发条件。

调整和优化建议

  1. 监控内存使用情况
    使用系统工具(如 top, htop, free -m)和 PostgreSQL 内置视图(如 pg_stat_activity)监控内存使用情况。

  2. 逐步调整参数
    从小到大逐步调整内存参数,观察系统性能变化,避免一次性调整过大导致系统不稳定。

  3. 平衡系统资源
    结合数据库和操作系统的内存需求,合理分配系统内存资源。确保操作系统有足够的内存用于文件系统缓存和其他重要任务。

  4. 考虑实际工作负载
    根据实际工作负载特点调整内存参数。例如,查询密集的环境下可以增大 work_mem,而批处理任务较多的环境下则更关注 maintenance_work_mem 等参数。

小结

调整和优化 PostgreSQL 的内存参数可以显著提升数据库的性能和效率。每个参数的具体值应根据系统资源、实际工作负载和性能需求进行合理配置和优化。通过逐步调整和监控,你可以找到最适合你的系统的配置参数组合。

谨记:心存敬畏,行有所止。

上一篇:应用层协议HTTP与HTTPS


下一篇:Unity实现简单的持久化存储