Postgres 是如何管理内存的？如何内存调优？
本文档主要记录在排查 PostgreSQL 数据库内存在大批量写时占用过高导致查询接口超时问题过程中的学习内容。主要以内存管理为中心，学习 PostgreSQL 的相关核心架构。
PostgreSQL 进程结构
主进程 Potmaster
PostgreSQL 数据库的主要功能都集中在 Postmaster 进程中，该程序的安装目录一般为 bin 目录。如果你不确定，可以使用命令: which postgres 查看。
Postmaster 是整个数据库实例的总控进程，负责启动和关闭该数据库的实例。平时我们使用的 pg_ctl 命令其实也是运行 Postmaster 和 postgres 命令加上合适的参数来启动数据库，只是又做了一层包装，方便用户操作。
Postmaster 是数据库的第一个 Postgres 进程，其他的辅助子进程是 Postmaster fork 出来的。当用户与 PostgreSQL 建立连接的时候，实际上是先于 Postmaster 进程建立连接。此时客户端程序会发出身份验证消息给 Postmaster 进程，如果验证成功，Postmaster 会 fork 出子进程来为该连接服务。fork 出来的子进程被成为服务进程，可使用命令查看：
SELECT pid，usename, client_addr, client_port FROM pg_stat_activity;当某个服务进程出现问题时，Postmaster 主进程会自动完成系统恢复。恢复过程中会停掉所有的服务进程，然后进行数据库的一致性恢复，等恢复完成之后才能接受新的连接。
辅助进程
Logger 系统日志进程
在 postgresql.conf 文件中将参数 logging_collect 设置为：on 主进程才会启动 Logger 辅助进程。
该辅助进程会记录 Postmaster 主进程和所有服务进程以及其他辅助进程的 stderr 输出。在 postgresql.conf 文件中可以配置文件大小和存留时间。当文件大小或存留时间等限制条件达到时，Logger 就会关闭旧的日志文件，并创建新的日志。如果收集到装载配置文件的信号：SIGHUP，就会检查配置文件中的配置参数 log_directory 和 log_filename 与当时的配置是否相同，如果不相同则会使用新的配置。
BgWriter 后台写进程
该辅助进程是将共享内存 shared_buffers 中的脏页写入 OS 缓存，进而刷新到磁盘上的进程。该进程的刷新频率会直接影响到读写的效率。可用 bgwriter_ 为前缀的相关参数控制。
WalWriter 预写式日志写进程
WAL 是 Write Ahead Log 的缩写，即 预写式日志，又有被简称为 xlog。WalWriter 就是写 WAL 日志的进程。WAL 日志被保存在 pg_xlog 下。
预写式日志就是在修改数据之前就把这些修改的操作记录到磁盘中。好处是后续的更新实际数据的时候就不需要实时将数据持久化到文件。如果中途机器宕机或者进程异常退出导致一部分脏页没有来得及刷新到文件当中，数据库重启后可以通过读取 WAL 日志把最后一部分日志重新执行来恢复状态。
PgArch 归档进程
WAL 日志会循环使用，即早期的 WAL 日志会被覆盖。PgArch 归档进程会在 WAL 日志被覆盖之前将其备份出来。从 8.0 版本之后，PostgreSQL 采用 PITR（Point-In-Time-Recovery）技术。就是在对数据库进行过一次全量的备份之后，使用该技术可以将备份时间点之后的 WAL 日志通过归档进行备份。使用数据库的全量备份再加上后续的 WAL 日志就可以把数据库回滚到全量备份之后的任意时间点上。
AutoVaccum 自动清理进程
在 PostgreSQL 数据库中，对表的 DELETE 操作或者其他更新操作之后磁盘并不会立刻释放或者更新，只是会新增一行数据，原有数据会被标记为“删除”状态。只有在没有并发的其他事务读取这些旧数据的时候才会将其清除。
AutoVaccum 进程就是进行这种清除工作的。postgresql.conf 中有很多参数可以指定清除的频率和策略，但是默认是进行自动循环清除，所以叫做 Auto。
PgStat 统计数据收集进程
该进程主要进行数据的统计收集工作。收集的信息主要用于查询优化时的代价估算。信息包括在一个表和索引上进行了多少次的插入、更新、删除操作，磁盘读写的次数以及行的读次数等。系统表 pg_statistic 中可以查看这些收集信息。
PostgreSQL 内存管理
PostgreSQL 内存主要分为两大类：
- 本地存储空间：由每个后端进程分配供自己使用。
- 共享存储空间：被 PostgreSQL 服务中所有的进程所共享。
本地存储空间
在 PostgreSQL 中，每个后端进程分配本地内存用于查询处理。每个空间被划分为子空间，子空间的大小是固定的或可变的。
所有的子空间如下:
Work_mem
执行器使用此空间按 ORDER BY 和 DISTINCT 操作对元组进行排序。它还用于 merge-join 和 hash-join 操作来连接表。
Maintenance_work_mem
该参数用于某些类型的维护操作，例如：VACUUM，REINDEX 等。
VACUUM 是指回收资源。简单的说就是在执行 DELETE 删除操作以后，我们仅仅是为删除的记录打上标记，而并没有真正的从物理上删除，也没有释放空间。因此这部分被删除的记录虽热显示被删除了，但是其他新增记录依然不能占用其物理空间，对于这种空间的占用我们称其为 HWM（最高水位线）。
REINDEX 使用存储在索引表中的数据重建索引，替换旧的索引的副本。一般的原因是索引崩溃后恢复或者索引更改希望其生效。
Temp_buffers
执行器用这个空间保存一些临时表。一般保持默认数值。
共享存储空间
共享内存空间由 PostgreSQL 服务器在启动时分配。这个空间被划分为几个固定大小的子空间。
Share buffer pool
PostgreSQL 将表和索引中的页从持久存储加载到共享缓冲池中，然后直接对它们进行操作。
WAL buffer
PostgreSQL 支持 WAL（Write ahead log，提前写日志）机制，确保服务器故障后数据不会丢失。WAL 数据实际上是 PostgreSQL 中的事务日志，WAL 缓冲区是 WAL 数据写入持久存储之前的缓冲区域。
Commit Log
提交日志（CLOG）保存所有事务的状态，是并发控制机制的一部分。提交日志分配给共享内存，并在整个事务处理过程中使用。
PostgreSQL 定义了以下四种事务状态:
- IN_PROGRESS
- COMMITTED
- ABORTED
- SUB-COMMITTED
PostgreSQL 内存调优
Shared_buffers
此参数指定用于共享内存缓冲区的内存量。shared_buffers 参数决定为服务器缓存数据专用多少内存，相当于 Oracle 数据库中的 SGA。
shared_buffers 的默认值通常是 128 MB。这个参数的默认值非常低，因为在一些平台（如旧的 Solaris 版本和 SGI）上，拥有较大的值需要进行侵入性操作，比如重新编译内核。即使在现代 Linux 系统上，如果不先调整内核设置，内核也不可能允许将 shared_buffers 设置为超过 32 MB。该机制在 PostgreSQL 9.4 及后续版本中已经改变，因此内核设置将不必调整。
如果数据库服务器上有很高的负载，那么设置一个高值将提高性能。
如果您有一个具有 1 GB 或更多 RAM 的专用 DB 服务器，shared_buffers 配置参数的合理起始值是系统中内存的 25%。
设置 shared_buffers 的建议
- 在 2 GB 内存以下，将
shared_buffers的值设置为系统总内存的 20%。 - 在 32 GB 内存以下，将
shared_buffers的值设置为系统总内存的 25%。 - 32 GB 以上内存，将
shared_buffers的值设置为 8 GB。
Work_mem
此参数指定写入临时磁盘文件之前，内部排序操作和哈希表所使用的内存量。如果发生了很多复杂的排序，并且你有足够的内存，那么增加 work_mem 参数允许 PostgreSQL 进行更大的内存排序，这将比基于磁盘的等效排序更快。
注意，对于复杂的查询，许多排序或散列操作可能并行运行。在开始将数据写入临时文件之前，每个操作将被允许使用与该值指定的相同多的内存。有一种可能是几个会话可能同时执行这样的操作。因此，使用的总内存可能是 work_mem 参数值的许多倍。
在选择正确值时请记住这一点。排序操作用于 ORDER BY、DISTINCT 和合并连接。哈希表用于哈希连接、基于哈希的 IN 子查询处理和基于哈希的聚合。
参数 log_temp_files 可以用来记录排序、散列和临时文件，这在判断排序是否溢出到磁盘而不是内存中很有用。您可以使用 EXPLAIN ANALYZE 计划检查溢出到磁盘的排序。例如，在 EXPLAIN ANALYZE 的输出中，如果看到这样一行："Sort Method: external merge Disk: 7528kB"，那么至少 8 MB 的 work_mem 将把中间数据保存在内存中，并提高查询响应时间。
这个参数并不是总共消耗的内存，也不是一个进程分配内存的最大值，PostgreSQL 中每个 HASH 或者排序操作会被分配这么多内存。
如果有并发的 M 个进程，每个进程有 N 个 HASH 操作，则需要分配的内存是 M * N * work_mem。所以不要把这个设置得太大，容易 OOM。
默认值为 work_mem = 4 MB。
设置 work_mem 的建议
- 从低值开始：32-64 MB。
- 在日志中寻找"临时文件"行，设置为最大临时文件的 2-3 倍。
maintenance_work_mem
该参数指定维护操作（如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）所使用的最大内存量。由于数据库会话一次只能执行其中一个操作，而 PostgreSQL 安装中并没有许多操作同时运行，因此将 maintenance_work_mem 的值设置为明显大于 work_mem 是安全的。
设置较大的值可以提高清空和恢复数据库转储的性能。有必要记住，当 autovacuum 运行时，可能会分配最多 autovacuum_max_workers 倍的内存，因此注意不要将默认值设置得太高。
maintenance_work_mem 默认值为 64 MB。
设置 maintenance_work_mem 的建议
- 设置系统内存的 10%，最大 1 GB。
- 如果你有真空问题，也许你可以把它设置得更高。
Effective_cache_size
应该将 effecve_cache_size 设置为操作系统和数据库本身可用于磁盘缓存的内存的估计值。这是关于你期望在操作系统和 PostgreSQL 缓冲缓存中有多少可用内存的指南，而不是分配。
PostgreSQL 查询计划器使用这个值来确定它所考虑的计划是否适合 RAM。如果它设置得太低，索引可能不会以您期望的方式用于执行查询。由于大多数 Unix 系统在缓存时相当积极，专用数据库服务器上至少 50% 的可用 RAM 将充满缓存的数据。
默认值为 effecve_cache_size = 4 GB。
设置 effecve_cache_size 的建议
- 将该值设置为可用的文件系统缓存的数量。
- 如果您不知道，可以将该值设置为系统总内存的 50%。
Temp_buffers
此参数设置每个数据库会话使用的临时缓冲区的最大数量。会话本地缓冲区仅用于访问临时表。此参数的设置可以在单独的会话中更改，但只能在会话中第一次使用临时表之前更改。
PostgreSQL 数据库利用这个内存区域来保存每个会话的临时表，当连接关闭时，这些临时表将被清除。
默认值为 temp_buffer = 8 MB。
如何查看各个配置
首先进入 Postgres，用以下命令查看 conf 文件位置。
SELECT name,setting FROM pg_settings WHERE category='File Locations'; name | setting -------------------+--------------------------------------------------------- config_file | /usr/local/pgsql/data/postgresql.conf data_directory | /usr/local/pgsql/ external_pid_file | hba_file | /usr/local/pgsql/data/pg_hba.conf ident_file | /usr/local/pgsql/data/pg_ident.conf查看
postgresql.conf和postgresql.auto.conf。这个配置文件主要包含着一些通用的设置，算是最重要的配置文件。不过从 9.4 版本开始，PostgreSQL 引入了一个新的配置文件
postgresql.auto.conf在存在相同配置的情况下系统先执行auto.conf这个文件。换句话说
auto.conf配置文件优先级高于conf文件 。值得注意的是auto.conf这个文件必须在psql中使用alter system命令来修改，而conf可以直接在文本编辑器中修改。根据需求修改参数。
alter system set shared_buffers=131072; alter system set max_worker_processes=104;重启 PostgreSQL。
性能监控工具
我主要使用的监控工具是普罗米修斯，在 Grafana 上直接查看 Docker 的内存占用和 I/O 情况。但是该方法的缺点在于只能看到整体的内存占用，但是无法看到每个任务的状态和单独的进程大小以及内存使用情况，所以又引入了 pg_top 工具。
sudo apt-get install pgtoppg_top -U postgres -d xxxxxx -h 192.168.xx.xx -p xxxx -W -s 1 -o res -I -c所有的参数含义可查看：pg_top 参数和内容含义
警告
RES 的监控不准。只能监控到 SELECT 语句，INSERT 语句 RES 一直是 0 KB。
总结
没有一个总的参数可以直接限制 PostgreSQL 的内存总用量。有其他文章也谈到过这点，例如：limiting-the-total-memory-usage-of-postgresql。
但是我们可以根据自己查询的特点: 比如多 ORDER BY，多 SORT 等调整相应的参数来获得更好的性能。
