PostgreSQL 内存使用率高时怎么办

从内存读取总是比从磁盘读取性能更高，因此对于所有数据库技术，你都希望使用尽可能多的内存。如果你对配置不是特别确定，或者配置错误，这可能会产生高内存利用率甚至oom问题。

在本博客中，我们将了解如何检查PostgreSQL内存利用率以及调整它时应考虑的参数。为此，让我们先看一下PostgreSQL架构的概述。

PostgreSQL 架构

PostgreSQL的包括三个部分：进程、内存和磁盘。

内存可以分为两类：

    Local Memory：它由每个后端进程加载（每个客户端连接对应一个后端进程），供自己用于查询处理。它分为几个子区域：

• Work mem：工作内存用于通过ORDER BY 和 DISTINCT 操作对元组进行排序，以及连接表。

• Maintenance work mem：某些类型的维护操作使用此区域。例如， VACUUM，如果你没有指定 autovacuum_work_mem。

• Temp buffers：用于存储临时表。

  
  

Shared Memory：由PostgreSQL服务在启动时分配，供所有进程使用。它分为几个子区域：

• Shared buffer pool：PostgreSQL 从磁盘加载包含表和索引的页面，直接从内存读取数据，减少磁盘访问。

• WAL 缓冲区：WAL 数据是 PostgreSQL 中的事务日志，包含数据库中的更改。每隔一定时间会将WAL 缓冲区中数据写入磁盘的WAL 文件中。这个预定义时间称为检查点。避免在服务器发生故障时丢失数据。

• Commit log：保存所有事务的状态，用于并发控制。

使用“top”命令

此命令可以查看消耗内存过多的进程。 

当您确认 是PostgreSQL导致时，下一步就是检查原因。

使用 PostgreSQL 日志

当内存不足时会看到如下消息：

Out of memory: Kill process 1161 (postgres) score 366 or sacrifice child
复制

或者当数据库出现一些异常行为时有其他一些错误信息

FATAL:  password authentication failed for user "username"
ERROR:  duplicate key value violates unique constraint "sbtest21_pkey"
ERROR:  deadlock detected
复制

因此，日志可用于检测此类问题。你可以通过解析日志文件来自动执行监控，以查找诸如“ FATAL ”、“ ERROR ”或“ Kill ”之类的信息，在发生这种情况时收到警报。

使用 Pg_top

如果已经知道PostgreSQL进程的内存利用率很高，但日志没有看出什么问题，还可以使用另一个工具 pg_top 。

这个工具类似top工具，但它是专门针对 PostgreSQL 的。因此，使用它可以获得数据库服务的更多详细信息，甚至可以终止查询，或者在检测到错误时运行查询计划。

如果仍然无法检测到任何错误，并且数据库仍在使用大量内存，此时可能需要检查下数据库配置。

要检查哪些配置参数

如果一切正常，但仍然存在高利用率问题，则应检查配置以确认是否正确。因此，在这种情况下，应该考虑以下参数。

shared_buffers

这是数据库服务用于共享内存缓冲区的大小。如果这个值太低，数据库会使用更多的磁盘，这会导致更慢，但如果它太高，可能会产生高内存利用率。根据文档，如果您有一个具有 1GB 或更大 RAM 的专用数据库服务器，shared_buffers 的合理起始值是系统内存的 25%。

work_mem

它指定了ORDER BY、DISTINCT和JOIN在写入磁盘上的临时文件之前将使用的内存量。与shared_buffers一样，如果我们将此参数配置得太低，我们可以有更多的操作进入磁盘，但太高对内存使用来说是危险的。默认值为 4 MB。

max_connections

Work_mem 也与 max_connections 值密切相关，因为每个连接将同时执行这些操作，并且每个操作在开始将数据写入临时文件之前将被允许使用work_mem指定的内存。这个参数决定了我们数据库的最大并发连接数，如果我们配置了大量的连接，默认值为 100。

temp_buffers

临时缓冲区用于存储每个会话中使用的临时表。此参数设置此任务的最大内存量。默认值为 8 MB。

maintenance_work_mem

这是诸如清空、添加索引或外键之类的操作可以消耗的最大内存。在一个会话中只能运行一个这种类型的操作，并且在系统中同时运行多个这些操作并不是最常见的事情。默认值为 64 MB。

autovacuum_work_mem

默认使用maintenance_work_mem，但我们可以使用此参数将其分开。我们可以在此处指定每个 autovacuum worker 使用的最大内存量。

wal_buffers

用于尚未写入磁盘的 WAL 数据的共享内存量。默认设置为 shared_buffers 的 3%，但不小于 64kB，也不大于一个 WAL 段的大小，通常为 16MB。 

结论

内存利用率高有多种原因，查找问题原因可能是一项耗时的工作。在这篇博客中，我们提到了检查PostgreSQL内存利用率的不同方法，以及应该考虑哪些参数来调整它，以避免内存使用过多。