Geekly articles weekly

在PostgreSQL中获取工作负载配置文件和等待历史的一种方法

继续文章“ 尝试为PostgreSQL创建ASH的类似物 ”。

本文将被考虑并在特定的查询和示例中显示-使用演示历史记录pg_stat_activity可以获得哪些有用的信息。
警告
由于该主题的新颖性和不完整的测试期限，因此该文章可能包含错误。 强烈鼓励和期望批评和评论。

输入数据


查看历史pg_stat_statements


pg_stat_history
CREATE TABLE pg_stat_history ( id SERIAL, snapshot_timestamp timestamp without time zone, database_id integer, dbid oid, userid oid, queryid bigint, query text, calls bigint, total_time double precision, min_time double precision, max_time double precision, mean_time double precision, stddev_time double precision, rows bigint, shared_blks_hit bigint, shared_blks_read bigint, shared_blks_dirtied bigint, shared_blks_written bigint, local_blks_hit bigint, local_blks_read bigint, local_blks_dirtied bigint, local_blks_written bigint, temp_blks_read bigint, temp_blks_written bigint, blk_read_time double precision, blk_write_time double precision, baseline_id integer );

每小时使用dblink到目标数据库填充该表。 该表中最有趣和最有用的列当然是queryid 。

pg_stat_activity查看历史


archive_pg_stat_activity
 CREATE TABLE archive_pg_stat_activity ( timepoint timestamp without time zone, datid oid, datname name, pid integer, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp without time zone, xact_start timestamp without time zone, query_start timestamp without time zone, state_change timestamp without time zone, wait_event_type text, wait_event text, state text, backend_xid xid, backend_xmin xid, query text, backend_type text, queryid bigint );

该表是按时钟划分的history_pg_stat_activity表（有关更多详细信息， 请参见pg_stat_statements + pg_stat_activity + loq_query = pg_ash？这是尝试为PostgreSQL创建ASH类似物的尝试。）

印记


集群CPU时间（系统+客户）


索取
 WITH t AS ( SELECT date_trunc('second', timepoint) FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND ( aa.wait_event_type IS NULL ) AND aa.state = 'active' ) SELECT count(*) INTO cpu_total FROM t ;

例子
 CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46

集群等待时间


索取
 WITH t AS ( SELECT date_trunc('second', timepoint) FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND ( aa.wait_event_type IS NOT NULL ) AND aa.state = 'active' ) SELECT count(*) INTO cpu_total FROM t ;

例子
 CLUSTER WAITINGS TIME : 30:12:49

pg_stat_statements总数


索取
  --TOTAL pg_stat SELECT SUM(calls) AS calls, SUM(total_time) AS total_time, SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,SUM(shared_blks_read) AS shared_blks_read , SUM(shared_blks_dirtied) AS shared_blks_dirtied,SUM(shared_blks_written) AS shared_blks_written , SUM(local_blks_hit) AS local_blks_hit , SUM(local_blks_read) AS local_blks_read , SUM(local_blks_dirtied) AS local_blks_dirtied , SUM(local_blks_written) AS local_blks_written, SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) temp_blks_written , SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time INTO pg_total_stat_history_rec FROM pg_stat_history WHERE snapshot_timestamp BETWEEN pg_stat_history_begin AND pg_stat_history_end AND queryid IS NULL;

SQL DBTIME-总查询运行时间


索取
 dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;

例子
 SQL DBTIME : 136:49:36

SQL CPU TIME执行查询所花费的CPU时间


索取
 WITH t AS ( SELECT date_trunc('second', timepoint) FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND ( aa.wait_event_type IS NULL ) AND backend_type = 'client backend' AND aa.state = 'active' ) SELECT count(*) INTO cpu_total FROM t ;

例子
 SQL CPU TIME : 27:40:15

SQL等待时间-查询的总等待时间


