循序渐进丨openGauss / MogDB 数据库内存占用相关SQL
一、内存总体分布
数据库总体内存使用分布
select * from gs_total_memory_detail;当dynamic_used_memory大于max_dynamic_memory就会报内存不足；如果此时dynamic_used_memory小于max_dynamic_memory，而dynamic_peak_memory大于max_dynamic_memory表明曾经出现内存不足的情况；如果是other_used_memory较大，则只能通过更换Debug版本进一步定位。
如果dynamic_used_shrctx较大，则查询gs_shared_memory_detail视图，观察是哪个MemoryContext使用内存较多；如果dynamic_used_shrctx不大，则查询gs_session_memory_detail视图， 观察是哪个MemoryContext使用内存较多。
openGauss=# select * from gs_total_memory_detail;nodename | memorytype | memorymbytes----------+-------------------------+--------------primary | max_process_memory | 12288 GUC参数设置的进程可使用内存大小 primary | process_used_memory | 466 进程实际使用的内存大小，同操作系统res primary | max_dynamic_memory | 8088 MemoryContext能够使用的内存大小 primary | dynamic_used_memory | 606 MemoryContext实际使用的内存大小 primary | dynamic_peak_memory | 617 MemoryContext使用的内存峰值 primary | dynamic_used_shrctx | 331 SharedMemoryContext能够使用的内存大小 primary | dynamic_peak_shrctx | 331 SharedMemoryContext实际使用的内存大小 primary | max_backend_memory | 348 SharedMemoryContext使用的内存峰值 primary | backend_used_memory | 1 primary | max_shared_memory | 3339 shared_buffers + 元数据 primary | shared_used_memory | 136 进程使用的共享内存大小 primary | max_cstore_memory | 512 cstore_buffers primary | cstore_used_memory | 0 primary | max_sctpcomm_memory | 0 primary | sctpcomm_used_memory | 0 primary | sctpcomm_peak_memory | 0 primary | other_used_memory | 0 primary | gpu_max_dynamic_memory | 0 process_used_memory - dynamic_used_memory - shared_used_memory - cstore_used_memory primary | gpu_dynamic_used_memory | 0 primary | gpu_dynamic_peak_memory | 0 primary | pooler_conn_memory | 0 primary | pooler_freeconn_memory | 0 primary | storage_compress_memory | 0 primary | udf_reserved_memory | 0 (24 rows)
二、共享内存分布
共享内存的内存分布
SELECT contextname,pg_size_pretty(sum(totalsize)) totalmem,pg_size_pretty(sum(usedsize)) usedmem,count(*) countFROM gs_shared_memory_detailGROUP BY contextnameORDER BY totalmem DESC limit 20;
三、session级别，连接内存等
1.查看连接数分布
select state,count(*) from pg_stat_activity group by state;
2.各状态连接占用总内存情况
SELECT state,pg_size_pretty(sum(totalsize))FROM gs_session_memory_detail m, pg_stat_activity aWHERE substring_inner(sessid, position('.'IN sessid)+1)=a.sessionidgroup by state;
3.session内存使用详情
SELECT contextname,pg_size_pretty(sum(totalsize)),pg_size_pretty(sum(freesize))FROM gs_session_memory_detailGROUP BY contextnameORDER BY sum(totalsize) DESC limit 10;
4.session中cacheplan内存占用情况
SELECT sessid,pg_size_pretty(sum(totalsize)) sess_cacheplan_sum,pg_size_pretty(sum(usedsize)) sess_cacheplan_used,count(*) countFROM gs_session_memory_detailWHERE contextname LIKE '%CachedPlan%'GROUP BY sessidORDER BY 2 DESC limit 20;
5.session中syscache占用情况
SELECT sessid,contextname,pg_size_pretty(sum(totalsize)) sess_cacheplan_sum,pg_size_pretty(sum(usedsize)) sess_cacheplan_used, count(*) countFROM gs_session_memory_detailWHERE contextname LIKE '%SessionCacheMemory%'GROUP BY sessid, contextnameORDER BY 2 DESC limit 20;
6.根据线程资源的使用情况定位session连接
如下方式是先根据系统资源定位线程，然后根据线程找到对应 openGauss / MogDB 数据库里的连接。
（1）想要根据资源占用找连接，建议用top命令查看线程id：
top -H -p 数据库进程id查看到对应耗费资源较多的线程后，根据lwtid字段，找到对应的数据库连接：
SELECT pid,lwtid,state,now() - query_start query_duration,wait_event, queryFROM pg_stat_activity a, dbe_perf.thread_wait_status sWHERE a.pid=s.tidAND lwtid=xxx;
（2）也可用ps命令找到线程id，不过不方便看资源：
ps -T -p 数据库进程id如果针对访问数据库的连接，可以筛选条件加上worker。因为一般访问 openGauss / MogDB 的时候，连接的线程名为worker。
ps -T -p 进程id|grep worker然后再通过上述SQL查询线程对应的连接。
也可参考线程lwtid，详情参考https://www.modb.pro/db/448030
7.内存类型和query结合
SELECT substring_inner(sessid, position('.'
IN sessid)+1) AS sessid, sesstype, contextname, level, parent, pg_size_pretty(sum(totalsize)) AS totalsize, pg_size_pretty(sum(freesize)) AS freesize, pg_size_pretty(sum(usedsize)) AS usedsize, query
FROM gs_session_memory_detail m, pg_stat_activity a
WHERE substring_inner(sessid, position('.'
IN sessid)+1)=a.sessionid
GROUP BY sessid, sesstype, contextname, level, parent, query
ORDER BY totalsize desc;
8.查找占用内存较多的连接
SELECT a.pid,a.application_name,a.client_addr,a.datname,a.usename,pg_size_pretty(sum(usedsize)) AS usedsize,pg_size_pretty(sum(totalsize)) AS totalsize, a.state, a.queryFROM gs_session_memory_detail s, pg_stat_activity aWHERE substring_inner(sessid, position('.'IN sessid) +1)= a.sessionidAND usename <> 'omm'AND pid !=pg_backend_pid()GROUP BY pid, application_name, client_addr, datname, usename, state, queryORDER BY sum(totalsize) desc;
四、内存追踪功能（慎用）
openGauss 提供一个内存追踪的功能，可以追踪当前正在使用的内存上下文的申请位置，能够比较方便地用来定位内存泄漏的问题。其主要由下面两个函数组成。
DBE_PERF.track_memory_context(cxtname text): 开关。入参为需要追踪的上下文名称。
DBE_PERF.track_memory_context_derail(): 查看正在追踪的上下文的细节。
用完后DBE_PERF.track_memory_context(cxtname text)函数需要空值入参，关闭内存追踪功能。
使用示例如下：
//根据内存视图中查到比较大的、可能存在内存泄漏的context，开启内存追踪，并重新执行相关作业openGauss=# select dbe_perf.track_memory_context('SessionCacheMemoryContext');track_memory_context----------------------t(1 row)//查看上下文的申请信息，能看到是在哪个文件的哪一行申请了多少长度。openGauss=# select * from dbe_perf.track_memory_context_detail();context_name | file | line | size---------------------------+-------------+------+------SessionCacheMemoryContext | list.cpp | 104 | 16SessionCacheMemoryContext | tupdesc.cpp | 62 | 496SessionCacheMemoryContext | list.cpp | 107 | 24(3 rows)//关闭内存追踪功能。openGauss=# select dbe_perf. track_memory_context('');track_memory_context----------------------(1 row)
关于作者
阎书利，云和恩墨数据库技术顾问，PostgreSQL ACE，《快速掌握 PostgreSQL 版本新特性》一书副主编，中国PG分会认证讲师，PGfans 2021年度MVP，Gauss松鼠会2021年度优秀会员，拥有PGCM、OCP(MySQL)等十多项数据库认证，目前主要从事于 PostgreSQL、openGauss、MogDB 的运维以及数据库迁移改造工作。