Greenplum数据库巡检报告_greenplum数据库巡检脚本

更多的文章请关注作者微信公众号或访问作者github:
 
https://github.com/xfg0218/greenplum--summarize

 

作者公众号

目录

目录 2

1 巡检说明 6

2 查看集群硬件相关信息 6

2.1 查看集群当前系统时间 6

2.2 查看操作系统版本 7

2.3 查看内核版本 7

2.4 查看内存的详细信息 7

2.4.1查看内存的使用情况 7

2.4.2 查看实时的内存使用情况 8

2.5 查看集群的CPU详细信息 8

2.5.1 查看集群中CPU的核数 8

2.5.2 实时查看CPU的使用情况 9

2.6 查看集群的磁盘的详细信息 10

2.6.1查看集群磁盘使用空间 10

2.6.2 动态查看磁盘的使用情况 10

2.7 查看机器内核配置参数 10

2.8 查看系统打开文件的最大数 11

2.9 查看磁盘的挂在情况 12

2.10 查看开机需要启动的项 12

2.11 查看系统系统资源限制 12

2.12 查看集群的网络的详细信息 13

2.12.1 查看集群中网卡的大小 13

2.12.2 动态查看网络的吞吐情况 13

3 查看集群上运行的任务 14

3.1 查看集群上运行的定时任务 14

3.1.1 编写查看定时的脚本 14

3.1.2 运行定时的脚本 14

3.2 查看集群上运行greenplum的进程 15

4 查看集群的基本信息 15

4.1 查看集群的版本信息 15

4.2 查看segment相关信息 15

4.2.1 查看当前down的segment节点 15

4.2.2 查看当前处于change tracking的segment节点 16

4.2.3 查看当前处于re-syncing状态的segment节点 16

4.2.4 查看所有segment是否可达,确保QD(query dispatching)正常 16

4.3 查看standby与master的信息 16

4.3.1 查看standby的配置 16

4.3.2 查看standby 的运行状态 17

4.3.3 查看master节点的是否正常 17

4.3.4 检查gp_persistent表,确保主备Segment之间是否有数据不一致的问题 18

4.4 查看有问题的segment primary/mirror信息 18

4.5 显示mirror列表 19

4.6 显示所有配置参数 19

4.7 查看集群中数据库中的详细信息 19

4.7.1 查看每个数据库占用的大小 19

4.7.2 查看每个schema的占用大小 20

4.7.3 查看AO表的相关信息 21

4.7.3.1 查看数据库中的AO表 21

4.7.3.2 查看数据库中的AO表的数量 21

4.7.4 查看堆表的相关信息 21

4.7.4.1 查看数据库中的堆表 21

4.7.4.2 查看堆表的数量 21

4.7.5 查看外部表相关信息 22

4.7.5.1 查看外部表 22

4.7.5.2 查看外部表的数量 22

4.7.6 查看视图的相关信息 22

4.7.6.1 查看制定schema下视图 22

4.7.6.2 查看制定schema下视图的数量 22

4.7.7 查看表的相关信息 22

4.7.7.1 查看每个表的大小信息 22

4.8 查看索引的相关信息 23

4.8.1查看索引大小超过表总大小1/2的系统表大小和索引大小 23

4.8.2 查看制定schema上表的索引 24

4.8.3 索引活跃度监控 24

4.8.3.1 IO活跃度查看 24

4.8.3.2 访问活跃度 24

4.8.4 查看Master与Segment上索引不一致的问题 25

4.9 检查是否使用了a-z 0-9 _ 以外的字母作为对象名 25

4.9.1 查看数据库是否使用了命名规范 25

4.9.2 查看表的索引和视图的命名规范 25

4.9.3 查看数据库中的类型命名规范 26

4.9.4 查看数据库中的储存过程的命名规范 26

4.9.5 查看数据库中的表,视图的的命名规范 26

4.10 查看集群是否处于not balanced状态 27

4.10.1查看当前的连接数 27

4.10.2 查看密码有效期不足30天的用户 27

4.10.3 查看每个用户链接的个数 28

4.10.4 查看数据库的连接数 28

4.11 查看ctid的值 28

5 集群巡检过程详细信息 29

5.1 检查大小超过1GB的表倾斜情况 29

5.1.1 查看超过1GB倾斜率的表 29

5.1.2 查看表在segment上占用大小及倾斜率 30

5.1.3 查看表在segment上占用的行数及百分比 30

5.1.4 解决表分布倾斜的情况 31

5.1.4.1 分布键说明 31

5.1.4.2 修改分布键 31

5.1.4.3 对表进行重新创建 31

5.2 检查膨胀率过高的表 32

5.2.1 查看schema下的AO表 32

5.2.2 查看表的膨胀率 33

5.2.3 对表进行释放空间 33

5.3 检查元数据不一致问题 33

5.3.1 问题描述 33

5.3.2 集群检查 34

5.3.3 问题处理 34

5.4 查看服务器配置参数 34

5.4.1 服务器OS参数查看 34

5.5 查看集群参数详细信息 35

5.5.1 查看每个segment的内存配置参数 35

5.5.1.1 查看分配内存信息 35

5.5.1.2 修改内存参数 35

5.5.2 查看shared_buffers(共享缓冲区)的内存 36

5.5.2.1 查看系统配置的参数 36

5.5.2.2 参数详解 36

5.5.2.3 修改参数 36

5.5.3 查看max_connections(最大连接数) 36

5.5.3.1 查看最大连接数参数 36

5.5.3.2 参数详解 37

5.5.4 查看block_size(磁盘块)的大小 37

5.5.4.1 查看磁盘块的大小 37

5.5.4.2 参数详解 37

5.5.5 查看work_mem的值 37

5.5.5.1 查看集群中work_mem的配置大小 37

5.5.5.2 参数详解 38

5.5.5.3 修改参数 38

5.5.6 查看statement_mem的值 38

5.5.6.1 查看集群中statement_mem的值 38

5.5.6.2 参数详解 38

5.5.6.3 修改参数 38

5.5.7 查看gp_workfile_limit_files_per_query的值 39

5.5.7.1 查看此值的大小 39

