# 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排查思路   
![](https://support.huaweicloud.com/usermanual-rds-pg/zh-cn_image_0000002668469716.png "点击放大")
- **连接/活跃连接数**
  - 通过管理控制台中的监控平台中内存使用率，连接数使用率，数据库连接数和活跃连接数监控项进行查看，查看目标时间段相关监控指标，观察指标是否同时增降。
  
  - 使用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](https://support.huaweicloud.com/usermanual-rds-pg/rds_09_0066.html#rds_09_0066__zh-cn_topic_0000001805421885_li668711524544)。
   

- **TPS**
  通过管理控制台中的监控平台中内存使用率，TPS监控项进行查看，查看目标时间段相关监控指标，观察指标是否同时增降。
  

- **长连接**
  - 执行以下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';
    ```
    
   
 
#### 对业务的影响
- OOM Kill：进程被系统强制杀死，数据库服务中断。
- Swap使用：性能急剧下降，磁盘I/O成为瓶颈。
- 查询失败：内存分配失败导致查询异常终止。
- 连接中断：现有连接可能被意外断开。
- 缓存命中率下降：内存不足导致缓冲池命中率降低，磁盘I/O增加。
 
#### 处理建议
**出现过载异常前的建议：**
- 监控内存使用：关注监控大盘内存使用量，设置预警阈值。
- 控制并发：合理限制并发查询数量，避免内存瞬时峰值。
- 优化查询：减少一次性加载大量数据的查询（如**SELECT \***）。
- 适当[升级实例规格](https://support.huaweicloud.com/usermanual-rds-pg/rds_pg_scale_rds.html)：业务高峰期前确保内存裕度充足。
- 清理历史数据：定期归档或清理冷数据。
**过载异常中的建议：**
- **连接/活跃连接数**
  若连接数或空闲连接数过多，可使用root用户执行如下SQL释放当前数据库的所有空闲连接，使用连接池或配置客户端连接超时参数及时释放空闲的连接。若活跃连接数过多，可减少业务并发量，或扩大内存规格。您也可以kill不需要的会话，降低实例内存消耗，详见[管理实时会话](https://support.huaweicloud.com/usermanual-rds-pg/rds_pg_08_0026.html)。
  ![](https://support.huaweicloud.com/usermanual-rds-pg/public_sys-resources/note_3.0-zh-cn.png)
  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字段有索引，减少内存排序。
    
    - 使用窗口函数替代多次子查询。
     
   
- 架构层面优化
  - 增加只读实例：分担当前实例的内存压力。
  
  - 数据归档：将冷数据迁移至归档库，减少活跃数据量。
  
  - 表分区：按时间分区表，便于清理历史分区释放内存。
   
- 容量规划
  - 实例规格升级：[选择更大内存规格](https://support.huaweicloud.com/usermanual-rds-pg/rds_pg_scale_rds.html)。
  
  - 至少预留30%余量：日常内存使用率控制在60%\~70%。
   
 
#### 常见问题
Q：使用pg_dump时导出数据时，为什么内存使用率会升高，如何避免出现该问题？
A：使用pg_dump导出数据时，有一个进程会访问目标库中所有的表、索引等对象来获取结构数据，如果访问的表、索引等对象过大，可能会导致 relcache（关系表缓存）、catcache（系统目录表缓存）过大，且无法释放，从而导致存使用率升高，严重时会发生OOM。
建议执行pg_dump任务时：
1. 不要执行DDL操作。
2. 监控好慢SQL的指标，出现锁冲突时直接kill掉对应冲突的进程。
3. 低峰期执行pg_dump任务。
4. 将shared_buffers、work_mem参数继续降低到当前的1/2或1/4甚至更多，任务结束后回退修改的参数。
5. 扩大内存规格。