磐维数据库会话信息查询

一、查看会话信息

1、活动会话查询

mydb=> SELECT datname, usename, state, pid, client_addr 
mydb-> FROM pg_stat_activity 
mydb-> WHERE state != 'idle';
 datname  | usename | state  |      pid       | client_addr 
----------+---------+--------+----------------+-------------
 postgres | omm     | active | 22514719774464 | 
 postgres | omm     | active | 22514602305280 | 
 postgres | omm     | active | 22514504759040 | 
 postgres | omm     | active | 22514389341952 | 
 mydb     | cy      | active | 22513799460608 | 
(5 rows)

2、会话连接数限制

mydb=> SHOW max_connections; --查看最大连接数
 max_connections 
-----------------
 200
(1 row)

mydb=> SELECT COUNT(*) FROM pg_stat_activity; --查看当前连接数
 count 
-------
    12
(1 row)

--查看用户级别连接限制
mydb=> SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname='cy'; 
 rolname | rolconnlimit 
---------+--------------
 cy      |           -1 

--查看数据库级别连接限制
mydb=> SELECT datname, datconnlimit FROM pg_database WHERE datname='mydb';
 datname | datconnlimit 
---------+--------------
 mydb    |           -1
(1 row)

二、慢SQL分析

1、实时长事务查询：

mydb=> SELECT datname, usename, query_start, state, query 
mydb-> FROM pg_stat_activity 
mydb-> WHERE now() - query_start > interval '10 second';
 datname  | usename |          query_start          | state  |                    query                    
----------+---------+-------------------------------+--------+---------------------------------------------
 mydb     | cy      | 2025-04-08 18:57:08.827075+08 | idle   | update emp set comm='200' where empno=7369;
 postgres | omm     | 2025-04-08 18:53:44.207585+08 | active | WLM fetch collect info from data nodes
(2 rows)

mydb=> SELECT datname, usename, query_start, state, query 
mydb-> FROM pg_stat_activity 
mydb-> WHERE now() - query_start > interval '10 second' AND state != 'idle';
 datname  | usename |          query_start          | state  |                 query                  
----------+---------+-------------------------------+--------+----------------------------------------
 postgres | omm     | 2025-04-08 18:53:44.207585+08 | active | WLM fetch collect info from data nodes
(1 row)

2、历史慢SQL诊断：

需开启参数：

openGauss=# show track_stmt_stat_level  --开启全量SQL跟踪
openGauss-# ;
 track_stmt_stat_level 
-----------------------
 OFF,L0
(1 row)

openGauss=# show log_min_duration_statement; --设置慢SQL阈值(ms)
 log_min_duration_statement 
----------------------------
 3s
(1 row)

openGauss=#

通过WDR报告或以下视图查询：

openGauss=# SELECT db_name,user_name,application_name,session_id,query FROM dbe_perf.statement_history where execution_time>5000 and db_name='mydb' and user_name='cy';
-[ RECORD 1 ]----+---------------------------------
db_name          | mydb
user_name        | cy
application_name | gsql
session_id       | 22626607822592
query            | insert into t1 select * from t1;
-[ RECORD 2 ]----+---------------------------------
db_name          | mydb
user_name        | cy
application_name | gsql
session_id       | 22626607822592
query            | insert into t1 select * from t1;
-[ RECORD 3 ]----+---------------------------------
db_name          | mydb
user_name        | cy
application_name | gsql
session_id       | 22626607822592
query            | insert into t1 select * from t1;

三、阻塞分析

查询被阻塞会话：

mydb=> SELECT
mydb->     a.datname,
mydb->     a.sessionid,
mydb->     a.usename,
mydb->     a.query_start,
mydb->     a.waiting,
mydb->     a.state,
mydb->     a.query_id,
mydb->     a.query,
mydb->     b.thread_name,
mydb->     b.lwtid,
mydb->     b.wait_status,
mydb->     b.wait_event,
mydb->     b.lockmode,
mydb->     b.block_sessionid  
mydb-> FROM 
mydb->     pg_thread_wait_status AS b
mydb-> INNER JOIN 
mydb->     pg_stat_activity AS a 
mydb->     ON a.sessionid = b.sessionid
mydb-> WHERE 
mydb->     a.datname = 'mydb';
-[ RECORD 1 ]---+------------------------------------------
datname         | mydb
sessionid       | 22626467116800
usename         | cy
query_start     | 2025-04-08 19:29:30.206526+08
waiting         | t
state           | active
query_id        | 10414574138294769
query           | update emp set comm=500 where empno=7499;
thread_name     | gsql
lwtid           | 90608
wait_status     | acquire lock
wait_event      | transactionid
lockmode        | ShareLock
block_sessionid | 22626607822592
-[ RECORD 2 ]---+------------------------------------------
datname         | mydb
sessionid       | 22626607822592
usename         | cy
query_start     | 2025-04-08 19:25:25.695575+08
waiting         | f
state           | idle in transaction
query_id        | 0
query           | update emp set comm=500 where empno=7499;
thread_name     | gsql
lwtid           | 78300
wait_status     | wait cmd
wait_event      | wait cmd
lockmode        | 
block_sessionid | 
-[ RECORD 3 ]---+------------------------------------------

查看锁信息：

mydb=> SELECT locktype, relation::regclass, mode, pid, granted 
mydb-> FROM pg_locks 
mydb-> WHERE relation = 'emp'::regclass;
 locktype | relation |       mode       |      pid       | granted 
----------+----------+------------------+----------------+---------
 relation | emp      | RowExclusiveLock | 22626523805440 | t
 relation | emp      | RowExclusiveLock | 22626607822592 | t
 tuple    | emp      | ExclusiveLock    | 22626607822592 | t
(3 rows)

四、等待事件分析

线程级等待详情

mydb=> SELECT  wait_status,query_id,sessionid  FROM pg_thread_wait_status where db_name='mydb';
 wait_status  |     query_id      |   sessionid    
--------------+-------------------+----------------
 wait cmd     |                 0 | 22626523805440
 acquire lock | 10414574138294555 | 22626607822592
 none         | 10414574138294569 | 22626437691136
(3 rows)

查询历史慢sql

openGauss=# SELECT distinct db_name, substring(query, 1, 80), query_plan, start_time 
openGauss-# FROM dbe_perf.statement_history 
openGauss-# ORDER BY start_time DESC 
openGauss-# LIMIT 1;
 db_name |              substring               | query_plan |          start_time           
---------+--------------------------------------+------------+-------------------------------
 mydb    | update emp set comm=? where empno=?; |            | 2025-04-08 19:29:30.206528+08
(1 row)

五、终止会话

mydb=> SELECT pg_terminate_backend(22626607822592);
 pg_terminate_backend 
----------------------
 t
(1 row)

-- 终止指定 PID 的会话
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE usename = 'cy';

-- 自动断开所有非超级用户的会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE usename NOT IN (SELECT usename FROM pg_user WHERE usesuper);

openGauss=# SELECT pg_terminate_backend(pid)
openGauss-# FROM pg_stat_activity 
openGauss-# WHERE usename NOT IN (SELECT usename FROM pg_user WHERE usesuper);
 pg_terminate_backend 
----------------------
 t
 t
(2 rows)