PostgreSQL数据库缓存配置_pg数据库内存缓存参数配置

数据库内存不足，服务器交换空间大量占用，以及最近遇到的autovacuum进程导致内存溢出等问题。本文就数据库参数配置预防内存溢出

计算公式

max_connections * work_mem + max_connections * temp_buffers +shared_buffers * 2
+ autovacuum_max_workers * autovacuum_work_mem + maintenance_work_mem + wal_buffers
+ OS运行最小要求的内存（2GB）< 服务器物理内存

• shared_buffers*2的原因是pg数据库是双缓存，虽然系统缓存不一定就和shared_buffers一样大，但是差距不会太大

• 如果没有配置autovacuum_work_mem那么默认会以maintenance_work_mem为准

• 以一台128GB服务器，3000连接为例：

autovacuum_max_workersmax_connections = 3000
shared_buffers=32GB #--1/4物理内存
temp_buffers=8MB --默认值
work_mem=4MB --默认值
wal_buffers=16MB #--with-wal-segsize的默认值
autovacuum_max_workers = 3 --默认值
maintenance_work_mem=2GB  --vacuum，create index等维护工作内存
autovacuum_work_mem = 1GB --autovacuum清理进程，总耗内存：autovacuum_max_workers*autovacuum_work_mem

计算如下：

select (3000*4+3000*8+32*1024*2+3*1*1024+2*1024+16)/1024=104GB

pg满载峰值时最多使用104GB内存，物理内存128GB，还有24GB供操作系统使用，但是如果我们改大连接数到6000，计算出结果139GB超过了服务器物理内存，那么在高负载的时候就有内存溢出的风险

effective_cache_size

推荐值50%  RAM

默认值：4GB

除了上面这些参数外，我们还经常看到一个参数effective_cache_size，这个值仅由PostgreSQL查询规划器使用，以确定它所考虑的计划是否应该适合RAM。

具体点就是：如果effecve_cache_size的值太低，那么查询规划器可能会决定不使用某些索引，即使它们可以极大地提高查询速度。所以换句话说，这个值是PG用来估计索引的成本用的。

effective_cache_size的保守值是系统上可用总量的1/2。最常见的情况是，该值被设置为专用DB服务器上系统总内存的75%，但根据特定服务器工作负载上的特定离散需求，该值可以有所不同。

此参数提供可用于磁盘缓存的总内存的估计值。请注意，这只是一个指南，而不是确切的缓存大小。effective_cache_size 不分配内存，相反，它通知优化器内核中可用的缓存级别。如果这个值设置得很低，那么查询规划器会自动限制使用某些索引，有时这些索引是有帮助的。所以总是把这个参数设置一个大的值。

shared_buffers

（推荐值：系统内存的25%）

默认值：128MB

在内存中读取或写入数据总是比在任何其他介质上更快。数据库服务器还需要内存来快速访问数据，无论是读访问还是写访问。在 PostgreSQL 中，这称为共享缓冲区并由参数shared_buffers控制。共享缓冲区所需的RAM量在 PostgreSQL实例的生命周期内始终被锁定。连接到数据库的所有后台服务器和用户进程都可以访问共享缓冲区。

推荐的值接近系统RAM 的25%

wal_buffers：

推荐值：16MB

默认值：16MB

预写日志 (WAL) 缓冲区也称为事务日志缓冲区，这是用于存储 WAL数据的内存分配量。此WAL 数据是有关实际数据更改的元数据信息，足以在数据库恢复操作期间重建实际数据。WAL 数据被写入持久位置中的一组物理文件，称为WAL 段或检查点段。

WAL 缓冲区内存分配由wal_buffers参数控制，它是从操作系统 RAM中分配的。尽管所有后台服务器和用户进程也可以访问此内存区域，但它不是共享缓冲区的一部分。WAL 缓冲区位于共享缓冲区的外部，与共享缓冲区相比非常小。WAL 数据在写入磁盘上的 WAL 段之前首先在 WAL 缓冲区中被修改（弄脏）。如果保留为默认设置，则分配的大小为共享缓冲区的 1/16。