5.5.7.1 参数详解 39

5.5.8 查看gp_resqueue_priority_cpucores_per_segment的值 39

5.5.8.1 查看此值的大小 39

5.5.8.2 参数详解 39

5.5.8.3 修改参数 40

5.6 备 Master 镜像情况 40

5.6.1 概述 40

5.6.2 查看备master的运行情况 40

5.6.3 备master需要检测的项 40

5.7 segment 镜像情况 41

5.7.1 查看集群中的镜像分布情况 41

5.8 资源队列情况 41

5.8.1 用户与资源队列检查 41

5.8.1.1 查看负载管理资源队列的状态和活动 41

5.8.1.2 查看当前用户使用的是什么队列 42

5.8.1.3 查看队列的活动负载状态 43

5.8.1.4 查看负载管理特性的Greenplum数据库资源队列的信息 44

5.8.1.5 查看队列资源的状态 44

5.8.1.6 查看每个资源队列的配置情况 45

5.8.1.7 修改资源队列的语句 46

5.9 Statistics 状态检查 46

5.9.1 查看所有的表 46

5.9.2 查看指定schema下的表 47

5.10 Skew 状态检查 47

5.11 Bloat 状态 47

5.12 集群系统性能 48

5.12.1 DB性能查看 48

5.12.2 SQL 锁检 48

5.12.3 集群硬件性能查看 49

5.13 查看集群系统元数据 50

5.14 查看集群中分布键倾斜率比较高的表 50

5.15 使用gpcheckcat命令检测集群 50

5.15.1 gpcheckcat 检测项说明 50

5.15.2 检查master,segment的catalog一致性 50

5.15.3检查持久化表的catalog一致性。 51

5.15.4 检查pg_class与pg_attribute是否不一致 51

5.16 表活跃度监控 52

5.16.1 IO活跃度的表 52

5.16.2 表的访问活跃度 53

1 巡检说明

作为上线的数据库必须经常做一下巡检，就像人的身体要经常做检查一样，敲代码的说不定

哪一天就挂了，巡检发现问题，并及时排除问题，让 greenplum 数据库健康运行，它没有问题，我们也放心、、、、

2 查看集群硬件相关信息

创建集群所有主机的映射文件,如:cluster_hosts

$ cat cluster_hosts

gpmdw

gpsdw1

gpsdw2

gpsdw3

执行gpssh命令

$ gpssh -f cluster_hosts

=>

使用greenplum自带的gpssh命令执行集群运行的参数,详细资料请参考:

https://gp-docs-cn.github.io/docs/utility_guide/admin_utilities/gpssh.html

2.1 查看集群当前系统时间

=> date +"%Y-%m-%d %H:%M:%S"

[gpsdw3] 2019-05-29 17:34:27

[ gpmdw] 2019-05-29 17:34:27

[gpsdw1] 2019-05-29 17:34:27

[gpsdw2] 2019-05-29 17:34:27

主要查看集群时间是否与外网时间是否同步

2.2 查看操作系统版本

=> cat /etc/system-release

[gpsdw3] CentOS Linux release 7.2.1511 (Core)

[ gpmdw] CentOS Linux release 7.2.1511 (Core)

[gpsdw1] CentOS Linux release 7.2.1511 (Core)

[gpsdw2] CentOS Linux release 7.2.1511 (Core)

在以上可以看出机器使用的是centos 7.2 发布版

2.3 查看内核版本

=> uname -a

转存失败重新上传取消

在以上可以看出使用的3.10.0-693.21.1.el7.x86_64的内核64位操作系统

2.4 查看内存的详细信息

2.4.1查看内存的使用情况

=> free -g

转存失败重新上传取消

由于gpmdw是master内存小了一点，其他的数据节点都被缓存使用了

2.4.2 查看实时的内存使用情况

以下命令式输出10次结果

$ vmstat 1 10

转存失败重新上传取消

主要关注buffers/cache的free是否已经快没有了，如果是，那就说明内存使用已经很吃紧了。还有就是看swap是否已经开始使用了，如果swap开始使用，说明操作系统的内存已经不足。在内存使用过程中，要时刻监控空闲内存的大小，如果发现内存占用过高，尤其是swap开始使用时（swap开始使用，会导致Greenplum性能大减），要及时发现并处理。

2.5 查看集群的CPU详细信息

2.5.1 查看集群中CPU的核数

=> lscpu|grep -E "Thread|Core|Socket|Model name|CPU"

或使用

=> lscpu

转存失败重新上传取消

有与集群机器多，这里只列出了一台数据节点的详细信息，可以看出该台机器18核，2.1GHz的

2.5.2 实时查看CPU的使用情况

以下是查看机器上所有的CPU的核,每1秒刷新一次

$ mpstat -P ALL 1

转存失败重新上传取消

%user 在internal时间段里，用户态的CPU时间(%)，不包含nice值为负进程 (usr/total)*100

%nice 在internal时间段里，nice值为负进程的CPU时间(%) (nice/total)*100

%sys 在internal时间段里，内核时间(%) (system/total)*100

%iowait 在internal时间段里，硬盘IO等待时间(%) (iowait/total)*100

%irq 在internal时间段里，硬中断时间(%) (irq/total)*100

%soft 在internal时间段里，软中断时间(%) (softirq/total)*100

%idle 在internal时间段里，CPU除去等待磁盘IO操作外的因为任何原因而空闲的时间闲置时间(%) (idle/total)*100

2.6 查看集群的磁盘的详细信息

2.6.1查看集群磁盘使用空间

=> df -h

转存失败重新上传取消

有与集群机器多，这里只列出了一台数据节点的详细信息，可以看出该数据节点的数据目录/greenplum已经使用57%,建议不要超过70%

2.6.2 动态查看磁盘的使用情况

$ iostat -x 1

转存失败重新上传取消

在以上可以详细的看出服务器的详细信息，重点观察最后一个参数%util，它表示磁盘使用时间的占比，当这个数据是100%时，就说明磁盘已经很忙碌了。然后再看下rsec/s、wsec/s等参数，观察磁盘的吞吐。

2.7 查看机器内核配置参数

=> grep "^[a-z]" /etc/sysctl.conf

