參考資料有 EnterpriseDB 原廠提供的原始 Function，為針對單個 session 透過指定 pid 來查詢資源使用量，但原本的寫法只適合單個查詢，當連線數量很大的時候，想要抓出 Cpu 與 Memory 使用量高的連線時會查很久，用 pgbench 簡單測試當連線數量五百多時就會需要花上超過三十秒才能查出結果。
因此在原先 Function 基礎下做調整，下面的 Function 可用來查詢找出 CPU 與 Memory 使用量較高的連線，使用方法如下。
建立 Function
【PostgreSQL】
CREATE EXTENSION plpython3u;
CREATE OR REPLACE FUNCTION get_pid_cpu_mem()
RETURNS TABLE(pid INT, cpu_perc FLOAT, mem_perc FLOAT) AS $$
import subprocess
cmd = "top -b -n 1 -u postgres | grep postgres | awk '{print $1\",\"$9\",\"$10}'"
out = subprocess.check_output(cmd, shell=True, universal_newlines=True)
for line in out.splitlines():
parts = line.split(',')
pid = int(parts[0])
cpu_perc = float(parts[1])
mem_perc = float(parts[2])
yield (pid, cpu_perc, mem_perc)
$$ LANGUAGE plpython3u;【EDB 企業版資料庫】
CREATE EXTENSION plpython3u;
CREATE OR REPLACE FUNCTION get_pid_cpu_mem()
RETURNS TABLE(pid INT, cpu_perc FLOAT, mem_perc FLOAT) AS $$
import subprocess
cmd = "top -b -n 1 -u enterprisedb | grep enterpr| awk '{print $1\",\"$9\",\"$10}'"
out = subprocess.check_output(cmd, shell=True, universal_newlines=True)
for line in out.splitlines():
parts = line.split(',')
pid = int(parts[0])
cpu_perc = float(parts[1])
mem_perc = float(parts[2])
yield (pid, cpu_perc, mem_perc)
$$ LANGUAGE plpython3u;查詢
select a.pid, a.usename, a.application_name, --a.state, --case -- when state = 'idle' -- then a.state_change - a.query_start -- else now() - a.query_start -- end AS "query time", u.cpu_perc, u.mem_perc, a.query from pg_stat_activity a join get_pid_cpu_mem() u on a.pid = u.pid order by u.cpu_perc desc, u.mem_perc desc limit 30;
查詢結果
pid | usename | application_name | cpu_perc | mem_perc | query
-------+--------------+------------------+----------+----------+------------------------------------------------------------------------
11591 | enterprisedb | pgbench | 0.2 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -3219 WHERE tid = 4;
11516 | enterprisedb | pgbench | 0.1 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -253 WHERE tid = 7;
11618 | enterprisedb | pgbench | 0.1 | 0.2 | UPDATE pgbench_branches SET bbalance = bbalance + 2041 WHERE bid = 1;
11601 | enterprisedb | pgbench | 0.1 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 2054 WHERE tid = 3;
11892 | enterprisedb | pgbench | 0.1 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -1551 WHERE tid = 10;
11293 | enterprisedb | pgbench | 0.1 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 4165 WHERE tid = 1;
7908 | (NULL) | | 0 | 0.8 |
8165 | enterprisedb | psql | 0 | 0.4 | select +
| | | | | a.pid, +
| | | | | a.usename, +
| | | | | a.application_name, +
| | | | | --a.state, +
| | | | | --case +
| | | | | -- when state = 'idle' +
| | | | | -- then a.state_change - a.query_start +
| | | | | -- else now() - a.query_start +
| | | | | -- end AS "query time", +
| | | | | u.cpu_perc, +
| | | | | u.mem_perc, +
| | | | | a.query +
| | | | | from +
| | | | | pg_stat_activity a +
| | | | | join +
| | | | | get_pid_cpu_mem() u +
| | | | | on a.pid = u.pid +
| | | | | order by u.cpu_perc desc, u.mem_perc desc +
| | | | | limit 30;
7910 | (NULL) | | 0 | 0.4 |
11185 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 2337 WHERE tid = 9;
11191 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 3490 WHERE tid = 4;
11202 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -4240 WHERE tid = 9;
11192 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 29 WHERE tid = 5;
11193 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 3955 WHERE tid = 1;
11190 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + 3258 WHERE tid = 4;
11184 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -2178 WHERE tid = 5;
11189 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -215 WHERE tid = 9;
11199 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -4126 WHERE tid = 8;
11198 | enterprisedb | pgbench | 0 | 0.2 | UPDATE pgbench_tellers SET tbalance = tbalance + -2591 WHERE tid = 1
.....略參考資料：
https://www.enterprisedb.com/blog/monitor-cpu-and-memory-percentage-used-each-process-postgresqlppas-91
https://blog.gtwang.org/linux/ps-top-find-processes-by-cpu-memory-usage/
https://ravenonhill.blogspot.com/2018/03/pgsql10-session-cpu-mem-usage.html
https://www.omniwaresoft.com.tw/product-news/greenplum-news/differences-between-greenplum-and-postgresql/
