RDS for PostgreSQL内存过载定位及处理建议

本章节的所有SQL操作都使用root用户执行。

生产数据库的内存要有一定的冗余，正常使用应保持在70%以下，若内存使用率长期高于阈值，建议考虑扩大内存规格。当内存使用率超过阈值并产生告警时，可能会产生以下问题。关于内存使用率的介绍详见内存使用率说明。

系统会频繁地进行内存交换，数据会在内存与磁盘之前大量交换，会消耗大量CPU，使数据库性能下降,影响数据的读取和写入速度等。
严重时可能会发生OOM，若发生OOM，数据库服务进程会发生重启，数据库会断开现有连接，且新连接无法接入。此时HA会重新拉起数据库，期间数据库会回放从上一次checkpoint到发OOM时之间产生的wal日志内容，以保证事务一致性。

PostgreSQL数据库中内存使用率主要是由共享内存和本地内存组成。

共享内存：主要用于数据的缓冲区、wal日志的缓冲区等，以便提高读写性能。除此之外也包含一些全局信息，如进程、锁信息等。
shared_buffers参数的值决定初始申请共享内存的大小，RDS for PostgreSQL设置该参数开始值为系统物理内存的25%，该参数合理设置区间为25%~40%，如超过物理内存的 40%，就会发现缓冲的效果并不明显，这是因为PostgreSQL是运行在文件系统之上的，若文件系统也有缓存，将导致双缓存过多，造成负面影响。

本地内存：后台服务会申请一些本地内存，以便暂存一些不需要全局存储的数据，一般分为以下几类：
- temp_buffers：设置每个会话用于访问临时表的本地缓冲区的最大块数量。
- work_mem：内部排序聚合操作和hash表在使用临时磁盘文件时使用的内存缓冲区。注意：每个排序操作都会消耗一个work_mem内存，并不是一个SQL消耗一个。
- maintenance_work_mem：维护操作使用的最大内存数。

内存过载的原因排查思路如图1。

图1 排查思路

连接/活跃连接数
- 通过管理控制台中的监控平台中内存使用率，连接数使用率，数据库连接数和活跃连接数监控项进行查看，查看目标时间段相关监控指标，观察指标是否同时增降。
- 使用root用户执行以下SQL，查看当前数据库最大连接数。
```
show max_connections;
```
- 使用root用户执行以下SQL，查看当前数据库活跃连接数。
```
select count(1) from pg_stat_activity where state <> 'idle';
```
- 使用root用户执行以下SQL，查看当前数据库空闲连接数。
```
select count(1) from pg_stat_activity where state = 'idle';
```

慢SQL
- 通过管理控制台中的监控平台中内存使用率，已执行1s的SQL数，已执行3s的SQL数和已执行5s的SQL数监控项进行查看，查看目标时间段相关监控指标，观察指标是否同时增降。
- 执行以下SQL，查看当前正在运行时长前三慢SQL（RDS for PostgreSQL 10及以上版本），检查返回的query字段中sql是否使用join、order等语法。
  select (extract(epoch from now() - query_start)) query_time, datname, usename, client_addr, wait_event, state, query from pg_stat_activity where state not like 'idle%' and query_start is not null and backend_type = 'client backend' and pid <> pg_backend_pid() order by 1 desc limit 3;
- 通过查询pg_stat_statements视图，获取统计信息并查询最耗共享内存的SQL，详见4。

长连接
- 执行以下SQL，查看正在运行时长前三的长连接（RDS for PostgreSQL 10及以上版本），其中返回的“conn_time”字段为连接存活时长，“query”字段为该进程执行的SQL。
```
select (extract(epoch from now()-backend_start)) conn_time, datname, pid, usename, client_addr, wait_event_type, wait_event, state, query from pg_stat_activity where backend_type = 'client backend' order by conn_time desc nulls last limit 3;
```
- 长连接会在数据库中会保持一个持久的连接状态，这个连接状态会维持一些缓存信息，如查询结果、事务信息、锁信息等等。如果长连接数量和连接时间较大，缓存的信息也会相应增多，从而占用更多的内存。需要进一步排查时，可通过上一条SQL返回的“query”字段查询pg_stat_statements视图，查看该SQL的共享内存消耗情况。
```
select userid::regrole, dbid, shared_blks_hit, shared_blks_dirtied from pg_stat_statements where query =  'query';
```

出现过载异常前的建议：

过载异常中的建议：

连接/活跃连接数
若连接数或空闲连接数过多，可使用root用户执行如下SQL释放当前数据库的所有空闲连接，使用连接池或配置客户端连接超时参数及时释放空闲的连接。若活跃连接数过多，可减少业务并发量，或扩大内存规格。您也可以kill不需要的会话，降低实例内存消耗，详见管理实时会话。

kill会话操作可能会导致业务断连，建议业务有重连机制，请谨慎操作。
```
select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle';
```
慢SQL被大量执行
定位到导致内存消耗增加的SQL，对SQL进行优化，或扩大内存规格。
TPS事务数过高
降低事务数，或扩大内存规格。
长连接数量多/连接存活时长久
避免长连接，长连接的缓存可能较大，导致内存不足，建议定期释放长连接。

过载异常后的复盘优化：

SQL层面优化
- 避免大内存消耗查询
  - 禁止一次性加载全表到内存（如大表JOIN、无LIMIT的全表扫描）。
  - 对大表分页查询使用书签式扫描（Keyset Pagination）替代OFFSET。
  - 拆分复杂查询为多个简单查询。
- 优化排序和聚合
  - 确保ORDER BY字段有索引，减少内存排序。
  - 使用窗口函数替代多次子查询。
架构层面优化
- 增加只读实例：分担当前实例的内存压力。
- 数据归档：将冷数据迁移至归档库，减少活跃数据量。
- 表分区：按时间分区表，便于清理历史分区释放内存。
容量规划
- 实例规格升级：选择更大内存规格。
- 至少预留30%余量：日常内存使用率控制在60%~70%。