转存失败重新上传取消

详细的参数含义请查看:

https://blog.csdn.net/xfg0218/article/details/91536066

2.8 查看系统打开文件的最大数

=> grep -v "^#" /etc/security/limits.conf|grep -v "^$"

转存失败重新上传取消

详细参数详解请查看:

https://blog.csdn.net/xfg0218/article/details/91554032

2.9 查看磁盘的挂在情况

=> egrep -v "^#|^$" /etc/fstab

转存失败重新上传取消

2.10 查看开机需要启动的项

=> cat /etc/rc.local|grep '^[a-z]'

转存失败重新上传取消

在以上可以看出开机需要执行以下操作

touch /var/lock/subsys/local

ulimit -SHn 102400

2.11 查看系统系统资源限制

=> for dir in `ls /etc/security/limits.d`; do echo "/etc/security/limits.d/$dir : "; grep -v "^#" /etc/security/limits.d/$dir|grep -v "^$"; echo ""; done

转存失败重新上传取消

以上的储存不要超过70%，否则会影响集群的性能下降

2.12 查看集群的网络的详细信息

2.12.1 查看集群中网卡的大小

=> ethtool bond0|grep "Speed"

[gpsdw3] Speed: 10000Mb/s

[ gpmdw] Speed: 4000Mb/s

[gpsdw1] Speed: 10000Mb/s

[gpsdw2] Speed: 10000Mb/s

bond0 : 绑定网卡的名字

在以上可以看出gpmdw使用了4000Mb的网卡，其余的都是10000Mb网卡

2.12.2 动态查看网络的吞吐情况

$ sar -n DEV 1 100

转存失败重新上传取消

3 查看集群上运行的任务

3.1 查看集群上运行的定时任务

3.1.1 编写查看定时的脚本

以下脚本请在root的用户下运行

# cat cluster-crontab.sh

#!bin/bash

for OS_USER in `cat /etc/passwd |grep -e '/bin/bash' | awk -F":" '{print $1}'`

do

echo "用户 $OS_USER " && crontab -l -u $OS_USER

done

3.1.2 运行定时的脚本

# sh cluster-crontab.sh

用户 root

0-59/10 * * * * /usr/sbin/ntpdate 192.168.201.11

用户 wasd

no crontab for wasd

用户 gpadmin

no crontab for gpadmin

用户 gp

no crontab for gp

用户 opsadmin

no crontab for opsadmin

查看每台集群上的定时的任务

3.2 查看集群上运行greenplum的进程

脚本下载:

https://github.com/xfg0218/shellForRoot

# sh cmd.sh all "ps -ef|grep greenplum"

转存失败重新上传取消

在以上可以看出集群链接的数量以及每个segment的端口和gpcc提供服务的端口等信息，需要仔细的查看一下

4 查看集群的基本信息

4.1 查看集群的版本信息

psql -d stagging -c "select version()"

转存失败重新上传取消

在以上可以看出greenplum集群使用的是PostgreSQL 8.3.23，GCC gcc (GCC) 6.2.0版本

4.2 查看segment相关信息

4.2.1 查看当前down的segment节点

$ psql -d stagging -c "select * from gp_segment_configuration where status='d';"

dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port

------+---------+------+----------------+------+--------+------+----------+---------+------------------

(0 rows)

如果没有数据表示没有宕机的segment

或使用以下SQL语句

SELECT * FROM gp_segment_configuration

WHERE status <> 'u';

4.2.2 查看当前处于change tracking的segment节点

SELECT * FROM gp_segment_configuration WHERE mode = 'c';

如果有记录返回，表示有处于change tracking的segment。

4.2.3 查看当前处于re-syncing状态的segment节点

SELECT * FROM gp_segment_configuration WHERE mode = 'r';

如果有记录返回，表示有处于re-syncing的segment

4.2.4 查看所有segment是否可达,确保QD(query dispatching)正常

SELECT gp_segment_id, count(*) FROM gp_dist_random('pg_class') GROUP BY 1;

正常情况下，每个节点返回一条记录，如果执行失败，表示有不可达的segment，执行SQL是QD阶段会失败。

4.3 查看standby与master的信息

4.3.1 查看standby的配置

$ gpstate -f

转存失败重新上传取消

在以上可以看出备用master的信息是gpdev153上数据目录在/data/gpmaster/gpseg-1

4.3.2 查看standby 的运行状态

SELECT procpid, state FROM pg_stat_replication;

转存失败重新上传取消

如果state不是'STREAMING',或者没有记录返回,那么说明master standby节点异常。

或使用select * from pg_stat_replication 查看standby的详细信息

4.3.3 查看master节点的是否正常

SELECT count(*) FROM gp_segment_configuration;

QUERY正常返回，表示master节点正常。

4.3.4 检查gp_persistent表,确保主备Segment之间是否有数据不一致的问题

如果在有standby的情况下执行以下语句有结果说明有不同步的情况

SELECT p.tablespace_oid,p.relfilenode_oid,p.segment_file_num,

case when p.persistent_state=0 then ' free'

when p.persistent_state=1 then 'create pending'

when p.persistent_state=2 then 'created'

when p.persistent_state=3 then 'drop pending'

when p.persistent_state=4 then 'abort create'

when p.persistent_state=5 then 'JIT create pending'

else 'unknown state:' || p.persistent_state end as persistent_state,

case when p.mirror_existence_state=0 then 'mirror free'

when p.mirror_existence_state=1 then 'not mirrored'

when p.mirror_existence_state=2 then 'mirror create pending'

when p.mirror_existence_state=3 then 'mirror created'

when p.mirror_existence_state=4 then 'mirror down before create'

when p.mirror_existence_state=5 then 'mirror down during create'

when p.mirror_existence_state=6 then 'mirror drop pending'

when p.mirror_existence_state=7 then 'mirror only drop remains'

else 'unknown state:' || p.mirror_existence_state end as mirror_existence_state

FROM gp_persistent_relation_node p WHERE (p.persistent_state not in (0,2)

or p.mirror_existence_state not in (0,1,3))

and p.database_oid in(

SELECT oid FROM pg_database WHERE datname=current_database()

)

