查询慢sql

原创已于 2024-02-23 17:48:45 修改 · 458 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2024-02-23 17:44:26 首次发布

本文介绍了当服务器数据库响应变慢时，如何通过SQL查询识别慢SQL并查看详细信息，包括PID、运行时间等。同时，提供了解除表锁和终止慢查询的方法，如使用`pg_cancel_backend`函数。

如果服务器数据库反应突然巨慢，估计是有慢sql

运行以下sql可以看到慢sql的一些详情PID，运行的sql语句。。。

SELECT
pid,
datname,
usename,
client_addr,
application_name,
STATE,
backend_start,
xact_start,
xact_stay,
query_start,
query_stay,
REPLACE ( query, chr( 10 ), ’ ’ ) AS query
FROM
(
SELECT
pgsa.pid AS pid,
pgsa.datname AS datname,
pgsa.usename AS usename,
pgsa.client_addr client_addr,
pgsa.application_name AS application_name,
pgsa.STATE AS STATE,
pgsa.backend_start AS backend_start,
pgsa.xact_start AS xact_start,
EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,
pgsa.query_start AS query_start,
EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,
pgsa.query AS query
FROM
pg_stat_activity AS pgsa
WHERE
pgsa.STATE != ‘idle’
AND pgsa.STATE != ‘idle in transaction’
AND pgsa.STATE != ‘idle in transaction (aborted)’
) idleconnections
ORDER BY
query_stay DESC

怎样停止正在执行的慢sql
SELECT pg_cancel_backend(进程id);

这个是查看表是否被锁

select oid from pg_class where relname=‘m_ss_kjcx_tech_inovate_talent’
select pid from pg_locks where relation=‘4384913’
–如果查询到了结果，表示该表被锁则需要释放锁定
select pg_cancel_backend(140650678843136)