最近生产上的 PostgreSQL 发现有几个进程一直长时间运行,通过 top 命令能看到很多对应 postgres 进程都跑到接近 100%,而且 TIME+ 时间很长,但是看不到对应的命令语句。
根据链接:https://www.cnblogs.com/liqiu/p/3817779.html ,找到了查看 CPU 执行时间过长是哪个语句。我这里当前使用的数据库版本是PostgreSQL 9.6.6。
查看占用 CPU 最多的几个 PostgreSQL 进程
# ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2, $3}'
查看占 CPU 过高的 PostgreSQL 进程的执行时间
postgres=# SELECT procpid, START, now() - START AS lap, current_query FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid, pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' and procpid=22687 ORDER BY lap DESC; procpid | start | lap | current_query ---------+------------------------------+-----------------+-------------------------------------------------------------------------------------------------------- 22687 | 2019-04-09 10:57:21.96082+08 | 00:36:07.920674 | EXPLAIN ANALYSE SELECT a.id, + | | | a.content, + | | | a.entity_id, + | | | a.entity_type, + | | | a.start_date, + | | | a.due_date, + | | | a.image_id, + | | | a.sg_status_list, + | | | a.project_id, + | | | a.cached_display_name, + | | | a.dependency_violation, + | | | a.sg_progress_info, + | | | a.inventory_date, + | | | a.step_id, + | | | a.time_logs_sum, + | | | a.est_in_mins, + | | | a.sg_production_bid_id, + | | | a.sg_production_bid_type, + | | | a.pinned, + | | | a.splits, + | | | a.filmstrip_image_id, + | | | c.cached_display_name AS _grp_c_cached_display_name, + | | | CASE WHEN c.id IS NULL THEN NULL ELSE 'Project' END AS _grp__project_, + | | | c.id AS _grp_c_id + | | | FROM tasks a + | | | LEFT JOIN display_name_caches b ON (b.entity_type = 'Project' AND b.entity_id = a.project_id)+ | | | LEFT JOIN projects c ON (c.id = a.project_id AND c.retirement_date IS NULL) + | | | WHERE ((a.sg_disabled = 'f' AND (a.project_id = ANY +
只需要在查询语句中的 procpid 输入对应查询到的进程 id 即可,我当前要查询的进程 id 号是 22687,当前上面的查询语句只是查询其中一个进程的结果。查询结果中几个字段的含义如下:
procpid:进程id
start:进程开始时间
lap:经过时间
current_query:执行中的sql
参考:https://www.cnblogs.com/liqiu/p/3817779.html