PostgreSQL数据库，找出阻塞进程的SQL语句

最新推荐文章于 2024-07-19 00:12:38 发布

东风破137

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量546

点赞数 9

文章标签：数据库 postgresql sql

本文链接：https://blog.csdn.net/qq_61920297/article/details/140018191

版权

结论：当数据库响应时间过长的时候，就有可能存在占用资源很多的SQL语句。

业务访问慢

1、问题说明
业务在运行过程中，发现访问变慢。
2、处理方案
通过top查询CPU使用率
$ top
(venv-patctl-1.8.0) [postgres@postgres ~]$ top
top - 17:00:51 up  2:09,  1 user,  load average: 0.00, 0.01, 0.05
Tasks: 251 total,   2 running, 249 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  3861364 total,  1098688 free,  1924652 used,   838024 buff/cache
KiB Swap:  3145724 total,  3145724 free,        0 used.   707804 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                           
     1 root      20   0  125600   4216   2640 S   0.0  0.1   0:01.44 systemd                                                                                           
     2 root      20   0       0      0      0 S   0.0  0.0   0:00.01 kthreadd                                                                                          
     4 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                                                                                      
     6 root      20   0       0      0      0 S   0.0  0.0   0:00.06 ksoftirqd/0                                                                                       
     7 root      rt   0       0      0      0 S   0.0  0.0   0:00.05 migration/0                                                                                       
     8 root      20   0       0      0      0 S   0.0  0.0   0:00.00 rcu_bh                                                                                            
     9 root      20   0       0      0      0 R   0.0  0.0   0:00.55 rcu_sched                                                                                         
    10 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 lru-add-drain                                                                          

通过数据库查询pg_stat_active字典表，排查是否由长SQL

--查看大于1s的SQL

实验开始：设置两个会话，使其产生阻塞（使一个会话中等待锁就行了）

查询选择那些状态改变时间超过 1 秒的连接


=# select * from pg_stat_activity where state<>'idle' and now()-state_change > interval '1 s' order by state_change ;

pg_stat_activity 是一个系统视图，它显示了当前所有活动的 PostgreSQL 连接和事务信息。

state <> 'idle' 筛选出当前状态不是空闲的活动连接，也就是正在执行查询或等待的连接。
now() - state_change > interval '1 s' 确保只选择那些状态改变时间超过 1 秒的连接。这可以用来查找已经持续一段时间的活动事务或连接。
ORDER BY state_change 按照状态改变时间升序排序，以便于观察最早发生状态改变的连接。

  {
    "datid": 5,
    "datname": "postgres",
    "pid": 4798,
    "leader_pid": null,
    "usesysid": 10,
    "usename": "fbase",
    "application_name": "psql",
    "client_addr": "192.168.6.108",
    "client_hostname": null,
    "client_port": 45349,
    "backend_start": "2024-06-27 02:42:05.047638 +00:00",
    "xact_start": "2024-06-27 02:46:04.058883 +00:00",
    "query_start": "2024-06-27 02:46:04.058883 +00:00",
    "state_change": "2024-06-27 02:46:04.058885 +00:00",
    "wait_event_type": "Lock",
    "wait_event": "transactionid",
    "state": "active",
    "backend_xid": null,
    "backend_xmin": "851",
    "query_id": -3613586159788325235,
    "query": "select * from test where id =3 for update;",
    "backend_type": "client backend"
  }

以下是PostgreSQL中与连接状态相关的字段及其说明：

pid: PostgreSQL 进程ID。
usename: 连接使用的用户名。
client_addr: 连接的客户端地址。
backend_start: 后端进程启动时间。
xact_start: 事务开始时间。
query_start: 查询开始时间。
state_change: 状态改变时间，指示连接进入当前状态的时间。
wait_event_type, wait_event: 当前连接等待的事件类型和具体事件。
state: 连接当前的状态，例如 “active” 表示活动中的事务或查询。
query: 此连接正在执行的查询语句，例如 “select * from test where id =3 for update;”。
backend_type: 后端连接的类型，例如 “client backend”。
–查看执行计划
执行explan SQL；查看SQL是否走索引，未走索引调整条件顺序或建立新的索引

–查看被阻塞SQL

=# select pid,pg_blocking_pids(pid),wait_event_type,query from pg_stat_activity where wait_event_type = 'Lock' and pid!=pg_backend_pid();

select: 这是 SQL 中的一个基础命令，用于从数据库表中检索数据。
pid: 是 PostgreSQL 的 pg_stat_activity 视图中的一个字段，表示进程 ID，也就是每个数据库会话的唯一标识符。
pg_blocking_pids(pid): 这是一个 PostgreSQL 函数，它接受一个 pid 参数并返回阻塞该 pid 的所有其他进程的 pid 列表。如果没有任何进程阻塞该 pid，则返回空数组。
wait_event_type: 是 pg_stat_activity 视图中的一个字段，表示会话正在等待的事件类型。在这个查询中，我们特别关注等待 ‘Lock’ 类型的会话。
query: 是 pg_stat_activity 视图中的一个字段，表示该会话当前正在执行的 SQL 语句（如果有的话）。
from pg_stat_activity: 指定从哪个视图或表中检索数据。pg_stat_activity 是一个系统视图，提供了关于当前数据库活动的信息。
where wait_event_type = ‘Lock’ and pid!=pg_backend_pid(): 这是一个过滤条件，它只选择那些正在等待 ‘Lock’ 事件且其 pid 不等于当前后端进程 ID（即执行此查询的会话）的会话。

[
  {
    "pid": 4798,
    "pg_blocking_pids": [4087],
    "wait_event_type": "Lock",
    "query": "select * from test where id =3 for update;"
  }
]

pid: 4798: 表示进程 ID 为 4798 的会话。
pg_blocking_pids: [4087]: 表示进程 ID 为 4087 的会话正在阻塞进程 ID 为 4798 的会话。
wait_event_type: Lock: 表示进程 ID 为 4798 的会话正在等待一个锁。
query: select * from test where id =3 for update;: 表示进程 ID 为 4798 的会话正在尝试执行这个 SQL 语句，但由于某种原因（例如其他会话已经锁定了相关的行或表），它无法立即完成。

解决方法：

=# select * from pg_locks where granted='f';
--解决：强制关闭该连接
=# select pg_terminate_backend(pid);

=# SELECT a.datname,a.pid,locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,cast(date_trunc('second',query_start) AS timestamp) AS query_start FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity a WHERE NOT pg_locks.pid = pg_backend_pid()AND pg_locks.pid=a.pid;