索取
 WITH t AS ( SELECT date_trunc('second', timepoint) FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND ( aa.wait_event_type IS NOT NULL ) AND aa.state = 'active' AND backend_type = 'client backend' ) SELECT count(*) INTO waiting_total FROM t ;

例子
 SQL WAITINGS TIME : 30:04:09

以下查询很简单，并且为了节省空间，省略了实现细节：

例子
 | SQL IOTIME : 19:44:50 | SQL READ TIME : 19:44:32 | SQL WRITE TIME : 00:00:17 | | SQL CALLS : 12188248 ------------------------------------------------------------- | SQL SHARED BLOCKS READS : 7997039120 | SQL SHARED BLOCKS HITS : 8868286092 | SQL SHARED BLOCKS HITS/READS % : 110.89 | SQL SHARED BLOCKS DIRTED : 419945 | SQL SHARED BLOCKS WRITTEN : 19857 | | SQL TEMPORARY BLOCKS READS : 7836169 | SQL TEMPORARY BLOCKS WRITTEN : 10683938

我们转到最有趣的部分

等待统计


客户过程中等待时间总计前10位的等待


索取
 SELECT wait_event_type , wait_event , get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * interval '1 hour') ,pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL GROUP BY wait_event_type, wait_event ORDER BY 3 DESC LIMIT 10

例子
  + ------------------------------------------------- -----------------------------------
 | 系统过程中等待时间总计前10位的等待
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  ＃|  wait_event_type |  wait_event | 持续时间
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 | 活动|  LogicalLauncherMain |  10:43:28
 |  2 | 活动|  AutoVacuumMain |  10:42:49
 |  3 | 活动|  WalWriterMain |  10:28:53
 |  4 | 活动|  CheckpointerMain |  10:23:50
 |  5 | 活动|  BgWriterMain |  09:11:59
 |  6 | 活动|  BgWriterHibernate |  01:37:46
 |  7 |  IO |  BufFileWrite |  00:02:35
 |  8 |  LWLock |  buffer_mapping |  00:01:54
 |  9 |  IO |  DataFileRead |  00:01:23
 |  10 |  IO |  WALWrite |  00:00:59
 + ----- + ------------------------------ + ------------ -------- + --------------------

客户过程中等待时间总计前10位的等待


