问题陈述
要优化PostgreSQL查询,它确实需要具有分析活动历史记录的能力,特别是-期望,锁,表统计信息。
可用选项
历史负载分析工具或“ AWR for Postgres” :一个非常有趣的解决方案,但是没有pg_stat_activity和pg_locks的历史记录。
Pgsentinel扩展名 :
“
所有累积的信息仅存储在RAM中,消耗的内存量由最后存储的记录数来调节。
添加了queryid字段-与pg_stat_statements扩展名相同的queryid(需要初步安装)。 ”
这当然会很有帮助,但是非常讨厌的是第一段,“
所有累积的信息仅存储在RAM中 ”,即 我们会对目标群体产生影响。 此外,没有锁定历史记录和表统计信息。 即 一般而言,解决方案是不完整的:“
尚无用于安装的现成软件包。 建议下载源代码并自己构建库。 首先,您需要为服务器安装“ devel”软件包,并在PATH变量中写入pg_config的路径。 ”
总的来说-大惊小怪,在严重的生产数据库的情况下,也许将无法对服务器做任何事情。 同样,您需要提出自己的想法。
警告
由于该主题的新颖性和测试期的不完整,本文主要是作为指导,而不是作为一组摘要和中间结果。
稍后将分部分准备更详细的材料。
概述解决方案要求
有必要开发一种工具来存储:
pg_stat_activity查看历史使用pg_locks视图的会话锁定历史记录决策要求是最大程度地减少对目标数据库的影响。
通常的想法是,数据收集代理不是在目标数据库中启动的,而是在监视数据库中作为系统服务启动的。 是的,可能会丢失一些数据,但这对于报告并不重要,但是就内存和磁盘空间而言,对目标数据库没有影响。 并且在使用连接池的情况下,对用户进程的影响最小。
实施阶段
1.服务表
为了存储表,使用了单独的方案,以免使所用主表的分析复杂化。
DROP SCHEMA IF EXISTS activity_hist ; CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
重要提示:该方案不是在目标数据库中创建的,而是在监视数据库中创建的。pg_stat_activity查看历史
使用该表存储pg_stat_activity视图的当前快照
activity_hist.history_pg_stat_activity: 为了加快插入速度-没有索引或限制。
要直接存储历史记录,请使用分区表:
activity_hist.archive_pg_stat_activity: DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity; CREATE TABLE activity_hist.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 with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text , queryid bigint ) PARTITION BY RANGE (timepoint);
由于在这种情况下对插入速度没有要求,因此已创建了一些索引来加快报告速度。
会话锁定历史
要存储当前的会话锁快照,请使用表:
activity_hist.history_locking: 另外,为了加快插入速度-没有索引或限制。
要直接存储历史记录,请使用分区表:
activity_hist.archive_locking: DROP TABLE IF EXISTS activity_hist.archive_locking; CREATE TABLE activity_hist.archive_locking ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) PARTITION BY RANGE (timepoint);
由于在这种情况下对插入速度没有要求,因此已创建了一些索引来加快报告速度。
2.填写当前历史记录
为了直接捕获视图的快照,使用运行bpg脚本的plgsql函数。
plpgsql dblink函数访问目标数据库中的视图,并将行插入到监视数据库中的服务表中。
get_current_activity.sql CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$ DECLARE database_rec record; dblink_str text ; BEGIN EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'|| ' user='||current_s_name||' password='||current_s_pass|| ' '')'; -------------------------------------------------------------------- --GET pg_stat_activity stats INSERT INTO activity_hist.history_pg_stat_activity ( SELECT * FROM dblink('LINK1', 'SELECT now() , datid , datname , pid , usesysid , usename , application_name , client_addr , client_hostname , client_port , backend_start , xact_start , query_start , state_change , wait_event_type , wait_event , state , backend_xid , backend_xmin , query , backend_type FROM pg_stat_activity ') AS t ( 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 with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text ) ); --------------------------------------- --ACTIVITY_HIST.HISTORY_LOCKING INSERT INTO activity_hist.history_locking ( SELECT * FROM dblink('LINK1', 'SELECT now() , lock.locktype, lock.relation, lock.mode, lock.transactionid as tid, lock.virtualtransaction as vtid, lock.pid, pg_blocking_pids(lock.pid), lock.granted FROM pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database WHERE NOT lock.pid = pg_backend_pid() ') AS t ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) ); PERFORM dblink_disconnect('LINK1'); RETURN TRUE ; END $$ LANGUAGE plpgsql;
为了收集视图的快照,使用了systemd服务和两个脚本:
pg_current_activity.service pg_current_activity.timer 分配脚本权限:
#chmod 755 pg_current_activity.timer
#chmod 755 pg_current_activity.service
我们启动服务:
#systemctl守护进程重新加载
#systemctl start pg_current_activity.service
因此,以每秒钟一次的镜头形式收集表演历史。 当然,如果一切都保持原样,桌子的尺寸将很快增加,并且几乎不可能进行生产性工作。
有必要组织数据归档。
3,存档历史
对于归档,使用分区表*。
每小时创建新的部分,同时删除历史记录*表中的旧数据,因此历史记录*表的大小不会有太大变化,并且插入速度不会随时间降低。
新节的创建由plpgsql函数activity_hist.archive_current_activity执行。 操作算法非常简单(使用archive_pg_stat_activity表的部分示例)。
创建并填写一个新部分 EXECUTE format( 'CREATE TABLE ' || partition_name || ' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_min_range ),'MM')||'-'|| to_char(date_trunc('day', partition_min_range ),'DD')||' '|| to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_max_range ),'MM')||'-'|| to_char(date_trunc('day', partition_max_range ),'DD')||' '|| to_char(date_trunc('hour', partition_max_range ),'HH24')||':00' ); INSERT INTO activity_hist.archive_pg_stat_activity ( SELECT * FROM activity_hist.history_pg_stat_activity WHERE timepoint BETWEEN partition_min_range AND partition_max_range );
创建索引 EXECUTE format ( 'CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' ); EXECUTE format ('CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' );
从history_pg_stat_activity表中删除旧数据 DELETE FROM activity_hist.history_pg_stat_activity WHERE timepoint < partition_max_range;
当然,定期删除旧部分是不必要的。
基本报告
实际上,为什么要进行所有这些操作。 可以非常远程地接收报告,大致让人联想到Oracle AWR。
重要的是,为了接收报告,有必要在pg_stat_activity和pg_stat_statements视图之间建立关系。 通过将“ queryid”列添加到表“ history_pg_stat_activity”,“ archive_pg_stat_activity”来链接表。 添加列值的方法超出了本文的范围,在此进行了介绍-pg_stat_statements + pg_stat_activity + loq_query = pg_ash? 。
查询的总CPU时间
要求: WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC
间隔' WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC
*间隔' WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC
后端' AND datname!='Postgres的AND(aa.wait_event_type IS NULL)ANDaa.state = WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC
一个例子:
------------------------------------------------------------------- | TOTAL CPU TIME FOR QUERIES : 07:47:36 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 04:28:58 | 2| | 01:07:29 | 3| 1237430309438971376| 00:59:38 | 4| 4710212362688288619| 00:50:48 | 5| 28942442626229688| 00:15:50 | 6| 9150846928388977274| 00:04:46 | 7| -6572922443698419129| 00:00:06 | 8| | 00:00:01 +----+----------------------------------------+--------------------
查询的总等待时间
要求: WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NOT NULL ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NOT NULL ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC
一个例子: ------------------------------------------------------------------- | TOTAL WAITINGS TIME FOR QUERIES : 21:55:04 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 16:19:05 | 2| | 03:47:04 | 3| 8085340880788646241| 00:40:20 | 4| 4710212362688288619| 00:13:35 | 5| 9150846928388977274| 00:12:25 | 6| 28942442626229688| 00:11:32 | 7| 1237430309438971376| 00:09:45 | 8| 2649515222348904837| 00:09:37 | 9| | 00:03:45 | 10| 3167065002719415275| 00:02:20 | 11| 5731212217001535134| 00:02:13 | 12| 8304755792398128062| 00:01:31 | 13| 2649515222348904837| 00:00:59 | 14| 2649515222348904837| 00:00:22 | 15| | 00:00:12 | 16| 3422818749220588372| 00:00:08 | 17| -5730801771815999400| 00:00:03 | 18| -1473395109729441239| 00:00:02 | 19| 2404820632950544954| 00:00:02 | 20| -6572922443698419129| 00:00:02 | 21| 2369289265278398647| 00:00:01 | 22| 180077086776069052| 00:00:01 +----+----------------------------------------+--------------------
等待查询
要求: WITH hist AS ( SELECT aa.wait_event_type , aa.wait_event 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 datname != 'postgres' AND aa.wait_event IS NOT NULL GROUP BY aa.wait_event_type , aa.wait_event UNION SELECT ha.wait_event_type , ha.wait_event FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ha.wait_event IS NOT NULL GROUP BY ha.wait_event_type , ha.wait_event ) SELECT wait_event_type , wait_event FROM hist GROUP BY wait_event_type , wait_event ORDER BY 1 ASC,2 ASC
一个例子: ------------------------------------------------ | WAITINGS FOR QUERIES +----------------------------------------------- | wait_event_type = Client| | wait_event = ClientRead| | Total time = 00:46:56| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 8085340880788646241| 00:40:20 | 2| | 00:03:45 | 3| 5731212217001535134| 00:01:53 | 4| | 00:00:12 | 5| 9150846928388977274| 00:00:09 | 6| 3422818749220588372| 00:00:08 | 7| 1237430309438971376| 00:00:06 | 8| 28942442626229688| 00:00:05 | 9| 4710212362688288619| 00:00:05 | 10|-5730801771815999400| 00:00:03 | 11| 8304755792398128062| 00:00:02 | 12|-6572922443698419129| 00:00:02 | 13|-1473395109729441239| 00:00:02 | 14| 2404820632950544954| 00:00:02 | 15| 180077086776069052| 00:00:01 | 16| 2369289265278398647| 00:00:01 +----------------------------------------------- | wait_event_type = IO| | wait_event = BufFileRead| | Total time = 00:00:38| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 28942442626229688| 00:00:38 +-----------------------------------------------
锁定过程的历史
要求: SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.archive_locking al 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 NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype UNION SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.history_locking 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 NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype ORDER BY 1
一个例子: -------------------------------------------------- -------------------------------------------------- ---------------------------------
| 锁定过程的历史
+ ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
| #| pid | 开始 持续时间 block_pids | 关系| 模式 锁型
+ ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
| 1 | 26224 | 2019-09-02 19:32:16 | 00:01:45 | {26211} | 16541 | AccessShareLock | 关系
| 2 | 26390 | 2019-09-02 19:34:03 | 00:00:53 | {26211} | 16541 | AccessShareLock | 关系
| 3 | 26391 | 2019-09-02 19:34:03 | 00:00:53 | {26211} | 16541 | AccessShareLock | 关系
| 4 | 26531 | 2019-09-02 19:35:27 | 00:00:12 | {26211} | 16541 | AccessShareLock | 关系
| 5 | 27284 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock | 关系
| 6 | 27283 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock | 关系
| 7 | 27286 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock | 关系
| 8 | 27423 | 2019-09-02 19:45:24 | 00:00:12 | {27394} | 16541 | AccessShareLock | 关系
| 9 | 27648 | 2019-09-02 19:48:06 | 00:00:20 | {27647} | 16541 | AccessShareLock | 关系
| 10 | 27650 | 2019-09-02 19:48:06 | 00:00:20 | {27647} | 16541 | AccessShareLock | 关系
| 11 | 27735 | 2019-09-02 19:49:08 | 00:00:06 | {27650} | 16541 | AccessExclusiveLock | 关系
| 12 | 28380 | 2019-09-02 19:56:03 | 00:01:56 | {28379} | 16541 | AccessShareLock | 关系
| 13 | 28379 | 2019-09-02 19:56:03 | 00:00:01 | 28377 | 16541 | AccessExclusiveLock | 关系
| | | | | 28376 | |
阻塞过程的历史
要求: SELECT blocking_pids FROM activity_hist.archive_locking al 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 NOT granted AND locktype = 'relation' GROUP BY blocking_pids UNION SELECT blocking_pids FROM activity_hist.history_locking 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 NOT granted AND locktype = 'relation' GROUP BY blocking_pids ORDER BY 1
一个例子: -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
阻塞过程的历史
+ ---- + ---------- + ---------- + -------------------- +- --------- + -------------------- + ------------------- -+ ------------------------------ + ----------------- -----------------------
| #| pid | 用户名| application_name | datname | 开始 持续时间 州| 询问
+ ---- + ---------- + ---------- + -------------------- +- --------- + -------------------- + ------------------- -+ ------------------------------ + ----------------- -----------------------
| 1 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:31:54 | 00:00:04 | 闲置
| 2 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:31:58 | 00:00:06 | 闲置交易| 开始
| 3 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:32:16 | 00:01:45 | 闲置交易| 锁定表Wafer_data;
| 4 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:35:54 | 00:01:23 | 闲置 承诺
| 5 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:38:46 | 00:00:02 | 闲置交易| 开始
| 6 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:38:54 | 00:00:08 | 闲置交易| 锁定表Wafer_data;
| 7 | 26211 | tuser | psql | tdb1 | 2019-09-02 19:39:08 | 00:42:42 | 闲置 承诺
| 8 | 26211 | tuser | psql | tdb1 | 2019-09-03 07:12:07 | 00:00:52 | 活跃 选择test_del();
发展。
所显示的基本查询和收到的报告已经大大简化了分析性能事件时的工作。
根据基本查询,您可以远程获得与Oracle AWR相似的报告。
样本摘要报告 + ------------------------------------------------- -----------------------------------
| 关于活动和等待的综合报告。 日期:09/03/2019 14:08
| ------------------------------------------------- -----------------------------------
| 主机:XXXX
| BEGIN_SNAPSHOT:09/02/2019 14:08 END_SNAPSHOT:09/03/2019 14:00
| ------------------------------------------------- -----------------------------------
| 当前数据库大小:
| 数据库:监视器
| 尺寸(MB):1370.00
| ------------------------------------------------- -----------------------------------
| 集群CPU时间:19:44:22
| 集群等待时间:78:49:16
|
| SQL DBTIME:65:53:09
| SQL CPU时间:19:05:21
| SQL等待时间:21:50:46
| SQL IOTIME:20:53:00
| SQL读取时间:20:52:55
| SQL写时间:00:00:05
|
| SQL呼叫:311293
-------------------------------------------------- -----------
| SQL共享的块读取:13351563334
| SQL共享的区块命中数:2775427045
| SQL共享的块命中率/读取%:20.79
| SQL共享块隔离:21105
| SQL共享块写入:3656
|
| SQL临时块读取:7464932
| 写入的SQL临时块:10176024
-------------------------------------------------- -----------
|
| 等待统计
|
+ ------------------------------------------------- -----------------------------------
| 系统过程中等待时间总计前10位的等待
+ ----- + ------------------------------ + ------------ -------- + --------------------
| #| wait_event_type | wait_event | 持续时间
+ ----- + ------------------------------ + ------------ -------- + --------------------
| 1 | 活动| LogicalLauncherMain | 11:21:01
| 2 | 活动| CheckpointerMain | 11:20:35
| 3 | 活动| AutoVacuumMain | 11:20:31
| 4 | 活动| WalWriterMain | 11:19:35
| 5 | 活动| BgWriterMain | 10:14:19
| 6 | 活动| BgWriterHibernate | 01:06:04
| 7 | 活动| WalSenderMain | 00:04:05
| 8 | 客户| ClientWrite | 00:04:00
| 9 | IO | BufFileWrite | 00:02:45
| 10 | LWLock | buffer_mapping | 00:02:14
+ ----- + ------------------------------ + ------------ -------- + --------------------
| 客户过程中等待时间总计前10位的等待
+ ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
| #| wait_event_type | wait_event | 持续时间 %dbtime
+ ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
| 1 | 锁| transactionid | 11:55:37 | 18.1
| 2 | IO | DataFileRead | 07:19:43 | 12/12
| 3 | 客户| ClientRead | 00:46:54 | 1.19
| 4 | 锁| 关系| 00:40:37 | 1.03
| 5 | LWLock | buffer_mapping | 00:31:08 | 0.79
| 6 | LWLock | buffer_io | 00:22:12 | 0.56
| 7 | 超时| PgSleep | 00:10:58 | 0.28
| 8 | 锁| 元组| 00:01:30 | 0.04
| 9 | IO | BufFileWrite | 00:01:16 | 0.03
| 10 | IO | BufFileRead | 00:00:37 | 0.02
+ ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
| 系统过程中等待时间的等待类型
+ ----- + ------------------------------ + ------------ --------
| #| wait_event_type | 持续时间
+ ----- + ------------------------------ + ------------ --------
| 1 | 活动| 56:46:10
| 2 | IO | 00:05:13
| 3 | 客户| 00:04:00
| 4 | LWLock | 00:03:07
+ ----- + ------------------------------ + ------------ --------
| 针对客户过程的总等待时间的等待类型
+ ----- + ------------------------------ + ------------ -------- + --------------------
| #| wait_event_type | 持续时间 %dbtime
+ ----- + ------------------------------ + ------------ -------- + --------------------
| 1 | 锁| 12:37:44 | 19.17
| 2 | IO | 07:21:40 | 11.17
| 3 | LWLock | 00:53:26 | 1.35
| 4 | 客户| 00:46:54 | 1.19
| 5 | 超时| 00:10:58 | 0.28
| 6 | IPC | 00:00:04 | 0
+ ----- + ------------------------------ + ------------ -------- + --------------------
| 等待系统过程
+ ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------
| #| backend_type | dbname | wait_event_type | wait_event | 持续时间
+ ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------
| 1 | 逻辑复制启动器| | 活动| LogicalLauncherMain | 11:21:01
| 2 | 检查点| | 活动| CheckpointerMain | 11:20:35
| 3 | 自动真空发射器| | 活动| AutoVacuumMain | 11:20:31
| 4 | walwriter | | 活动| WalWriterMain | 11:19:35
| 5 | 背景作家| | 活动| BgWriterMain | 10:14:19
| 6 | 背景作家| | 活动| BgWriterHibernate | 01:06:04
| 7 | walsender | | 活动| WalSenderMain | 00:04:05
| 8 | walsender | | 客户| ClientWrite | 00:04:00
| 9 | 平行工人| tdb1 | IO | BufFileWrite | 00:02:45
| 10 | 平行工人| tdb1 | LWLock | buffer_mapping | 00:02:05
| 11 | 平行工人| tdb1 | IO | DataFileRead | 00:01:10
| 12 | 平行工人| tdb1 | IO | BufFileRead | 00:01:05
| 13 | 平行工人| tdb1 | LWLock | buffer_io | 00:00:45
| 14 | 真空吸尘器| tdb1 | LWLock | buffer_mapping | 00:00:09
| 15 | walwriter | | IO | WALWrite | 00:00:08
| 16 | walwriter | | LWLock | WALWriteLock | 00:00:04
| 17 | 背景作家| | LWLock | WALWriteLock | 00:00:03
| 18 | 背景作家| | IO | WALWrite | 00:00:02
| 19 | 背景作家| | IO | DataFileWrite | 00:00:02
| 20 | 检查点| | IO | ControlFileSyncUpdate | 00:00:01
| 21 | 真空吸尘器| tdb1 | LWLock | buffer_io | 00:00:01
+ ----- + ----------------------------- + ---------- +- ------------------ + ---------------------- + -------- ------------
| SQL等待
+ ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
| #| queryid | dbname | wait_event_type | wait_event | 持续时间 %dbtime
+ ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
| 1 | 389015618226997618 | tdb1 | 锁| transactionid | 09:47:43 | 14.87
| 2 | 389015618226997618 | tdb1 | IO | DataFileRead | 05:47:07 | 8.78
| 3 | | tdb1 | 锁| transactionid | 02:07:54 | 3.24
| 4 | | tdb1 | IO | DataFileRead | 01:30:24 | 2.29
| 5 | 8085340880788646241 | tdb1 | 客户| ClientRead | 00:40:20 | 1.02
| 6 | 389015618226997618 | tdb1 | LWLock | buffer_mapping | 00:20:41 | 0.52
| 7 | 389015618226997618 | tdb1 | LWLock | buffer_io | 00:17:30 | 0.44
| 8 | 2649515222348904837 | tdb1 | 超时| PgSleep | 00:10:58 | 0.28
| 9 | 4710212362688288619 | tdb1 | 锁| 关系| 00:10:44 | 0.27
| 10 | 9150846928388977274 | tdb1 | 锁| 关系| 00:10:24 | 0.26
| 11 | 28942442626229688 | tdb1 | 锁| 关系| 00:07:48 | 0.2
| 12 | 1237430309438971376 | tdb1 | 锁| 关系| 00:07:32 | 0.19
| 13 | | tdb1 | LWLock | buffer_mapping | 00:04:32 | 0.11
| 14 | | tdb1 | LWLock | buffer_io | 00:04:13 | 0.11
| 15 | | tdb1 | 客户| ClientRead | 00:03:57 | 0.1
| 16 | 4710212362688288619 | tdb1 | LWLock | buffer_mapping | 00:02:26 | 0.06
| 17 | 3167065002719415275 | tdb1 | 锁| 关系| 00:02:20 | 0.06
| 18 | 5731212217001535134 | tdb1 | 客户| ClientRead | 00:01:53 | 0.05
| 19 | 1237430309438971376 | tdb1 | LWLock | buffer_mapping | 00:01:42 | 0.04
| 20 | 389015618226997618 | tdb1 | 锁| 元组| 00:01:30 | 0.04
| 21 | 8304755792398128062 | tdb1 | 锁| 关系| 00:01:29 | 0.04
| 22 | 28942442626229688 | tdb1 | IO | BufFileWrite | 00:01:16 | 0.03
| 23 | 9150846928388977274 | tdb1 | IO | DataFileRead | 00:01:07 | 0.03
| 24 | 28942442626229688 | tdb1 | LWLock | buffer_mapping | 00:01:03 | 0.03
| 25 | 9150846928388977274 | tdb1 | LWLock | buffer_mapping | 00:00:44 | 0.02
| 26 | 28942442626229688 | tdb1 | IO | BufFileRead | 00:00:37 | 0.02
| 27 | 28942442626229688 | tdb1 | LWLock | buffer_io | 00:00:25 | 0.01
| 28 | 1237430309438971376 | tdb1 | IO | DataFileRead | 00:00:24 | 0.01
| 29 | 28942442626229688 | tdb1 | IO | DataFileRead | 00:00:22 | 0.01
| 30 | 5731212217001535134 | tdb1 | 锁| 关系| 00:00:20 | 0.01
| 31 | 4710212362688288619 | tdb1 | IO | DataFileRead | 00:00:19 | 0.01
| 32 | 9150846928388977274 | tdb1 | 客户| ClientRead | 00:00:09 | 0
| 33 | 3422818749220588372 | tdb1 | 客户| ClientRead | 00:00:08 | 0
| 34 | 1237430309438971376 | tdb1 | 客户| ClientRead | 00:00:06 | 0
| 35 | 389015618226997618 | tdb1 | LWLock | buffer_content | 00:00:05 | 0
| 36 | 4710212362688288619 | tdb1 | 客户| ClientRead | 00:00:05 | 0
| 37 | 4710212362688288619 | tdb1 | LWLock | buffer_io | 00:00:04 | 0
| 38 | 28942442626229688 | tdb1 | 客户| ClientRead | 00:00:04 | 0
| 39 | 28942442626229688 | tdb1 | IPC | 并行完成| 00:00:03 | 0
| 40 | 389015618226997618 | tdb1 | IO | DataFileWrite | 00:00:02 | 0
| 41 | -5730801771815999400 | tdb1 | 客户| ClientRead | 00:00:02 | 0
| 42 | 2404820632950544954 | tdb1 | 客户| ClientRead | 00:00:02 | 0
| 43 | -6572922443698419129 | tdb1 | 客户| ClientRead | 00:00:02 | 0
| 44 | 8304755792398128062 | tdb1 | 客户| ClientRead | 00:00:02 | 0
| 45 | -1473395109729441239 | tdb1 | 客户| ClientRead | 00:00:02 | 0
| 46 | | tdb1 | LWLock | buffer_content | 00:00:01 | 0
| 47 | 180077086776069052 | tdb1 | 客户| ClientRead | 00:00:01 | 0
| 48 | | tdb1 | IO | DataFileWrite | 00:00:01 | 0
| 49 | 28942442626229688 | tdb1 | IPC | MessageQueueReceive | 00:00:01 | 0
| 50 | 2369289265278398647 | tdb1 | 客户| ClientRead | 00:00:01 | 0
| 51 | 9150846928388977274 | tdb1 | IO | DataFileWrite | 00:00:01 | 0
+ ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
|
| 客户SQL统计
|
+ ------------------------------------------------- -----------------------------------
| CLIENT SQL按经过时间排序
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
| 经过时间| 电话| %dbtime | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
| 06:43:19 | 36 | 10.2 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 02:06:53 | 715 | 3.21 | 0.85 | 0.06 | tdb1 | 1237430309438971376
| 01:52:07 | 720 | 2.84 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 00:39:03 | 357 | 0.99 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 00:22:00 | 8 | 0.56 | 0.96 | 0 | tdb1 | 2649515222348904837
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
| CLIENT SQL按CPU时间排序
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| cpu时间| 电话| %dbtime | total_time | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 16:14:38 | 36 | 10.2 | 06:43:19 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 00:13:38 | 720 | 2.84 | 01:52:07 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 00:11:39 | 357 | 0.99 | 00:39:03 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 00:10:58 | 8 | 0.56 | 00:22:00 | 0.96 | 0 | tdb1 | 2649515222348904837
| 00:09:44 | 715 | 3.21 | 02:06:53 | 0.85 | 0.06 | tdb1 | 1237430309438971376
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 用户I / O等待时间排序的CLIENT SQL
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| io_wait时间| 电话| %dbtime | total_time | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 05:47:09 | 36 | 10.2 | 06:43:19 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 00:02:15 | 357 | 0.99 | 00:39:03 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 00:00:24 | 715 | 3.21 | 02:06:53 | 0.85 | 0.06 | tdb1 | 1237430309438971376
| 00:00:19 | 720 | 2.84 | 01:52:07 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 00:00:00 | 8 | 0.56 | 00:22:00 | 0.96 | 0 | tdb1 | 2649515222348904837
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 共享缓冲区排序的CLIENT SQL读取
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 缓冲区读取| 电话| %dbtime | total_time | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 2562353244 | 36 | 10.2 | 06:43:19 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 11041689 | 357 | 0.99 | 00:39:03 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 3303551 | 715 | 3.21 | 02:06:53 | 0.85 | 0.06 | tdb1 | 1237430309438971376
| 3242892 | 720 | 2.84 | 01:52:07 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 0 | 8 | 0.56 | 00:22:00 | 0.96 | 0 | tdb1 | 2649515222348904837
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| CLIENT SQL按磁盘读取时间排序
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 阅读时间 电话| %dbtime | total_time | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 03:37:46 | 36 | 10.2 | 06:43:19 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 00:04:07 | 357 | 0.99 | 00:39:03 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 00:00:59 | 720 | 2.84 | 01:52:07 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 00:00:42 | 715 | 3.21 | 02:06:53 | 0.85 | 0.06 | tdb1 | 1237430309438971376
| 00:00:00 | 8 | 0.56 | 00:22:00 | 0.96 | 0 | tdb1 | 2649515222348904837
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 客户SQL按执行顺序排序
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 电话| 行| %dbtime | total_time | %CPU | IO百分比| dbname | queryid
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| 720 | 720 | 2.84 | 01:52:07 | 1.19 | 0.08 | tdb1 | 4710212362688288619
| 715 | 715 | 3.21 | 02:06:53 | 0.85 | 0.06 | tdb1 | 1237430309438971376
| 357 | 0 | 0.99 | 00:39:03 | 1.02 | 0.33 | tdb1 | 28942442626229688
| 36 | 36 | 10.2 | 06:43:19 | 九月85 | 17.38 | tdb1 | 389015618226997618
| 8 | 8 | 0.56 | 00:22:00 | 0.96 | 0 | tdb1 | 2649515222348904837
+ -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
| SQL文本的完整列表
----------------------------------------------
...
待续。 下一步是形成锁定历史记录(pg_stat_locks),这是对填充表的过程的更详细描述。