work_mem：

推荐值：4MB，针对复杂查询可在会话级设置较大值

默认值：4MB

work_mem的值用于排序操作，并定义用于中间结果(如哈希表)和排序的最大内存量。

work_mem值不要设置得太高，因为当应用程序执行排序操作时，它可能会造成系统上可用内存的瓶颈。理想的做法是将work_mem的全局值设置为一个相对较低的值，然后针对复杂的排序在会话级别设置较大的work_mem值。

默认值：4MB

autovacuum_work_mem

推荐值：1GB

默认值：-1

我们可以在这里指定每个autovacuum worker 使用的最大内存量。

autovacuum 默认时使用maintenance_work_mem，建议分开设置一般情况下maintenance_work_mem维护都是单进程可以稍微设置大点，而autovacuum_work_mem受autovacuum_max_workers影响，设置1GB的维护工作内存足以一次处理大约 1.79 亿个死元组

这是每个autovacuum 工作进程使用的最大内存量，它由autovacuum_work_mem数据库参数控制。内存是从操作系统 RAM分配的，也受autovacuum_max_workers数据库参数的影响。所有这些参数设置仅在启用自动真空守护进程时起作用，否则，这些设置在其他上下文中运行时对VACUUM的行为没有影响。此内存组件不由任何其他后台服务器或用户进程共享。

maintenance_work_mem

推荐值：2GB

默认值：64MB

maintenance_work_mem指定用于例行维护任务(如VACUUM、CREATE INDEX等)的内存使用量。

与work_mem不同的是，数据库会话一次只能执行这些维护操作中的一个。因此，大多数系统不会并发运行许多这样的进程，因此，将这个值设置为比work_mem大得多通常是安全的，因为更大的可用内存可以提高清理和恢复数据库转储的性能。

temp_buffers

推荐值：8MB

默认：8MB

一个数据库可能有一个或多个临时表，这些临时表的数据块（页面）需要单独分配内存以进行处理。临时缓冲区通过利用由temp_buffers参数定义的一部分 RAM 来达到此目的. 临时缓冲区仅用于访问 用户会话中的临时表。内存中的临时缓冲区与大型排序和哈希表操作期间在pgsql_tmp 目录下创建的临时文件之间没有关系。

优化内存和缓存
调整PostgreSQL内存设置，如shared_buffers和effective_cache_size等参数。

要让PostgreSQL达到最佳性能，还要使用像pg_tune这样的PostgreSQL优化工具，可以根据系统的内存大小，I/O和网络性能，来调整PostgreSQL参数。例如常用的shared_buffers和effective_cache_size，它们是与访问文件并维护内存缓存有关的重要参数，可以控制PostgreSQL访问磁盘文件的频繁程度。除此之外，还可以根据测试结果做出改变，例如increasing wal_buffers to improve write performance，这有助于将PostgreSQL写入操作提升到最高水平。

最后，正确的PostgreSQL内核性能调优优化必须包含两个要素：PostgreSQL参数设置以及服务器的配置。因此，对于数据库管理员或性能调优者而言，正确的性能调优优化消耗大量时间，但它也是实现PostgreSQL最佳性能的必要之道。

举例来说，想要提升PostgreSQL的性能，可以使用以下代码：

ALTER SYSTEM SET shared_buffers = '1000MB';            

ALTER SYSTEM SET effective_cache_size = '2000MB';

一般shared_buffers 值应该被设为整个机器内存的 15% ~ 25%。effective_cache_size参数有操作系统和数据库评估多少内存可用磁盘缓存，PostgreSQL查询计划决定它是否固定在RAM中。索引扫描最有可能用于较高的值;如果该值为低将使用顺序扫描。建议将effecve_cache_size设置为机器总RAM的50%。

--查看所有数据库参数的值
show all;

例如：我们现在要修改 maintenance_work_mem 

show maintenance_work_mem;

--注意这里的设置不会改变postgresql.conf,只会改变postgresql.conf
ALTER SYSTEM SET maintenance_work_mem= 1048576;

