PG效率监控的一些方式

发表于 本文字数： 1.2k 阅读时长 ≈ 1 分钟

1. 有效连接用户数

1
2
SELECT count(*) AS num_connections
FROM pg_stat_activity;

2. 执行时间最长的SQL

1
2
3
4
5
6
-- postgres13
SELECT queryid, query, (total_exec_time / 1000) AS total_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

3. 正在执行的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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;