4.4 查看有问题的segment primary/mirror信息

$ gpstate -e

转存失败重新上传取消

当看到All segments are running normally时表示所有的节点都没有问题

4.5 显示mirror列表

$ gpstate -m

转存失败重新上传取消

在以上可以所有的Mirror所有的配置信息

4.6 显示所有配置参数

$ psql -d stagging -c " show all; "

转存失败重新上传取消

以上全部列出了集群中配置信息，请仔细查看选项

4.7 查看集群中数据库中的详细信息

4.7.1 查看每个数据库占用的大小

SELECT d.datname as "Name",

pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

ELSE 'No Access' END as "Size",

t.spcname as "Tablespace",

pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"

FROM pg_catalog.pg_database d

JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid

ORDER BY 1;

转存失败重新上传取消

4.7.2 查看每个schema的占用大小

select pg_size_pretty(cast(sum(pg_relation_size( schemaname || '.' || tablename)) as bigint)), schemaname from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname

group by schemaname ORDER BY pg_size_pretty desc;

转存失败重新上传取消

该SQL比较耗时，会查询每个表的大小进行合并，可以详细的查看出数据库下每个schema的大小

4.7.3 查看AO表的相关信息

4.7.3.1 查看数据库中的AO表

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('c', 'a');

4.7.3.2 查看数据库中的AO表的数量

select count(*) from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('c', 'a');

4.7.4 查看堆表的相关信息

4.7.4.1 查看数据库中的堆表

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';

4.7.4.2 查看堆表的数量

select count(*) from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';

4.7.5 查看外部表相关信息

4.7.5.1 查看外部表

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('x') and relkind='r';

4.7.5.2 查看外部表的数量

select count(*) from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('x') and relkind='r';

4.7.6 查看视图的相关信息

4.7.6.1 查看制定schema下视图

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('v')

and nspname in ('schema1','schema2',[schema3,schema4]);

4.7.6.2 查看制定schema下视图的数量

select count(*) from pg_class t1, pg_namespace t2

where t1.relnamespace=t2.oid and relstorage in ('v')

and nspname in ('schema1','schema2',[schema3,schema4]);

4.7.7 查看表的相关信息

4.7.7.1 查看每个表的大小信息

以下SQL会比较耗时，请在查询时注意时间的分配

SELECT t2.nspname,relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other

FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class, pg_namespace t2

WHERE sotd.sotdoid = pg_class.oid

and t2.nspname in ('schema1'[schema2,schema3])

4.8 查看索引的相关信息

4.8.1查看索引大小超过表总大小1/2的系统表大小和索引大小

select indrelid::regclass tab,

pg_size_pretty(avg(pg_total_relation_size(indrelid))::bigint) all_size,

case when avg(pg_relation_size(indrelid))+ sum(pg_total_relation_size(indexrelid)) = avg

(pg_total_relation_size(indrelid)) then 't' else 'f' end,

pg_size_pretty(avg(pg_relation_size(indrelid))::bigint) tab_size,

pg_size_pretty(sum(pg_total_relation_size(indexrelid))::bigint) all_idx_size

from pg_index

where indrelid in (select oid from pg_class where relnamespace in (select oid from pg_namespace where

nspname='pg_catalog') )

group by indrelid::regclass

having sum(pg_total_relation_size(indexrelid))::bigint >= avg(pg_total_relation_size(indrelid))::bigint * 0.5

order by sum(pg_total_relation_size(indexrelid)) desc;

转存失败重新上传取消

在以上可以详细的看到索引大小超过表总大小1/2的系统表大小和索引大小

4.8.2 查看制定schema上表的索引

select * from pg_stat_all_indexes where schemaname in ('schema1',[schema2,schema3]);

4.8.3 索引活跃度监控

详细的参数请查看:

https://www.postgresql.org/docs/10/monitoring-stats.html

4.8.3.1 IO活跃度查看

select * from pg_catalog.pg_statio_all_indexes where schemaname in ('ods','main');

1、使用较少、较多的索引（idx_blks_read + idx_blks_hit）

2、消耗物理IO较多、较少的索引（idx_blks_read）

4.8.3.2 访问活跃度

select * from pg_catalog.pg_stat_all_indexes where schemaname in ('ods','main');

1、使用较少、较多的索引（idx_scan）

2、扫描较少、较多index item的索引（idx_tup_read）

3、从索引读取HEAP TUPLE较多、较少的索引（idx_tup_fetch）

4.8.4 查看Master与Segment上索引不一致的问题

以下语句只要有数据输出表示有的表索引不一致，请进一步查看

select distinct n.nspname,c.relname from gp_dist_random('pg_class') r ,pg_class c,pg_namespace n

where r.oid=c.oid and r.relhasindex<>c.relhasindex

and c.relnamespace = n.oid limit 10

4.9 检查是否使用了a-z 0-9 _ 以外的字母作为对象名

4.9.1 查看数据库是否使用了命名规范

select distinct datname from (select datname,regexp_split_to_table(datname,

)

word from pg_database) t where (not (ascii(word) >=97 and ascii(word) <=122)) and (not (ascii(word) >=48 and

ascii(word) <=57)) and ascii(word)<>95

4.9.2 查看表的索引和视图的命名规范

select current_database(),relname,relkind from (select relname,relkind,

regexp_split_to_table(relname,

) word from pg_class) t

where (not (ascii(word) >=97 and ascii(word) <=122))

and (not (ascii(word) >=48 and ascii(word) <=57)) and ascii(word)<>95 group by 1,2,3

4.9.3 查看数据库中的类型命名规范

select current_database(), typname from (select typname,regexp_split_to_table

(typname,

) word from pg_type) t

where (not (ascii(word) >=97 and ascii(word) <=122)) and (not (ascii(word)

>=48 and ascii(word) <=57)) and ascii(word)<>95 group by 1,2

4.9.4 查看数据库中的储存过程的命名规范

select current_database(), proname from (select proname,regexp_split_to_table

(proname,

) word from pg_proc where proname !~ $$^RI_FKey_$$ ) t where (not (ascii(word) >=97 and ascii(word)

<=122)) and (not (ascii(word) >=48 and ascii(word) <=57)) and ascii(word)<>95 group by 1,2

