一、需求背景

二、需求设计与关键表介绍

2.1 system_*介绍

2.2 queries_*介绍

三、实践

3.1 自动化排程

一、需求背景

监控GP集群每个资源管理队列的耗时情况，来观察业务上的改变对数据库影响几何？

二、需求设计与关键表介绍

GreenPlum 中有一个gpperfmon数据库，该数据库中记录了GP运行时的状态，并提供履历查询。在分析GP效能时比较好用。官方也提供了GPCC web监控页面，但总不能满足你的需求。对其系统表熟悉之后，便可以自己来做自己想要的监控。

需要注意的是GP6安装不需要单独安装gppermon数据库。但gpperfmon库仍然是存在的。外表设计上也有了很大的优化，较GP4变化很大，毕竟GPCC 现在是商业组件。

2.1 system_*介绍

system_ *表存储系统使用率指标。 有三个系统表，它们都具有相同的列：

•system_now是一个外部表，其数据文件存储在$ MASTER_DATA_DIRECTORY / gpperfmon /data中。 在数据收集之间的时间段内，当前系统使用率数据存储在system_now中从Command Center代理中自动提交到system_history表。•system_tail是一个外部表，其数据文件存储在$ MASTER_DATA_DIRECTORY /gpperfmon /data中。 这是已从中清除的系统利用率数据的过渡表。system_now，但尚未提交给system_history。 它通常只包含一些分钟的数据量。•system_history是存储历史系统利用率指标的常规表。 它是预先分区的，分成每月的分区。 分区会根据需要以两个月为增量自动添加。

Column

type

Description

ctime

timestamp

创建此行的时间

hostname

varchar(64)

与这些系统指标关联的段或主主机名。

mem_total

bigint

该主机的总系统内存（以字节为单位）。

mem_used

bigint

该主机使用的系统内存（以字节为单位）。

mem_actual_used

bigint

该主机已使用的实际内存（以字节为单位）（不包括为memory和cache保留的内存）.

mem_actual_free

bigint

该主机的可用实际内存（以字节为单位）（不包括为memory和cache保留的内存）

swap_total

bigint

该主机的总交换空间（以字节为单位）。

swap_used

bigint

此主机的可用实际内存（以字节为单位）（不包括为memory和cache保留的内存）

swap_page_in

bigint

交换页面的数量。

swap_page_out

bigint

换出页数

cpu_user

float

Greenplum系统用户的CPU使用率。

cpu_sys

float

该主机的CPU使用率。

cpu_idle

float

指标收集时空闲的CPU容量。

load0

float

前一分钟的CPU平均负载。

load1

float

前五分钟的CPU平均负载

load2

float

前十五分钟的CPU平均负载

quantum

int

此度量标准的度量标准收集之间的间隔

disk_ro_rate

bigint

每秒磁盘读取操作。

disk_wo_rate

bigint

每秒磁盘写操作。

disk_rb_rate

bigint

磁盘写入操作的每秒字节数。

net_rp_rate

bigint

系统网络上每秒的数据包，用于读取操作。

net_wp_rate

bigint

系统网络上每秒用于写操作的数据包。

net_rb_rate

bigint

系统网络上每秒的字节数，用于读取操作。

net_wb_rate

bigint

系统网络上每秒用于写入操作的字节数。

2.2 queries_*介绍

tmid，ssid和ccnt列是唯一标识特定查询的组合键。这些列可用于与iterators_ *表联接。

有三个查询表，所有查询表都具有相同的列：

query_now是一个外部表，其数据文件存储在$ MASTER_DATA_DIRECTORY /中。当前查询状态存储在querys_now中从命令中心代理收集并自动提交到querys_history表。query_tail是一个外部表，其数据文件存储在$ MASTER_DATA_DIRECTORY /中这是已从中清除查询状态数据的过渡表。query_history是存储历史查询状态数据的常规表。它是预先分区的分成每月的分区。分区会根据需要以两个月为增量自动添加。

Column

Type

Description

ctime

timestamp

创建此行的时间

tmid

int

特定查询的时间标识符。 与查询关联的所有记录将具有相同的tmid。

ssid

int

会话ID，如gp_session_id所示。 与查询关联的所有记录将具有相同的ssid。

ccnt

int

该会话中的命令编号，如gp_command_count所示。 与查询关联的所有记录将具有相同的ccnt。

username

