内存使用率高问题定位及处理方法

内存使用率说明

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

• 共享内存：主要用于数据的缓冲区、wal日志的缓冲区等，以便提高读写性能。除此之外也包含一些全局信息，如进程、锁信息等。

  shared_buffers参数的值决定初始申请共享内存的大小，RDS for PostgreSQL设置该参数开始值为系统物理内存的25%，该参数合理设置区间为25%~40%，如超过物理内存的 40%，就会发现缓冲的效果并不明显，这是因为PostgreSQL是运行在文件系统之上的，若文件系统也有缓存，将导致双缓存过多，造成负面影响。

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

指标异常说明（影响）

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

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

问题排查思路

PostgreSQL数据库中引起内存增长的原因一般是：连接/活跃连接数、慢SQL、TPS、长连接等增加。当内存利用率突增或不符合预期，可以按照以下思路进行排查：

图1 排查思路

排查方法

• 连接/活跃连接数
  • 通过管理控制台中的监控平台中内存使用率，连接数使用率，数据库连接数和活跃连接数监控项进行查看，查看目标时间段相关监控指标，观察指标是否同时增降。
  • 执行以下SQL，查看当前数据库最大连接数。

    show max_connections;

  • 执行以下SQL，查看当前数据库活跃连接数。

    select count(1) from pg_stat_activity where state <> 'idle';

  • 执行以下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。

• 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';

解决方法

• 连接/活跃连接数

  若连接数或空闲连接数过多，可执行如下SQL释放当前数据库的所有空闲连接，使用连接池或配置客户端连接超时参数及时释放空闲的连接。若活跃连接数过多，可减少业务并发量，或扩大内存规格。

  select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle';

• 慢SQL被大量执行

  定位到导致内存消耗增加的SQL，对SQL进行优化，或扩大内存规格。

• TPS事务数过高

  降低事务数，或扩大内存规格。

• 长连接数量多/连接存活时长久

  避免长连接，长连接的缓存可能较大，导致内存不足，建议定期释放长连接。

常见问题

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. 扩大内存规格。