转存失败重新上传取消

在以上可以看出储存过程使用了不规范命名,在database和schema和table以及FUNCTION都不建议使用大写，如果大写使用时需要加双引使用

4.9.5 查看数据库中的表,视图的的命名规范

select current_database(),nspname,relname,attname from (select nspname,relname,

attname,regexp_split_to_table(attname,

) word from pg_class a,pg_attribute b,pg_namespace c where a.oid=b.

attrelid and a.relnamespace=c.oid ) t where (not (ascii(word) >=97 and ascii(word) <=122)) and (not (ascii

(word) >=48 and ascii(word) <=57)) and ascii(word)<>95 group by 1,2,3,4

转存失败重新上传取消

出现........pg.dropped.138........问题请查看:

https://www.postgresql.org/message-id/CAFj8pRDUSTNoX8zJSMLy%3Dr45z_Tq-OUwd5S7_HGOwtxHZN-yAg%40mail.gmail.com

4.10 查看集群是否处于not balanced状态

4.10.1查看当前的连接数

select * from pg_stat_activity;

或使用以下SQL

select current_query,count(*) from pg_stat_activity group by current_query ORDER by count desc;

4.10.2 查看密码有效期不足30天的用户

select rolname,rolvaliduntil from pg_authid

where rolvaliduntil-now()<'30 days' order by rolvaliduntil;

转存失败重新上传取消

4.10.3 查看每个用户链接的个数

select usename,count(*) from pg_stat_activity group by 1;

转存失败重新上传取消

4.10.4 查看数据库的连接数

select datname, count(*) from pg_stat_activity group by 1;

4.11 查看ctid的值

先查看当前最大的ctid的值

select max(ctid) from pg_class;

-- (19122,123)

创建一张新表

create table xiaoxu_a(id int);

create table xiaoxu_b(id int);

再次查看ctid的值

select ctid,relname from pg_class where relname like 'xiaoxu%';

转存失败重新上传取消

在以上可以看出ctid是递增的，说明当前的数据库没有执行vacuum,可以查找出膨胀比较大的表进行vacuum table 或者vacuum pg_class,vacuum pg_class 需要谨慎，首先需要查看表的个数select count(*) from pg_class;在进行操作。

5 集群巡检过程详细信息

5.1 检查大小超过1GB的表倾斜情况

问题描述：GPDB 为分布式数据库，所有表数据应该均匀分布到所有segment 实例中存储，才能发挥数据库最佳的性能。数据倾斜是指表的数据没有均匀分布到所有 segment 实例中，部分实例存储数据量大，其他实例存储数据量小，甚至没有数据。数据查询过程中出现部分实例繁忙，其他实例空闲，导致数据库性能不均衡。

建议在创建表时使用唯一键作为分布键，例如使用uuid作为分布键做到表分布均匀，代码请查看:https://blog.csdn.net/xfg0218/article/details/90699970

5.1.1 查看超过1GB倾斜率的表

SELECT