索取
 SELECT wait_event_type , wait_event , get_clients_waiting_duration( wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL GROUP BY wait_event_type, wait_event ORDER BY 3 DESC LIMIT 10

例子
  + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  ＃|  wait_event_type |  wait_event | 持续时间  ％dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 | 锁|  transactionid |  08：16：47 |  6.05
 |  2 |  IO |  DataFileRead |  06：13：41 |  4.55
 |  3 | 超时|  PgSleep |  02：53：21 |  2.11
 |  4 |  LWLock |  buffer_mapping |  00：40：42 |  0.5
 |  5 |  LWLock |  buffer_io |  00：17：17 |  0.21
 |  6 |  IO |  BufFileWrite |  00：01：34 |  0.02
 |  7 | 锁| 元组|  00：01：32 |  0.02
 |  8 | 客户|  ClientRead |  00：01：19 |  0.02
 |  9 |  IO |  BufFileRead |  00：00：37 |  0.01
 |  10 |  LWLock |  buffer_content |  00：00：08 |  0
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------

系统过程中等待时间的等待类型


索取
 SELECT wait_event_type , get_system_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL GROUP BY wait_event_type ORDER BY 2 DESC

例子
  + ----- + ------------------------------ + ------------ --------
 |  ＃|  wait_event_type | 持续时间
 + ----- + ------------------------------ + ------------ --------
 |  1 | 活动|  53:08:45
 |  2 |  IO |  00:06:24
 |  3 |  LWLock |  00:03:02
 + ----- + ------------------------------ + ------------ --------

针对客户过程的总等待时间的等待类型


索取
 SELECT wait_event_type , get_clients_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL GROUP BY wait_event_type ORDER BY 2 DESC

例子
  + ----- + ------------------------------ + ------------ -------- + --------------------
 |  ＃|  wait_event_type | 持续时间  ％dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 | 锁|  08：18：19 |  6.07
 |  2 |  IO |  06：16：01 |  4.58
 |  3 | 超时|  02：53：21 |  2.11
 |  4 |  LWLock |  00：58：12 |  0.71
 |  5 | 客户|  00：01：19 |  0.02
 |  6 |  IPC |  00：00：04 |  0
 + ----- + ------------------------------ + ------------ -------- + --------------------

对系统过程和单个请求的期望持续时间。

等待系统过程


索取
 SELECT backend_type , datname , wait_event_type , wait_event , get_backend_type_waiting_duration( backend_type , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL GROUP BY backend_type , datname , wait_event_type , wait_event ORDER BY 5 DESC

例子
  + ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------
 |  ＃|  backend_type |  dbname |  wait_event_type |  wait_event | 持续时间
 + ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------
 |  1 | 逻辑复制启动器|  | 活动|  LogicalLauncherMain |  10:43:28
 |  2 | 自动真空发射器|  | 活动|  AutoVacuumMain |  10:42:49
 |  3 |  walwriter |  | 活动|  WalWriterMain |  10:28:53
 |  4 | 检查点|  | 活动|  CheckpointerMain |  10:23:50
 |  5 | 背景作家|  | 活动|  BgWriterMain |  09:11:59
 |  6 | 背景作家|  | 活动|  BgWriterHibernate |  01:37:46
 |  7 | 平行工人|  tdb1 |  IO |  BufFileWrite |  00:02:35
 |  8 | 平行工人|  tdb1 |  LWLock |  buffer_mapping |  00:01:41
 |  9 | 平行工人|  tdb1 |  IO |  DataFileRead |  00:01:22
 |  10 | 平行工人|  tdb1 |  IO |  BufFileRead |  00:00:59
 |  11 |  walwriter |  |  IO |  WALWrite |  00:00:57
 |  12 | 平行工人|  tdb1 |  LWLock |  buffer_io |  00:00:47
 |  13 | 真空吸尘器|  tdb1 |  LWLock |  buffer_mapping |  00:00:13
 |  14 | 背景作家|  |  IO |  DataFileWrite |  00:00:12
 |  15 | 检查点|  |  IO |  DataFileWrite |  00:00:11
 |  16 |  walwriter |  |  LWLock |  WALWriteLock |  00:00:09
 |  17 | 检查点|  |  LWLock |  WALWriteLock |  00:00:06
 |  18 | 背景作家|  |  LWLock |  WALWriteLock |  00:00:06
 |  19 |  walwriter |  |  IO |  WALInitWrite |  00:00:02
 |  20 | 真空吸尘器|  tdb1 |  LWLock |  WALWriteLock |  00:00:02
 |  21 |  walwriter |  |  IO |  WALInitSync |  00:00:02
 |  22 | 真空吸尘器|  tdb1 |  IO |  DataFileRead |  00:00:01
 |  23 | 检查点|  |  IO |  ControlFileSyncUpdate |  00:00:01
 |  24 | 背景作家|  |  IO |  WALWrite |  00:00:01
 |  25 | 背景作家|  |  IO |  DataFileFlush |  00:00:01
 |  26 | 检查点|  |  IO |  SLRUFlushSync |  00:00:01
 |  27 | 真空吸尘器|  tdb1 |  IO |  WALWrite |  00:00:01
 |  28 | 检查点|  |  IO |  DataFileSync |  00:00:01
 + ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------

SQL的等待-通过queryid对单个查询的期望


索取
 SELECT queryid , datname , wait_event_type , wait_event , get_query_waiting_duration( queryid , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL AND queryid IS NOT NULL GROUP BY queryid , datname , wait_event_type , wait_event ORDER BY 1 , 5 DESC

例子
  + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  ＃|  queryid |  dbname |  wait_event_type |  wait_event | 等待中| 合计
 |  |  |  |  |  | 持续时间 持续时间
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  1 |  -8247416849404883188 |  tdb1 | 客户|  ClientRead |  00：00：02 |
 |  2 |  -6572922443698419129 |  tdb1 | 客户|  ClientRead |  00：00：05 |
 |  3 |  -6572922443698419129 |  tdb1 |  IO |  DataFileRead |  00：00：01 |
 |  4 |  -5917408132400665328 |  tdb1 | 客户|  ClientRead |  00：00：04 |
 |  5 |  -4091009262735781873 |  tdb1 | 客户|  ClientRead |  00：00：03 |
 |  6 |  -1473395109729441239 |  tdb1 | 客户|  ClientRead |  00：00：01 |
 |  7 |  28942442626229688 |  tdb1 |  IO |  BufFileWrite |  00：01：34 |  00:46:06
 |  8 |  28942442626229688 |  tdb1 |  LWLock |  buffer_mapping |  00：01：05 |  00:46:06
 |  9 |  28942442626229688 |  tdb1 |  IO |  DataFileRead |  00：00：44 |  00:46:06
 |  10 |  28942442626229688 |  tdb1 |  IO |  BufFileRead |  00：00：37 |  00:46:06
 |  11 |  28942442626229688 |  tdb1 |  LWLock |  buffer_io |  00：00：35 |  00:46:06
 |  12 |  28942442626229688 |  tdb1 | 客户|  ClientRead |  00：00：05 |  00:46:06
 |  13 |  28942442626229688 |  tdb1 |  IPC |  MessageQueueReceive |  00：00：03 |  00:46:06
 |  14 |  28942442626229688 |  tdb1 |  IPC |  BgWorkerShutdown |  00：00：01 |  00:46:06
 |  15 |  389015618226997618 |  tdb1 | 锁|  transactionid |  03:55:09 |  04:14:15
 |  16 |  389015618226997618 |  tdb1 |  IO |  DataFileRead |  03：23：09 |  04:14:15
 |  17 |  389015618226997618 |  tdb1 |  LWLock |  buffer_mapping |  00：12：09 |  04:14:15
 |  18 |  389015618226997618 |  tdb1 |  LWLock |  buffer_io |  00：10：18 |  04:14:15
 |  19 |  389015618226997618 |  tdb1 | 锁| 元组|  00：00：35 |  04:14:15
 |  20 |  389015618226997618 |  tdb1 |  LWLock |  WALWriteLock |  00：00：02 |  04:14:15
 |  21 |  389015618226997618 |  tdb1 |  IO |  DataFileWrite |  00：00：01 |  04:14:15
 |  22 |  389015618226997618 |  tdb1 |  LWLock |  SyncScanLock |  00：00：01 |  04:14:15
 |  23 |  389015618226997618 |  tdb1 | 客户|  ClientRead |  00：00：01 |  04:14:15
 |  24 |  734234407411547467 |  tdb1 | 客户|  ClientRead |  00：00：11 |
 |  25 |  734234407411547467 |  tdb1 |  LWLock |  buffer_mapping |  00：00：05 |
 |  26 |  734234407411547467 |  tdb1 |  IO |  DataFileRead |  00：00：02 |
 |  27 |  1237430309438971376 |  tdb1 |  LWLock |  buffer_mapping |  00：02：18 |  02:45:40
 |  28 |  1237430309438971376 |  tdb1 |  IO |  DataFileRead |  00：00：27 |  02:45:40
 |  29 |  1237430309438971376 |  tdb1 | 客户|  ClientRead |  00：00：02 |  02:45:40
 |  30 |  2404820632950544954 |  tdb1 | 客户|  ClientRead |  00：00：01 |
 |  31 |  2515308626622579467 |  tdb1 | 客户|  ClientRead |  00：00：02 |
 |  32 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_mapping |  00：03：08 |  02:18:21
 |  33 |  4710212362688288619 |  tdb1 |  IO |  DataFileRead |  00：00：22 |  02:18:21
 |  34 |  4710212362688288619 |  tdb1 | 客户|  ClientRead |  00：00：06 |  02:18:21
 |  35 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_io |  00：00：02 |  02:18:21
 |  36 |  9150846928388977274 |  tdb1 |  IO |  DataFileRead |  00：01：19 |
 |  37 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_mapping |  00：00：34 |
 |  38 |  9150846928388977274 |  tdb1 | 客户|  ClientRead |  00：00：10 |
 |  39 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_io |  00：00：01 |
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------

CLIENT SQL STATICTICS-TOP查询


然而，没有给出再次接收的请求，这很琐碎并且节省了空间。

例子
  + ------------------------------------------------- -----------------------------------
 |  CLIENT SQL按经过时间排序
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 | 经过时间| 电话|  ％dbtime |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  04：14：15 |  19 |  3.1 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  02：45：40 |  746 |  2.02 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 |  02：18：21 |  749 |  1.69 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 |  00：46：06 |  375 |  0.56 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  CLIENT SQL按CPU时间排序
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  cpu时间| 电话|  ％dbtime | total_time |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  02：59：49 |  19 |  3.1 |  04：14：15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  01：10：12 |  746 |  2.02 |  02：45：40 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 |  00：56：15 |  749 |  1.69 |  02：18：21 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 |  00：15：35 |  375 |  0.56 |  00：46：06 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 用户I / O等待时间排序的CLIENT SQL
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  io_wait时间| 电话|  ％dbtime | total_time |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  03：23：10 |  19 |  3.1 |  04：14：15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  00：02：54 |  375 |  0.56 |  00：46：06 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 |  00：00：27 |  746 |  2.02 |  02：45：40 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 |  00：00：22 |  749 |  1.69 |  02：18：21 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 共享缓冲区排序的CLIENT SQL读取
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 缓冲区读取| 电话|  ％dbtime | total_time |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  1056388566 |  19 |  3.1 |  04：14：15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  11709251 |  375 |  0.56 |  00：46：06 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 |  3439004 |  746 |  2.02 |  02：45：40 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 |  3373330 |  749 |  1.69 |  02：18：21 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL按磁盘读取时间排序
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 阅读时间 电话|  ％dbtime | total_time |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  02：16：30 |  19 |  3.1 |  04：14：15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  00：04：50 |  375 |  0.56 |  00：46：06 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 |  00：01：10 |  749 |  1.69 |  02：18：21 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 |  00：00：57 |  746 |  2.02 |  02：45：40 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 客户SQL按执行顺序排序
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 | 电话| 行|  ％dbtime | total_time |  ％CPU |  IO百分比|  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  749 |  749 |  1.69 |  02：18：21 |  3.39 |  0.1 |  tdb1 |  4710212362688288619
 |  746 |  746 |  2.02 |  02：45：40 |  4.23 |  0.08 |  tdb1 |  1237430309438971376
 |  375 |  0 |  0.56 |  00：46：06 |  0.94 |  0.41 |  tdb1 |  28942442626229688
 |  19 |  19 |  3.1 |  04：14：15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------

总结


使用提交的请求和生成的报告，您可以获得更完整的图景，用于分析和解决单个请求以及整个集群整体的性能下降问题。

发展历程


到目前为止，开发计划如下：

  • 使用锁定历史记录来补充报告。 请求正在测试中，将很快提交。
  • 使用TimescaleDB扩展来存储pg_stat_activity和pg_locks历史记录。
  • 在github上准备一个批处理解决方案，以在生产基地上进行大规模部署。

待续...

Source: https://habr.com/ru/post/zh-CN467575/

More articles:


All Articles