查看 PGSQL10 每個 Session 的 CPU/Mem 用量

最近被問到 Postgres 如何查看吃比較多 CPU 的 Session。不過目前 Postgres 在內建狀態這塊還在努力的樣子...因此找了一下替代方案，發現 EnterpriseDB 曾經有過相關教學。


通常來說，需要自己在工作管理員（ps 指令）查找 CPU 用量偏高的 postgres Session 的 pid，然後再到 psql 底下查找相對應 pid 的 pg_stat_activity 系統表內容，以便查找確切的 Query。不過 EnterpriseDB 原廠以前有提供以下方案，讓你可以直接在資料庫裡面完成這兩步驟！原文連結在參考資料內。以下就把內容的 Script 記下來留存～

CREATE EXTENSION plperlu;

CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float)
as
$$
  my $ps = "ps aux";
  my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
  my $cmd = $ps."|".$awk;
  $output = `$cmd 2>&1`;
  @output = split(/[\n\r]+/,$output);
  foreach $out (@output)
  {
    my @line = split(/:/,$out);
    return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
    return undef;
  }
   return;
 $$ language plperlu;

postgres=# select a.pid,usename,application_name, u.cpu_perc, u.mem_perc, query from pg_stat_activity a, get_pid_cpu_mem(a.pid) u;
 pid  | usename  | application_name | cpu_perc | mem_perc |                                                          query                                                          
------+----------+------------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------
 1255 | postgres |                  |        0 |      0.4 |
 1253 |          |                  |        0 |      0.5 |
 1290 | postgres | psql             |        0 |      2.1 | select a.pid,usename,application_name, u.cpu_perc, u.mem_perc, query from pg_stat_activity a, get_pid_cpu_mem(a.pid) u;
 1251 |          |                  |        0 |      0.5 |
 1250 |          |                  |        0 |      0.3 |
 1252 |          |                  |        0 |        1 |
(6 rows)

以下是隨便跑一段資料輸入的 Scipt，然後過程中使用 psql Meta-Command \watch 查看：
塞資料

create table tt2 as select * from pg_stat_activity, pg_class,pg_attribute;

在 PGSQL 9.6 之後，psql 指令提供了一個 \watch 的 Meta-Command，可以像 Linux Shell 底下的 watch 指令，定時重刷指令。以下是查看過程的某一片段，呈現了上述指令執行完畢後，自動啟動的 VACUUM 作業的狀況。

postgres=# select a.pid,usename,application_name, u.cpu_perc, u.mem_perc, query from pg_stat_activity a, get_pid_cpu_mem(a.pid) u; \watch 2
...
過程中的某片段
 pid  | usename  | application_name | cpu_perc | mem_perc |                                                          query                                                          
------+----------+------------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------
 1255 | postgres |                  |        0 |      0.4 |
 1253 |          |                  |        0 |      0.4 |
 1290 | postgres | psql             |        0 |      1.6 | select a.pid,usename,application_name, u.cpu_perc, u.mem_perc, query from pg_stat_activity a, get_pid_cpu_mem(a.pid) u;
 1450 | postgres | psql             |      5.4 |      4.9 | create table tt2 as select * from pg_stat_activity, pg_class,pg_attribute;
 3250 |          |                  |      1.4 |      4.3 | autovacuum: ANALYZE public.tt2
 1251 |          |                  |        0 |      0.5 |
 1250 |          |                  |        0 |      1.7 |
 1252 |          |                  |        0 |        1 |
(8 rows)

繼續跳下去...

希望這篇更新對你有幫助～

參考資料
postgresql - Troubleshooting high CPU usage from postgres and postmaster services? - Database Administrators Stack Exchange
Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1 | EnterpriseDB Blog