'select ''' || schemaname || '.' || tablename || ''' tabname' ||

E' ,pg_size_pretty(sum(pg_relation_size(\'' || schemaname || '.' || tablename || E'\'))::bigint) as

sum_relation_size' ||

E' ,pg_size_pretty(max(pg_relation_size(\'' || schemaname || '.' || tablename || E'\'))) as max_relation_size'

||

E' ,pg_size_pretty(min(pg_relation_size(\'' || schemaname || '.' || tablename || E'\'))) as min_relation_size'||

E' ,max(pg_relation_size(\'' || schemaname || '.' || tablename || E'\'))::numeric(100,1)/min(pg_relation_size

(\'' || schemaname || '.' || tablename || E'\')) skew_info ' || E' from gp_dist_random(\'gp_id\') ' || E'where

pg_relation_size(\'' || schemaname || '.' || tablename || E'\' )<>0;' as question_table_sql

FROM

pg_tables

WHERE

pg_relation_size(tablename) > 1.0 * 1024 * 1024 * 1024

and schemaname not in ('information_schema','pg_catalog')

ORDER BY

pg_relation_size(tablename)

DESC;

5.1.2 查看表在segment上占用大小及倾斜率

运行以上的SQL会把超过1GB倾斜的表的SQL列出来,例如以下SQL:

select 'datafix.lyj_main_ent' tabname ,pg_size_pretty(sum(pg_relation_size('datafix.lyj_main_ent'))::bigint) as

sum_relation_size ,pg_size_pretty(max(pg_relation_size('datafix.lyj_main_ent'))) as max_relation_size ,pg_size_pretty(min(pg_relation_size('datafix.lyj_main_ent'))) as min_relation_size ,max(pg_relation_size('datafix.lyj_main_ent'))::numeric(100,1)/min(pg_relation_size

('datafix.lyj_main_ent')) skew_info from gp_dist_random('gp_id') where

pg_relation_size('datafix.lyj_main_ent' )<>0;

转存失败重新上传取消

在以上可以看出列出了当前表的详细信息,其中字段的详细信息为:

tabname : 当前表的名字

sum_relation_size : 表的大小

max_relation_size : segment 上最大的占用空间

min_relation_size : segment 上最小的占用空间

skew_info : 倾斜率

5.1.3 查看表在segment上占用的行数及百分比

SELECT 'Example Table' AS "Table Name",

max(c) AS "Max Seg Rows", min(c) AS "Min Seg Rows",

(max(c)-min(c))*100.0/max(c) AS "Percentage Difference Between Max & Min"

FROM (SELECT count(*) c, gp_segment_id FROM datafix.lyj_main_ent GROUP BY 2) AS a;

转存失败重新上传取消

Max Seg Rows : 单个segment实例存储占用空间（最大）

Min Seg Rows : 单个segment实例存储占用空间（最小）

Percentage Difference Between Max & Min :最大值和最小值的百分比差异

5.1.4 解决表分布倾斜的情况

5.1.4.1 分布键说明

对以上查询出来的倾斜比较大的表可以使用修改表分布键的形式是数据分布均匀，常见的分布方式有:

1、指定一个字段作为分布键:distributed by(filed1)

2、指定多个字段作为分布键:distributed by(filed1,filed2)

3、随机字段分布键:distributed randomly;

建议使用制定一个字段作为分布键，因为多个字段和随机字段做分布键都可能影响性能的下降,详细创建表的方式请查看:

https://blog.csdn.net/xfg0218/article/details/86473234

5.1.4.2 修改分布键

修改一个或多个字段作为分布键

alter table tablenname set with (reorganize=true) distributed by (filed1[filed2,filed3]) ;

修改随机字段作为分布键

alter table tablename set with (reorganize=true) distributed randomly ;

5.1.4.3 对表进行重新创建

在原表的基础上创建一张压缩表

create table newtanlename with (appendonly = true, compresstype = zlib, compresslevel = 7

,orientation=column, checksum = false,blocksize = 2097152) as

select * from tablename

Distributed by (filed1[filed2]);

获取原始表的权限

select 'grant '||privilege_type||' on tablename to '||grantee||';' from information_schema.table_privileges

where table_schema='schema' and table_name='tablename'

group by grantee,privilege_type;

重命名原表的名字

alter table tablename rename to newtablename;

5.2 检查膨胀率过高的表

Greenplum支持行储存(HEAP储存)与列(append-only)储存,对于AO存储，虽然是appendonly，但实际上GP是支持DELETE和UPDATE的，被删除或更新的行，通过BITMAP来标记删除与修改。AO存储是块级组织，当一个块内的数据大部分都被删除或更新掉时，扫描它浪费的成本实际上是很高的。而PostgreSQL是通过HOT技术以及autovacuum来避免或减少垃圾的。但是Greenplum没有自动回收的worker进程，所以需要人为的触发。

详细介绍请查看:

https://blog.csdn.net/xfg0218/article/details/83031550

shell脚本请下载:

https://github.com/xfg0218/greenplum--summarize/tree/master/201905/greenplum-inspect-ao

5.2.1 查看schema下的AO表

SELECT ns.nspname,pc.relname FROM pg_class pc,pg_namespace ns

WHERE pc.relnamespace = ns.oid AND relstorage IN ('c', 'a')

AND ns.nspname = 'dim';

5.2.2 查看表的膨胀率

select percent_hidden

from gp_toolkit.__gp_aovisimap_compaction_info('dim.t_dex_app_codelist_mapping'::regclass)

ORDER BY percent_hidden desc;

转存失败重新上传取消

5.2.3 对表进行释放空间

使用vacuum tablename即可清理表空间

VACUUM dim.t_dex_app_codelist_mapping;

5.3 检查元数据不一致问题

5.3.1 问题描述

GPDB 元数据就是系统表所存的数据，也叫数据字典。GP 可以

看做一组 PostgreSQL 实例组成的阵列，其每一个实例都存在系统表，这些实

例的大部分系统表存储的数据应该都是相同的；并且在每个实例中，不同的系

统表之间也应存在这严格的外键约束。

元数据不一致就是，以上的情况出现了错误，同一张系统表在不同实例中存

储的数据存在缺失、多余；或者同一个实例中，不同的系统表外键约束出现错

误；该错误原因大都是由于系统宕机，实例故障引起。

该系统存在"Found a total of 130554 issue(s)"，其中大部分异常信息如下：

"No pg_class entry for gp_distribution_policy {'localoid': 12163942} on

master (datacenter-mdw:5432)"

5.3.2 集群检查

$ psql -d stagging -c " select count(0) from gp_distribution_policy where localoid not in (select oid from pg_class);"

count

-------

0

(1 row)

Found a total of 130554 issue(s)

No pg_class entry for gp_distribution_policy {'localoid': 12163942} on

master (gpmdw:5432)

通过日志可以看出该系统存在"Found a total of 130554 issue(s)"，其中大部分异常信息如下："No pg_class entry for gp_distribution_policy {'localoid': 12163942} on master (datacenter-mdw:5432)"，经过分析检查确认，为 master 实例 gp_distribution_policy 和 pg_class 的外键约束出现大量错误数据

5.3.3 问题处理

目前暂不影响使用，但是存在安全隐患，元数据异常很可能会导致部分表无法正常访问，严重可能导致实例宕机、数据库启动失败、segment 实例恢复失败等情况。

该问题需要每一项错误进行逐一排查，清理失效的元数据，修复错误；例如本次 master 外键故障，可以删除 gp_distribution_policy 表中多余的数据。另外，元数据修复需要谨慎处理，处理前需要多次测试，或者由专业工程师操作。

5.4 查看服务器配置参数

数据库参数 gp_vmem_protect_limit 控制每个数据库 segment 实例可使用的内存数量，单位MB

5.4.1 服务器OS参数查看

查看主机映射文件

$ cat seg_host

gpmdw

gpsdw1

gpsdw2

gpsdw3

使用gpcheck -f seg_host

转存失败重新上传取消

当出现以上的ERROR信息时请排查OS系统参数

5.5 查看集群参数详细信息

5.5.1 查看每个segment的内存配置参数

5.5.1.1 查看分配内存信息

gpconfig -s gp_vmem_protect_limit

转存失败重新上传取消

在以上可以看出segment使用了系统默认的内存配置8192MB,改参数按照机器的内存大小可以适当的调大，详见计算如下:

1、计算公式可参考如下：(mem+swap)* 0.9 /单个节点 segment 数量

2、例如master节点上有252G的内存，segment个数为2个，分配最高的内存为:

252*0.9 / 2 ≈ 110GB（112640 MB）

3、例如数据节点上有252G的内存，segment个数为12个，分配最高的内存为:

252*0.9/12 ≈ 18GB（18432MB）

5.5.1.2 修改内存参数

登录到master节点上执行以下命令即可

gpconfig -c gp_vmem_protect_limit -m 112640 -v 18432

-c ： 改变参数的名称

-m : 修改主备master的内存的大小一般的和-v一块使用

-v ： 此值用于所有的segments，mirrors和master的修改

5.5.2 查看shared_buffers(共享缓冲区)的内存

5.5.2.1 查看系统配置的参数

$ gpconfig -s shared_buffers

转存失败重新上传取消

5.5.2.2 参数详解

只能配置segment节点，用作磁盘读写的内存缓冲区,开始可以设置一个较小的值，比如总内存的15%，然后逐渐增加，过程中监控性能提升和swap的情况。以上的缓冲区的参数为125MB，此值不易设置过大，过大或导致以下错误

[WARNING]:-FATAL: DTM initialization: failure during startup recovery, retry failed, check segment status (cdbtm.c:1603)，详细的配置请查看

http://gpdb.docs.pivotal.io/4390/guc_config-shared_buffers.html

5.5.2.3 修改参数

修改配置

gpconfig -c shared_buffers -v 1024MB

gpconfig -r shared_buffers -v 1024MB

5.5.3 查看max_connections(最大连接数)

5.5.3.1 查看最大连接数参数

$ gpconfig -s max_connections

转存失败重新上传取消

5.5.3.2 参数详解

此参数为客户端链接数据库的连接数，按照个人数据库需求配置，参数详解请查看:

https://gpdb.docs.pivotal.io/4380/guc_config-max_connections.html

5.5.4 查看block_size(磁盘块)的大小

5.5.4.1 查看磁盘块的大小

$ gpconfig -s block_size

转存失败重新上传取消

5.5.4.2 参数详解

此参数表示表中的数据以默认的参数32768 KB作为一个文件，参数的范围8192KB - 2MB ,范围在8192 - 2097152 ，值必须是8192的倍数，使用时在blocksize = 2097152即可

5.5.5 查看work_mem的值

5.5.5.1 查看集群中work_mem的配置大小

$ gpconfig -s work_mem

转存失败重新上传取消

5.5.5.2 参数详解

work_mem 在segment用作sort,hash操作的内存大小当PostgreSQL对大表进行排序时，数据库会按照此参数指定大小进行分片排序，将中间结果存放在临时文件中，这些中间结果的临时文件最终会再次合并排序，所以增加此参数可以减少临时文件个数进而提升排序效率。当然如果设置过大，会导致swap的发生，所以设置此参数时仍需谨慎。刚开始可设置总内存的5%

5.5.5.3 修改参数

修改系统配置文件,重启集群使之生效

gpconfig -c work_mem -v 128MB

或在客户端session设置此参数

SET work_mem TO '64MB'

销毁session参数为:

reset work_mem;

5.5.6 查看statement_mem的值

5.5.6.1 查看集群中statement_mem的值

$ gpconfig -s statement_mem

转存失败重新上传取消

5.5.6.2 参数详解

设置每个查询在segment主机中可用的内存，该参数设置的值不能超过max_statement_mem设置的值，如果配置了资源队列，则不能超过资源队列设置的值。

5.5.6.3 修改参数

修改配置后重启生效

gpconfig -c statement_mem -v 256MB

5.5.7 查看gp_workfile_limit_files_per_query的值

5.5.7.1 查看此值的大小

$ gpconfig -s gp_workfile_limit_files_per_query

转存失败重新上传取消

5.5.7.1 参数详解

SQL查询分配的内存不足，Greenplum数据库会创建溢出文件（也叫工作文件）。在默认情况下，一个SQL查询最多可以创建 100000 个溢出文件，这足以满足大多数查询。

该参数决定了一个查询最多可以创建多少个溢出文件。0 意味着没有限制。限制溢出文件数据可以防止失控查询破坏整个系统。

如果数据节点的内存是512G的内存,表的压缩快的大小(block_size)是2M的话，计算为: 512G + 2 * 1000000 / 1024 ≈ 707 G 的空间，一般的表都是可以的，一般的此值不需要修改

5.5.8 查看gp_resqueue_priority_cpucores_per_segment的值

5.5.8.1 查看此值的大小

$ gpconfig -s gp_resqueue_priority_cpucores_per_segment

转存失败重新上传取消

5.5.8.2 参数详解

每个segment分配的分配的cpu的个数，例如:在一个20核的机器上有4个segment,则每个segment有5个核，而对于master节点则是20个核，master节点上不运行segment的信息，因此master反映了cpu的使用情况

5.5.8.3 修改参数

按照不同集群的核数以及segment修改此参数即可，下面的实例是修改成8核

gpconfig -c gp_resqueue_priority_cpucores_per_segment -v 8

5.6 备 Master 镜像情况

5.6.1 概述

作为重要的数据库，备master是必不可少的，经常查看备master健康情况能有效的替换主master节点。

5.6.2 查看备master的运行情况

$ gpstate -f

转存失败重新上传取消

在以上可以看出备用master的信息是gpdev153上数据目录在/data/gpmaster/gpseg-1

5.6.3 备master需要检测的项

5.7 segment 镜像情况

5.7.1 查看集群中的镜像分布情况

$ gpstate -m

转存失败重新上传取消

主要查看是否有ERROR信息以及Data Status的状态是否是同步状态

5.8 资源队列情况

5.8.1 用户与资源队列检查

5.8.1.1 查看负载管理资源队列的状态和活动

该视图允许管理员查看到一个负载管理资源队列的状态和活动。它显示在系统中一个特定的资源队列有多少查询正在等待执行以及有多少查询当前是活动的。

select * from gp_toolkit.gp_resqueue_status;

转存失败重新上传取消

参数详解如下:

5.8.1.2 查看当前用户使用的是什么队列

该视图显示与角色相关的资源队列。该视图能够被所有用户访问。

select * from gp_toolkit.gp_resq_role;

转存失败重新上传取消

5.8.1.3 查看队列的活动负载状态

对于那些有活动负载的资源队列，该视图为每一个通过资源队列提交的活动语句显示一行。该视图能够被所有用户访问。

select * from gp_toolkit. gp_resq_activity;

转存失败重新上传取消

5.8.1.4 查看负载管理特性的Greenplum数据库资源队列的信息

select * from pg_catalog.pg_resqueue;

5.8.1.5 查看队列资源的状态

SELECT * FROM pg_resqueue_status;

5.8.1.6 查看每个资源队列的配置情况

select * from pg_resqueue_attributes;

转存失败重新上传取消

5.8.1.7 修改资源队列的语句

ALTER RESOURCE QUEUE pg_default WITH (priority=max);

pg_default : 资源队列名称

priority=max : 需要修改的值

5.9 Statistics 状态检查

该视图显示那些没有统计信息的表，因此可能需要在表上执行ANALYZE tablename命令。

5.9.1 查看所有的表

select * from gp_toolkit.gp_stats_missing where smisize='f';

正在上传…重新上传取消

5.9.2 查看指定schema下的表

select * from gp_toolkit.gp_stats_missing where smisize='f' and smischema in ('riskbell','main');

正在上传…重新上传取消

字解释如下:

5.10 Skew 状态检查

Skew阶段主要查看表的倾斜的表，过高的表倾斜由于查询会落到一个segment上，影响了集群的性能，建议分布数据做到分布均匀。 请看 < 5.1 检查大小超过1GB的表倾斜情况> 详解

5.11 Bloat 状态

Bloat 阶段主要查看表的膨胀率，过高的表的膨胀率会影响表的查询以及增删改查性能，原因是表中存在的不可见的文件过大,建议定期的清理。请看 < 5.2 检查膨胀率过高的表 > 详解

5.12 集群系统性能

5.12.1 DB性能查看

以下主要对集群的性能测试，建议在安装数据库时检测一次，并把检测的结果保存，以便于下次测试结果相比较。性能测试的案例请查看:

https://blog.csdn.net/xfg0218/article/details/82785187

5.12.2 SQL 锁检

select b.query_start,a.* from

gp_toolkit.gp_locks_on_relation a, pg_stat_activity b where a.lorpid=b.procpid

and a.lorrelname not like 'pg_%' and a.lorrelname not like 'gp_%' order by 1

desc;

正在上传…重新上传取消

以上SQL显示当前所有表上持有锁，以及查询关联的锁的相关联的会话信息。

5.12.3 集群硬件性能查看

1、请使用gpcheckperf 命令检测集群中磁盘的读写网卡的信息等，详细的过程请查看:

https://blog.csdn.net/xfg0218/article/details/90711569

2、在测试期间请使用以下命令收集服务器的指标信息并保存

$ cat cluster_hosts

gpmdw

gpsdw1

gpsdw2

gpsdw3

nohup gpssh -f cluster_hosts sar -A 1 20000 >> /home/gpadmin/cluster-target.txt &

详细的查看指标的信息请查看:

https://blog.csdn.net/xfg0218/article/details/91490999

3、根据文件cluster-target.txt过滤出需要查看的机器的信息在kSar上查看即可,效果如下所示:

正在上传…重新上传取消

5.13 查看集群系统元数据

nohup gpcheckcat -p 5432 stagging>> greenplum-metadata.log &

$ head -n 20 greenplum-metadata.log

Truncated batch size to number of primaries: 49

Connected as user 'gpadmin' to database 'ch****', port '5432', gpdb version '5.11'

-------------------------------------------------------------------

Batch size: 49

Found and dropped 8 unbound temporary schemas

Performing test 'unique_index_violation'

Total runtime for test 'unique_index_violation': 0:12:33.53

Performing test 'duplicate'

Total runtime for test 'duplicate': 0:00:48.59

Performing test 'missing_extraneous'

Total runtime for test 'missing_extraneous': 0:02:28.29

************************

5.14 查看集群中分布键倾斜率比较高的表

表的分布及均匀会造成查询数据时性能会大大下降，定期清理倾斜率高的表会提高集群的整体性能，详见shell脚本请查看:

https://github.com/xfg0218/greenplum--summarize/tree/master/201906/greenplum-table-percentage

5.15 使用gpcheckcat命令检测集群

5.15.1 gpcheckcat 检测项说明

gpcheckcat详细的参数说明请查看:

https://blog.csdn.net/xfg0218/article/details/90910764

5.15.2 检查master,segment的catalog一致性

对每一个数据执行以下命令,默认的是

$ gpcheckcat -O

*************

SUMMARY REPORT

===================================================================

Completed 11 test(s) on database 'template1' at 2019-06-05 17:40:32 with elapsed time 0:00:38

Found no catalog issue

real 0m20.840s

user 0m0.370s

sys 0m0.180s

在以上可以看出没有找到问题

5.15.3检查持久化表的catalog一致性。

对每一个数据执行以下命令

$ time gpcheckcat -R persistent

**************

SUMMARY REPORT

===================================================================

Completed 1 test(s) on database 'template1' at 2019-06-05 17:44:09 with elapsed time 0:00:11

Found no catalog issue

real 0m12.840s

user 0m0.370s

sys 0m0.180s

如果有输出，说明有不一致的持久化表的catalog。

5.15.4 检查pg_class与pg_attribute是否不一致

对每一个数据执行以下命令

$ gpcheckcat -R pgclass

*************

SUMMARY REPORT

===================================================================

Completed 1 test(s) on database 'template1' at 2019-06-05 17:51:46 with elapsed time 0:00:00

Found no catalog issue

real 0m1.804s

user 0m0.215s

sys 0m0.071s

如果有输出，说明pg_class与pg_attribute不一致。

转存失败重新上传取消

5.16 表活跃度监控

5.16.1 IO活跃度的表

select * from pg_catalog.pg_statio_all_tables;

1、使用(索引+表)较少、较多的表（heap_blks_read+heap_blks_hit+toast_blks_read+toast_blks_hit+idx_blks_read+idx_blks_hit+tidx_blks_read+tidx_blks_hit）

2、消耗IO较多的(索引+表)表（heap_blks_read+toast_blks_read+idx_blks_read+tidx_blks_read）

3、使用(索引)较少、较多的表（idx_blks_read+idx_blks_hit+tidx_blks_read+tidx_blks_hit）

4、消耗IO较多的(索引)表（idx_blks_read+tidx_blks_read）

5、使用(表)较少、较多的表（heap_blks_read+heap_blks_hit+toast_blks_read+toast_blks_hit）

6、消耗IO较多的(表)表（heap_blks_read+toast_blks_read）

5.16.2 表的访问活跃度

select * from pg_catalog.pg_stat_all_tables;