varchar(64)

发出此查询的Greenplum角色名称。

db

varchar(64)

查询的数据库名称。

cost

int

在此版本中未实现。

tsubmit

timestamp

提交查询的时间。

tstart

timestamp

查询开始的时间。

tfinish

timestamp

查询结束的时间。

status

varchar(64)

查询状态 -- start,

done, or abort.

rows_out

bigint

Rows out for the query.

cpu_elapsed

bigint

执行此查询的所有段中所有进程的CPU使用率（以秒为单位）。 它是从数据库系统中所有主要主段获取的CPU使用率值的总和。 请注意，如果查询运行时间短于Quantum的值，Greenplum Command Center会将值记录为0。 即使查询运行时大于min_query_time和min_detailed_query的值，并且这些值小于Quantum的值，也会发生这种情况。

cpu_currpct

float

当前执行此查询的所有进程的CPU平均百分比。 对每个段上运行的所有进程的百分比求平均值，然后计算所有这些值得平均值以表示该指标。历史数据和尾部数据中当前的CPU百分比平均值始终为零。

skew_cpu

float

显示该查询在系统中的处理偏斜量。 当一个段对查询执行的处理量不成比例时，就会发生处理/ CPU偏斜。此值是此查询所有段中所有迭代器的所有迭代器的CPU％指标的变异系数乘以100。

例如，值为.95为

显示为95。

skew_rows

float

显示系统中的行偏斜量。 发生行偏斜

当一个细分产生不成比例的数量时

查询的行。 此值是此查询所有段上所有迭代器的rows_in度量标准的变异系数，乘以100。

例如，.95的值显示为95。

query_hash

bigint

在此版本中未实现。

query_text

text

此查询的SQL文本。

query_plan

text

此查询的SQL的查询计划。

application_name

varchar(64)

应用程序的名称。

rsqname

varchar(64)

资源队列的名称。

rqppriority

varchar(64)

查询的优先级 -- max,

high, med, low, or min.

三、实践

本次使用的是query_history。gp中好像没有oracle中的rownum。为了排序使用窗口函数

"row_number" () OVER ( ORDER BY sum1 DESC )

SELECT	"row_number" () OVER ( ORDER BY sum1 DESC ) RANK,	rsqname,	sum1 FROM	(	SELECT-- "row_number"() over (partition by rsqname,SUBSTRING(ctime,1,10) order by diff asc) as id,	rsqname,-- SUBSTRING(ctime,1,10) as time1,	SUM ( diff ) AS sum1 FROM	(SELECT EXTRACT	(	epoch FROM	( tfinish - tstart )) AS diff,	rsqname,	ctime -- ,tsubmit,-- tstart,tfinish,query_text,*	FROM	queries_history WHERE	ctime >= '2020-09-03 00:00:00' 	AND ctime < '2020-09-04 00:00:00' --  and username = 'edaapu1_array'-- and rsqname = 'edaapu'		AND rsqname <> '' 	) T GROUP BY	rsqname -- ,SUBSTRING(ctime,1,10)		) t2

查询的结果就是 资源队列耗时的Rank 。

3.1 自动化排程

我们最终的目的是每天按时提供资源队列耗时的Rank并发送邮件。

我的解决方案是： psql + crontab + mail

#!/bin/bash# @ author ninuesun# @ date 2020年9月7日16:19:35# @ desc get top time-consuming's user form gpccdatabase=$1 if [ $# -ne 1 ];then        echo -e "Usage: ./vacuumgp.sh  < dbname > \n "        echo -e "Example : ./vacuumgp.sh postgres"        exit 8fi #加载gp环境变量source /home/gpadmin/.bash_profile date=`date +"%Y-%m-%d %H:%M:%S"`  dateFrom=$(date +"%Y-%m-%d %H:%M:%S" --date '24 hours ago')dateTo=$(date +"%Y-%m-%d %H:%M:%S")message=$(psql -h hostname -d $database -v v1="'$dateFrom'" -v v2="'$dateTo'" -f /home/scripts/GPCC/getGpccInfo.sql)  #echo "message\n :$message"echo -e "GPCC top resname:\n $message"|mail  -s "resname 耗时rank" 邮箱地址

需要注意的就是

1、psql 传参。使用参数-v

2、多个参数需要传多个-v(单个生效原则)

3、传参的单引号可以在sql中也可以在psql脚本中