PostgreSQL 查看哪些语句执行时间过长

最近生产上的 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

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注