--重启数据库
show maintenance_work_mem;

--取消postgresql.auto.conf的参数设置
ALTER SYSTEM SET maintenance_work_mem= default;

数据库参数优化总结

max_connections = 300 # (change requires restart)
unix_socket_directories = ‘.‘ # comma-separated list of directories
shared_buffers = 194GB # 尽量用数据库管理内存，减少双重缓存，提高使用效率
huge_pages = on # on, off, or try ，使用大页
work_mem = 256MB # min 64kB ， 减少外部文件排序的可能，提高效率
maintenance_work_mem = 2GB # min 1MB ， 加速建立索引
autovacuum_work_mem = 2GB # min 1MB, or -1 to use maintenance_work_mem ， 加速垃圾回收
dynamic_shared_memory_type = mmap # the default is the first option
vacuum_cost_delay = 0 # 0-100 milliseconds ， 垃圾回收不妥协，极限压力下，减少膨胀可能性
bgwriter_delay = 10ms # 10-10000ms between rounds ， 刷shared buffer脏页的进程调度间隔，尽量高频调度，减少用户进程申请不到内存而需要主动刷脏页的可能（导致RT升高）。
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round , 一次最多刷多少脏页
bgwriter_lru_multiplier = 10.0 # 0-10.0 multipler on buffers scanned/round 一次扫描多少个块，上次刷出脏页数量的倍数
effective_io_concurrency = 2 # 1-1000; 0 disables prefetching ， 执行节点为bitmap heap scan时，预读的块数。从而
wal_level = minimal # minimal, archive, hot_standby, or logical ， 如果现实环境，建议开启归档。
synchronous_commit = off # synchronization level; ， 异步提交
wal_sync_method = open_sync # the default is the first option ， 因为没有standby，所以写xlog选择一个支持O_DIRECT的fsync方法。
full_page_writes = off # recover from partial page writes ， 生产中，如果有增量备份和归档，可以关闭，提高性能。
wal_buffers = 1GB # min 32kB, -1 sets based on shared_buffers ，wal buffer大小，如果大量写wal buffer等待，则可以加大。
wal_writer_delay = 10ms # 1-10000 milliseconds wal buffer调度间隔，和bg writer delay类似。
commit_delay = 20 # range 0-100000, in microseconds ，分组提交的等待时间
commit_siblings = 9 # range 1-1000 , 有多少个事务同时进入提交阶段时，就触发分组提交。
checkpoint_timeout = 55min # range 30s-1h 时间控制的检查点间隔。
max_wal_size = 320GB # 2个检查点之间最多允许产生多少个XLOG文件
checkpoint_completion_target = 0.99 # checkpoint target duration, 0.0 - 1.0 ，平滑调度间隔，假设上一个检查点到现在这个检查点之间产生了100个XLOG，则这次检查点需要在产生100*checkpoint_completion_target个XLOG文件的过程中完成。PG会根据这些值来调度平滑检查点。
random_page_cost = 1.0 # same scale as above , 离散扫描的成本因子，本例使用的SSD IO能力足够好
effective_cache_size = 240GB # 可用的OS CACHE
log_destination = ‘csvlog‘ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
update_process_title = off
track_activities = off
autovacuum = on # Enable autovacuum subprocess? ‘on‘
autovacuum_max_workers = 4 # max number of autovacuum subprocesses ，允许同时有多少个垃圾回收工作进程。
autovacuum_naptime = 6s # time between autovacuum runs ， 自动垃圾回收探测进程的唤醒间隔
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for ， 垃圾回收不妥协

application.yml

spring:
  application:
    name: server-name
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://127.0.0.1:5432/tablename?serverTimezone=UTC&characterEncoding=utf-8&stringtype=unspecified
    username: postgres
    password: postgres
    # 连接池
    hikari:
      #连接池名
      pool-name: DateHikariCP
      #最小空闲连接数
      minimum-idle: 5
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 180000
      # 连接池最大连接数，默认是10
      maximum-pool-size: 100
      # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒，即30000
      connection-timeout: 50000
      connection-test-query: SELECT 1