现在企业客户中搭建的Greenplum集群服务器数量是越来越大，在电信行业和银行业，搭建50台服务器以上的Greenplum集群越来越多。而集群服务器数量越多也就代表故障发生率越高。作为Greenplum的DBA和运维人员，不单只关注Greenplum本身，还要关注集群中各硬件的状况，及时发现及时处理。硬盘状态、阵列卡状态、硬件告警、操作系统告警、空间使用率等都是应关注的重点。这些都可通过厂商提供的工具，编写监控程序，SNMP协议对接企业监控平台等手段提升日常巡检和监控的效率。

要保持一个Greenplum数据库系统高效运行，必须对数据库定期清理过期数据并且更新表统计信息，这样查询优化器才能有准确的信息。

Greenplum数据库要求定期执行特定的任务来达到最优性能。这里讨论的任务都是必须的，但数据库管理员可以使用标准的UNIX工具（如cron脚本）来自动化这些任务。管理员建立适当的脚本并且检查它们是否成功执行。

日常维护关注点

针对Greenplum，DBA需要关注的重点：

（1）Greenplum的状态：Standby master的同步状态往往容易被忽略。通过监控平台或者脚本程序，能够及时告警则最好。

（2）系统表：日常系统表维护（vacuum analyze），在系统投产时就应该配置好每天执行维护。

（3）统计信息收集：统计信息的准确性影响到运行效率，用户表应该及时收集统计信息。在应用程序中增加收集统计信息的处理逻辑，通过脚本定时批量收集统计信息，或者两者相结合。针对分区表日常可按需收集子分区的统计信息，可节省时间提升效率。

（4）表倾斜：表倾斜情况应该DBA的关注点之一，但无需每天处理。

（5）表膨胀：基于postgresql的MVCC机制，表膨胀情况不能忽视。重点应该关注日常更新和删除操作的表。

（6）报错信息：在日志中错误信息多种多样，大部分不是DBA需要关注的。应该重点关注PANIC、OOM、Internal error等关键信息。

官方文档

一切尽在官方文档中......

http://docs-cn.greenplum.org/

http://docs-cn.greenplum.org/v6/homenav.html

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/9ccd913ea8be24b9.md

日常运维查询命令

启动和关闭

数据库启动：gpstart

常用可选参数： -a : 直接启动，不提示终端用户输入确认

-m:只启动master 实例，主要在故障处理时使用

数据库停止：gpstop

常用可选参数：-a：直接停止，不提示终端用户输入确认

-m：只停止master 实例，与gpstart –m 对应使用

-M fast：停止数据库，中断所有数据库连接，回滚正在运行的事务

-u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中运行时参数，当改动参数配置时候使用。

总结

-- 启动
gpstart -a

-- 关闭:默认情况下，如果有任何客户端连接存在，就不允许关闭Greenplum数据库。 使用-M fast选项可以在关闭前回滚所有正在进行中的事务并且中断所有连接。
gpstop -M fast -a

-- 重载配置文件
gpstop -u

-- 不用输入Y进行确定而直接重启
gpstop -M fast -ra

-- 启动

gpstart -a

-- 关闭:默认情况下，如果有任何客户端连接存在，就不允许关闭Greenplum数据库。使用-M fast选项可以在关闭前回滚所有正在进行中的事务并且中断所有连接。

gpstop -M fast -a

-- 重载配置文件

gpstop -u

-- 不用输入Y进行确定而直接重启

gpstop -M fast -ra

查看实例配置和状态

select * from gp_segment_configuration order by 1 ;

1	select * from gp_segment_configuration order by 1 ;

主要字段说明：

Content：该字段相等的两个实例，是一对Ｐ（primary instance）和Ｍ（mirror Instance)

Isprimary：实例是否作为primary instance 运行

Valid：实例是否有效，如处于false 状态，则说明该实例已经down 掉。

Port：实例运行的端口

Datadir:实例对应的数据目录

集群状态查询

查询命令：gpstate

显示Greenplum数据库运行状态，详细配置等信息

常用可选参数：-c：primary instance 和 mirror instance 的对应关系

-m：只列出mirror 实例的状态和配置信息

-f：显示standby master 的详细信息

-Q：显示状态综合信息

该命令默认列出数据库运行状态汇总信息，常用于日常巡检。

评：最开始由于网卡驱动的问题，做了mirror后，segment经常down掉，用-Q参数查询综合信息还是比较有用的。

查看集群主实例与镜像实例同步状态

查询命令：gpstate -m

Status均为Passive，Data Status均为为Synchronized则正常，否则异常

负载查询

使用vmstat命令可以监控 CPU 使用、进程状态、内存使用、虚拟内存使用、硬盘输入/输出状态等信息，常使用方法为：vmstat [刷新延时刷新次数]

也可以使用gpcc历史视图查询，参考：https://www.xmmup.com/greenplumguanfangjiankonggongjugpccjianjiejishiyong.html

磁盘空间查询

数据库查看使用量

select dfsegment,dfhostname,dfdevice,round(dfspace/1024/1024,2) as free_disk_GB 
from gp_toolkit.gp_disk_free 
ORDER BY dfsegment  ;

-- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位)

select dfsegment,dfhostname,dfdevice,round(dfspace/1024/1024,2) as free_disk_GB

from gp_toolkit.gp_disk_free

ORDER BY dfsegment ;

-- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位)

系统命令行查看（在此例中“host_list”为所有服务器节点清单，“data”为数据存放目录，根据实际目录文件名查询即可）

gpssh -f host_list -e "df -h |grep data"

1	gpssh -f host_list -e "df -h \|grep data"

查询数据库、表占用空间大小

select pg_size_pretty(pg_relation_size('schema.tablename'));

select pg_size_pretty(pg_database_size('databasename'));

--  1.查看所有库大小
select sodddatname,sodddatsize/1024/1024 as db_size_MB 
from gp_toolkit.gp_size_of_database 
order by sodddatname;

-- 2.查看库下面的所有schema占用的磁盘空间
select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB 
from gp_toolkit.gp_size_of_schema_disk;

-- 单个schema大小
SELECT  pg_size_pretty(sum(pg_total_relation_size('"' || nspname || '".' || relname))) AS total_size
FROM  pg_class c
JOIN  pg_namespace n ON n.oid = c.relnamespace
WHERE nspname = 'public';

-- 3.查看schema下所有表和索引占用的磁盘空间
SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB
FROM gp_toolkit.gp_size_of_all_table_indexes 
order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;

-- 4.查看表占用空间(表大小)
select (pg_relation_size('public.tb_name'));

select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB  
from gp_toolkit.gp_size_of_table_uncompressed 
order by sotuschemaname, table_SIZE_MB desc,sotutablename

-- 5.查看膨胀表
select * from gp_toolkit.gp_bloat_diag 
order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;

-- 没有统计信息且可能需要ANALYZE的表。
select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc;

select pg_size_pretty(pg_relation_size('schema.tablename'));

select pg_size_pretty(pg_database_size('databasename'));

-- 1.查看所有库大小

select sodddatname,sodddatsize/1024/1024 as db_size_MB

from gp_toolkit.gp_size_of_database

order by sodddatname;

-- 2.查看库下面的所有schema占用的磁盘空间

select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB

from gp_toolkit.gp_size_of_schema_disk;

-- 单个schema大小

SELECT pg_size_pretty(sum(pg_total_relation_size('"' || nspname || '".' || relname))) AS total_size

FROM pg_class c

JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE nspname = 'public';

-- 3.查看schema下所有表和索引占用的磁盘空间

SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB

FROM gp_toolkit.gp_size_of_all_table_indexes

order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;

-- 4.查看表占用空间(表大小)

select (pg_relation_size('public.tb_name'));

select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB

from gp_toolkit.gp_size_of_table_uncompressed

order by sotuschemaname, table_SIZE_MB desc,sotutablename

-- 5.查看膨胀表

select * from gp_toolkit.gp_bloat_diag

order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;

-- 没有统计信息且可能需要ANALYZE的表。

select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc;

必须在数据库所对应的存储系统里，至少保留30%的自由空间，日常巡检，要检查存储空间的剩余容量。

评：可以查看任何数据库对象的占用空间，pg_size_pretty可以显示如mb之类的易读数据，另外，可以与pg_tables，pg_indexes之类的系统表链接，统计出各类关于数据库对象的空间信息。

查询数据库大小

参考：https://www.xmmup.com/greenplumchaxunshujukudaxiao.html

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       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
UNION ALL
SELECT '总计' as "Name",
       '' as "Owner",
       ''  as "Encoding",
       ''  as "Collate",
       ''  as "Ctype",
       ''  AS "Access privileges",
       pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size",
       ''  as "Tablespace",
       ''  as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

-- 或
select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ;

-- 或
\l+

SELECT d.datname as "Name",

pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

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

d.datcollate as "Collate",

d.datctype as "Ctype",

pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",

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

UNION ALL

SELECT '总计' as "Name",

'' as "Owner",

'' as "Encoding",

'' as "Collate",

'' as "Ctype",

'' AS "Access privileges",

pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size",

'' as "Tablespace",

'' as "Description"

FROM pg_catalog.pg_database d

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

ORDER BY 1;

-- 或

select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ;

-- 或

\l+

表占用空间大小

select pg_size_pretty(pg_total_relation_size('test'));
select pg_size_pretty(pg_relation_size('test'));
select pg_size_pretty(pg_table_size('test'));
select pg_size_pretty(pg_indexes_size('test'));

SELECT 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
WHERE sotd.sotdoid = pg_class.oid ORDER BY relname;

select pg_size_pretty(pg_total_relation_size('test'));

select pg_size_pretty(pg_relation_size('test'));

select pg_size_pretty(pg_table_size('test'));

select pg_size_pretty(pg_indexes_size('test'));

SELECT 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

WHERE sotd.sotdoid = pg_class.oid ORDER BY relname;

索引占用空间大小

select pg_size_pretty(pg_total_relation_size('test'));
select pg_size_pretty(pg_relation_size('test'));
select pg_size_pretty(pg_table_size('test'));
select pg_size_pretty(pg_indexes_size('test'));

SELECT soisize/1024/1024 as size_MB, relname as indexname
FROM pg_class, gp_toolkit.gp_size_of_index
WHERE pg_class.oid = gp_size_of_index.soioid
AND pg_class.relkind='i';

select pg_size_pretty(pg_total_relation_size('test'));

select pg_size_pretty(pg_relation_size('test'));

select pg_size_pretty(pg_table_size('test'));

select pg_size_pretty(pg_indexes_size('test'));

SELECT soisize/1024/1024 as size_MB, relname as indexname

FROM pg_class, gp_toolkit.gp_size_of_index

WHERE pg_class.oid = gp_size_of_index.soioid

AND pg_class.relkind='i';

分区表大小


-- 分区表信息和大小
select * from (
SELECT pt.schemaname,pt.tablename,pt.partitiontype,pt.partitionschemaname,pt.partitiontablename,pt.partitionname,pt.partitionposition||'' partitionposition,pt.partitionlistvalues,pt.partitionlevel||'' partitionlevel,pt.partitionboundary,
pg_size_pretty( pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_tb_size,
pg_size_pretty( pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_index_size
FROM pg_partitions pt
union ALL
SELECT pt.schemaname,pt.tablename,pt.partitiontype,'','','','','','','',
pg_size_pretty(sum(pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_tb_size,
pg_size_pretty(sum(pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_index_size
FROM pg_partitions pt  GROUP BY pt.schemaname,pt.tablename,pt.partitiontype
) aa
WHERE aa.tablename='topiclog_month'
order by aa.partitionname
;

-- 分区表信息和大小

select * from (

SELECT pt.schemaname,pt.tablename,pt.partitiontype,pt.partitionschemaname,pt.partitiontablename,pt.partitionname,pt.partitionposition||'' partitionposition,pt.partitionlistvalues,pt.partitionlevel||'' partitionlevel,pt.partitionboundary,

pg_size_pretty( pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_tb_size,

pg_size_pretty( pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_index_size

FROM pg_partitions pt

union ALL

SELECT pt.schemaname,pt.tablename,pt.partitiontype,'','','','','','','',

pg_size_pretty(sum(pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_tb_size,

pg_size_pretty(sum(pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_index_size

FROM pg_partitions pt GROUP BY pt.schemaname,pt.tablename,pt.partitiontype

) aa

WHERE aa.tablename='topiclog_month'

order by aa.partitionname

;

查看GreenPlum表的分布键和压缩类型

SELECT
    n.nspname AS "schema",
    C.relname AS "tb_name",
CASE WHEN P.policytype = 'p' THEN '分区策略' 
        WHEN P.policytype = 'r' THEN '复制策略' 
END "policytype",
  P.numsegments,
    P.distkey,
    pg_get_table_distributedby ( C.oid ) "distributed_key",
    C.reloptions,
    case c.relstorage
  when 'a' then ' append-optimized'
  when 'c' then 'column-oriented'
  when 'h' then 'heap'
  when 'v' then 'virtual'
  when 'x' then 'external table'
 end as "data_storage_mode"
FROM
    pg_class  C 
LEFT JOIN gp_distribution_policy P ON C.oid = P.localoid
LEFT JOIN pg_namespace n ON C.relnamespace = n.oid 
where n.nspname NOT IN ( 'pg_catalog', 'pg_toast','information_schema','gp_toolkit' )
and  C.relkind in ('r','m') 
and P.policytype is not null
order by n.nspname,C.relname;

SELECT

n.nspname AS "schema",

C.relname AS "tb_name",

CASE WHEN P.policytype = 'p' THEN '分区策略'

WHEN P.policytype = 'r' THEN '复制策略'

END "policytype",

P.numsegments,

P.distkey,

pg_get_table_distributedby ( C.oid ) "distributed_key",

C.reloptions,

case c.relstorage

when 'a' then ' append-optimized'

when 'c' then 'column-oriented'

when 'h' then 'heap'

when 'v' then 'virtual'

when 'x' then 'external table'

end as "data_storage_mode"

FROM

pg_class C

LEFT JOIN gp_distribution_policy P ON C.oid = P.localoid

LEFT JOIN pg_namespace n ON C.relnamespace = n.oid

where n.nspname NOT IN ( 'pg_catalog', 'pg_toast','information_schema','gp_toolkit' )

and C.relkind in ('r','m')

and P.policytype is not null

order by n.nspname,C.relname;

查询对象类型和个数

select 
    nsp.nspname as SchemaName
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'composite type'
        when 't' then 'TOAST'
        when 'f' then 'foreign table'
        when 'p' then 'partitioned_table'
        when 'I' then 'partitioned_index'
        else cls.relkind::text
    end as ObjectType,
    COUNT(*) cnt
from pg_class cls
join pg_namespace nsp 
 on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')
  and nsp.nspname not like 'pg_toast%'
GROUP BY nsp.nspname,cls.relkind
UNION all
SELECT n.nspname as "Schema",
 'pg_proc' as "pg_proc",
 COUNT(*) cnt
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')
GROUP BY n.nspname 
order by SchemaName,ObjectType;

select

nsp.nspname as SchemaName

,case cls.relkind

when 'r' then 'TABLE'

when 'm' then 'MATERIALIZED_VIEW'

when 'i' then 'INDEX'

when 'S' then 'SEQUENCE'

when 'v' then 'VIEW'

when 'c' then 'composite type'

when 't' then 'TOAST'

when 'f' then 'foreign table'

when 'p' then 'partitioned_table'

when 'I' then 'partitioned_index'

else cls.relkind::text

end as ObjectType,

COUNT(*) cnt

from pg_class cls

join pg_namespace nsp

on nsp.oid = cls.relnamespace

where nsp.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')

and nsp.nspname not like 'pg_toast%'

GROUP BY nsp.nspname,cls.relkind

UNION all

SELECT n.nspname as "Schema",

'pg_proc' as "pg_proc",

COUNT(*) cnt

FROM pg_catalog.pg_proc p

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

WHERE pg_catalog.pg_function_is_visible(p.oid)

AND n.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')

GROUP BY n.nspname

order by SchemaName,ObjectType;

查看表数据分布情况（数据倾斜）

参考：https://www.xmmup.com/greenplumxingnengdiaoyouzhishujuqingxie.html


 -- 用count(*)方式计算每个segment上的记录数，慢，不建议
 select gp_segment_id, count(*) from table_name group by 1 order by 1;

 -- 计算一张表在不同segment上所占空间来评估是否发生数据倾斜，推荐
   select gp_segment_id, pg_size_pretty(pg_relation_size('table_name'))
   from gp_dist_random('gp_id') order by 1;

-- 真实大小 
  select pg_size_pretty(sum(tbsize)) from (
 select gp_segment_id, (pg_relation_size('table_name')) tbsize
   from gp_dist_random('gp_id') order by 1) a;

-- 该视图通过计算存储在每个Segment上的数据的变异系数（CV）来显示数据分布倾斜。
select * from gp_toolkit.gp_skew_coefficients;

-- 该视图通过计算在表扫描过程中系统空闲的百分比来显示数据分布倾斜，这是一种数据处理倾斜的指示器。
select * from gp_toolkit.gp_skew_idle_fractions;   

在命令运行： gpskew -t public.ate -a postgres

-- 如果表的分布键合理，直接重分布数据：
alter table table_name set with(reorganize=true);

-- 指定分步键重新平衡
alter table table_name set distributed by (col1,col2...);

-- 随机分布
alter table bi.fat_zy_cwsyqk  set distributed  randomly;

-- 用count(*)方式计算每个segment上的记录数，慢，不建议

select gp_segment_id, count(*) from table_name group by 1 order by 1;

-- 计算一张表在不同segment上所占空间来评估是否发生数据倾斜，推荐

select gp_segment_id, pg_size_pretty(pg_relation_size('table_name'))

from gp_dist_random('gp_id') order by 1;

-- 真实大小

select pg_size_pretty(sum(tbsize)) from (

select gp_segment_id, (pg_relation_size('table_name')) tbsize

from gp_dist_random('gp_id') order by 1) a;

-- 该视图通过计算存储在每个Segment上的数据的变异系数（CV）来显示数据分布倾斜。

select * from gp_toolkit.gp_skew_coefficients;

-- 该视图通过计算在表扫描过程中系统空闲的百分比来显示数据分布倾斜，这是一种数据处理倾斜的指示器。

select * from gp_toolkit.gp_skew_idle_fractions;

在命令运行： gpskew -t public.ate -a postgres

-- 如果表的分布键合理，直接重分布数据：

alter table table_name set with(reorganize=true);

-- 指定分步键重新平衡

alter table table_name set distributed by (col1,col2...);

-- 随机分布

alter table bi.fat_zy_cwsyqk set distributed randomly;

如数据分布不均匀，将发挥不了并行计算的优势，严重影响性能。

评：非常有用，gp要保障数据分布均匀。

分步键的选择参数gp_create_table_random_default_distribution参考：https://www.xmmup.com/greenplumchuangjianheguanlibiao.html#can_shugp_create_table_random_default_distribution

查看数据库数据分布情况（数据倾斜）

select gp_segment_id,pg_size_pretty(pg_catalog.pg_database_size('DW')) from gp_dist_random('gp_id') order by 1;

1	select gp_segment_id,pg_size_pretty(pg_catalog.pg_database_size('DW')) from gp_dist_random('gp_id') order by 1;

会话、锁信息管理

会话查询、慢查询SQL

SELECT pgsa.pid,
       pgsa.sess_id,
      pgsa.client_port,
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state, 
      pgsa.waiting,
      pgsa.waiting_reason,
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      state_change,
      pgsa.query_start AS query_start, 
      TRUNC(EXTRACT( epoch  FROM  (NOW() - pgsa.xact_start)  )) AS xact_stay, 
      trunc(EXTRACT( epoch  FROM  (NOW() - pgsa.query_start) )) AS query_stay, 
      REPLACE(pgsa.QUERY, chr(10),  ' ') AS QUERY,
      yc_filesize_GB,
      yc_numfiles, 
      'select pg_terminate_backend('||pgsa.pid||');' kill1,
      'select pg_cancel_backend('||pgsa.pid||');' kill2 
    FROM   pg_stat_activity AS pgsa
        -- 溢出文件大小查询
    LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as  yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d
    ON pgsa.pid=d.pid
    WHERE   pgsa.state not in ( 'idle' ,'idle in transaction','idle in transaction (aborted)' )
ORDER BY  query_stay,xact_stay DESC
LIMIT  100;

-- 会话占用内存
CREATE EXTENSION gp_internal_tools;
SELECT  * 
FROM    session_state.session_level_memory_consumption d 
WHERE   d.datname IS NOT NULL 
ORDER BY
    d.vmem_mb DESC,
    d.sess_id,
    d.segid 
    LIMIT 20;

select datid,  --数据库OID
      datname,  --数据库名
      pid,  --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)
      sess_id,  --会话线程号
      usesysid,  --role ID号
      usename,  --role名称
      client_addr,  --客户端地址
      client_port,  --客户端端口
      application_name,  --客户端应用名称
      substr(query,1,80)  --请求SQL，较长SQL截取部分
      query_start,  --请求开始时间
      backend_start,  --backend进程开始时间，即用户连接数据库时建立会话的时间
      xact_start,  --事务开始时间
      waiting,  --是否等待True if waiting on a lock, false if not waiting
      waiting_reason  --等待原因. The value can be:lock, replication, or resgroup
    from pg_stat_activity where state<>'idle';

SELECT pgsa.pid,

pgsa.sess_id,

pgsa.client_port,

pgsa.datname AS datname,

pgsa.usename AS usename,

(SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,

pgsa.client_addr client_addr,

pgsa.application_name AS application_name,

pgsa.state AS state,

pgsa.waiting,

pgsa.waiting_reason,

pgsa.backend_start AS backend_start,

pgsa.xact_start AS xact_start,

state_change,

pgsa.query_start AS query_start,

TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,

trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,

REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,

yc_filesize_GB,

yc_numfiles,

'select pg_terminate_backend('||pgsa.pid||');' kill1,

'select pg_cancel_backend('||pgsa.pid||');' kill2

FROM pg_stat_activity AS pgsa

-- 溢出文件大小查询

LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d

ON pgsa.pid=d.pid

WHERE pgsa.state not in ( 'idle' ,'idle in transaction','idle in transaction (aborted)' )

ORDER BY query_stay,xact_stay DESC

LIMIT 100;

-- 会话占用内存

CREATE EXTENSION gp_internal_tools;

SELECT *

FROM session_state.session_level_memory_consumption d

WHERE d.datname IS NOT NULL

ORDER BY

d.vmem_mb DESC,

d.sess_id,

d.segid

LIMIT 20;

select datid, --数据库OID

datname, --数据库名

pid, --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)

sess_id, --会话线程号

usesysid, --role ID号

usename, --role名称

client_addr, --客户端地址

client_port, --客户端端口

application_name, --客户端应用名称

substr(query,1,80) --请求SQL，较长SQL截取部分

query_start, --请求开始时间

backend_start, --backend进程开始时间，即用户连接数据库时建立会话的时间

xact_start, --事务开始时间

waiting, --是否等待True if waiting on a lock, false if not waiting

waiting_reason --等待原因. The value can be:lock, replication, or resgroup

from pg_stat_activity where state<>'idle';

pg_stat_activity 该表能查看到当前数据库连接的IP 地址，用户名，提交的查询等。

另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。ps -ef |grep -i postgres |grep -i con

锁查询


SELECT * FROM pg_locks d where d.pid in (192019,191474)  order by pid;
select * from gp_toolkit.gp_locks_on_relation ;

SELECT pg_locks.pid as pid,  --进程ID
      transactionid as transaction_id, --事务ID
             pd.datname db_name,
      nspname as schemaname,  --schema名
      relname as object_name,  --对象名
      locktype as lock_type,  --锁类型
      mode lock_mode,  --锁模式
      CASE
        WHEN granted = 'f' THEN
         'get_lock'
        WHEN granted = 't' THEN
         'wait_lock'
      END lock_satus,   --锁状态：持有锁|等待锁
      CASE
        WHEN waiting = 'false' THEN
         'already get lock,sql executing'
        WHEN waiting = 'true' THEN
         'waiting get lock,sql waiting execute'
      END waitting_satus,  --当前会话状态：执行中|等待中
      least(query_start, xact_start) AS query_start,  --query请求开始时间
      substr(query, 1, 25) AS query_text   --当前SQL语句
 FROM pg_locks
    left join pg_database pd
    on (pg_locks.database=pd.oid)
 LEFT OUTER JOIN pg_class
   ON (pg_locks.relation = pg_class.oid)
 LEFT OUTER JOIN pg_namespace
   ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
  AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;

-- 正被锁定的关系
select 
lorlocktype 被锁的关系的类型,
lordatabase 对象标识符_共享对象为0,
lorrelname 关系的名字,
lorrelation 关系的对象标识符,
lortransaction 锁影响到的事务标识符,
lorpid 获取或等待锁的服务器进程号_准备型0,
lormode 锁模式,
lorgranted 锁是T否F,
lorcurrentquery 会话当前的查询
from gp_toolkit.gp_locks_on_relation;

select 
lorusename 当前会话用户,
lorrsqname 资源队列,
lorlocktype 锁对象类型,
lorobjid 锁定的事务对象标识符,
lortransaction 锁影响到的事务标识符,
lorpid 锁影响到的事务的服务器进程号,
lormode 锁模式的名字,
lorgranted 锁是True否alse,
lorwaiting 会话是否正在等待
 from gp_toolkit.gp_locks_on_resqueue;

-- GP 6.0不能使用了
SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
FROM pg_locks l, pg_class c, pg_stat_activity a
WHERE l.relation=c.oid
AND l.pid=a.procpid
ORDER BY c.relname;

SELECT
    max_conn 最大连接数,
    now_conn 当前连接数,
    max_conn - now_conn 剩余连接数 
FROM
    ( SELECT setting::int8  AS max_conn, ( SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings WHERE NAME = 'max_connections' ) T;

-- 杀会话
select 'select pg_terminate_backend('||a.pid||');' kill1,
'select pg_cancel_backend('||a.pid||');' kill2 
from pg_stat_activity   a
where pid<>pg_backend_pid()
and state  in ( 'idle');

SELECT * FROM pg_locks d where d.pid in (192019,191474) order by pid;

select * from gp_toolkit.gp_locks_on_relation ;

SELECT pg_locks.pid as pid, --进程ID

transactionid as transaction_id, --事务ID

pd.datname db_name,

nspname as schemaname, --schema名

relname as object_name, --对象名

locktype as lock_type, --锁类型

mode lock_mode, --锁模式

CASE

WHEN granted = 'f' THEN

'get_lock'

WHEN granted = 't' THEN

'wait_lock'

END lock_satus, --锁状态：持有锁|等待锁

CASE

WHEN waiting = 'false' THEN

'already get lock,sql executing'

WHEN waiting = 'true' THEN

'waiting get lock,sql waiting execute'

END waitting_satus, --当前会话状态：执行中|等待中

least(query_start, xact_start) AS query_start, --query请求开始时间

substr(query, 1, 25) AS query_text --当前SQL语句

FROM pg_locks

left join pg_database pd

on (pg_locks.database=pd.oid)

LEFT OUTER JOIN pg_class

ON (pg_locks.relation = pg_class.oid)

LEFT OUTER JOIN pg_namespace

ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity

WHERE NOT pg_locks.pid = pg_backend_pid()

AND pg_locks.pid = pg_stat_activity.pid

ORDER BY query_start;

-- 正被锁定的关系

select

lorlocktype 被锁的关系的类型,

lordatabase 对象标识符_共享对象为0,

lorrelname 关系的名字,

lorrelation 关系的对象标识符,

lortransaction 锁影响到的事务标识符,

lorpid 获取或等待锁的服务器进程号_准备型0,

lormode 锁模式,

lorgranted 锁是T否F,

lorcurrentquery 会话当前的查询

from gp_toolkit.gp_locks_on_relation;

select

lorusename 当前会话用户,

lorrsqname 资源队列,

lorlocktype 锁对象类型,

lorobjid 锁定的事务对象标识符,

lortransaction 锁影响到的事务标识符,

lorpid 锁影响到的事务的服务器进程号,

lormode 锁模式的名字,

lorgranted 锁是True否alse,

lorwaiting 会话是否正在等待

from gp_toolkit.gp_locks_on_resqueue;

-- GP 6.0不能使用了

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query

FROM pg_locks l, pg_class c, pg_stat_activity a

WHERE l.relation=c.oid

AND l.pid=a.procpid

ORDER BY c.relname;

SELECT

max_conn 最大连接数,

now_conn 当前连接数,

max_conn - now_conn 剩余连接数

FROM

( SELECT setting::int8 AS max_conn, ( SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings WHERE NAME = 'max_connections' ) T;

-- 杀会话

select 'select pg_terminate_backend('||a.pid||');' kill1,

'select pg_cancel_backend('||a.pid||');' kill2

from pg_stat_activity a

where pid<>pg_backend_pid()

and state in ( 'idle');

会话kill、停止会话、杀会话

当报Too many sessions会话数满或数据库无响应或事务被锁需要中断锁源头时，可以使用杀会话方式临时解决。优先建议使pg_cancel_backend()函数(长事务会话需要等待较长时间中断)，在无法等待较长时间等待时，可pg_terminate_backend()函数(该方式杀会话有极小概率导致系统元数据不一致)。在数据库响应等极端情况可，方可考虑使用 kill -9 pid进行强制会话进程中断，一般先使用 kill -15 pid终端会话进程。

区别：pg_cancel_backend 只是取消当前某一个进程的查询操作，但不能释放数据库连接。但pg_terminate_backend可以在pg的后台杀死这个进程，从而释放出宝贵的连接资源，类似于kill -9 pid操作。

正常取消会话

select pg_cancel_backend(pid);

1	select pg_cancel_backend(pid);

强制中断会话

select pg_terminate_backend(pid);

1	select pg_terminate_backend(pid);

其中：pid为pg_stat_activity视图中的pid查询结果，如：

gpdw=# select pg_cancel_backend(2243);
     pg_cancel_backend
    -------------------
     t
    (1 row)
gpdw=# select pg_terminate_backend(2243);
 pg_terminate_backend
----------------------
 t
(1 row)

gpdw=# select pg_cancel_backend(2243);

pg_cancel_backend

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

(1 row)

gpdw=# select pg_terminate_backend(2243);

pg_terminate_backend

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

(1 row)

查询空闲会话ID并使用杀会话方式进行会话资源释放。

select pid from pg_stat_activity where state='idle';

1	select pid from pg_stat_activity where state='idle';

查询某时间段之后的会话

select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);

1	select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);

查询30分钟内会话

select * from pg_stat_activity where backend_start>(now() - interval '30 min'

1	select * from pg_stat_activity where backend_start>(now() - interval '30 min'

操作系统层面查看idle会话进程 kill后台进程

 ps -ef |grep -i postgres |grep -i idle|grep -v grep | awk '{print $2}' |xargs kill -15

1	ps -ef \|grep -i postgres \|grep -i idle\|grep -v grep \| awk '{print $2}' \|xargs kill -15

登陆与退出Greenplum

#正常登陆

psql gpdb

psql -d gpdb -h gphostm -p 5432 -U gpadmin

#使用utility方式

PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port

#退出

在psql命令行执行q

#正常登陆

psql gpdb

psql -d gpdb -h gphostm -p 5432 -U gpadmin

#使用utility方式

PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port

#退出

在psql命令行执行q

参数查询

psql -c 'SHOW ALL;' -d gpdb

gpconfig --show max_connections

SELECT * FROM gp_toolkit.gp_param_settings();

psql -c 'SHOW ALL;' -d gpdb

gpconfig --show max_connections

SELECT * FROM gp_toolkit.gp_param_settings();

这个有用，可以管道给grep。

查询分步键

select
 c.oid,
 n.nspname as schemaname,
 c.relname as tablename,
 pg_get_table_distributedby(c.oid) distributedby,
 case c.relstorage
  when 'a' then ' append-optimized'
  when 'c' then 'column-oriented'
  when 'h' then 'heap'
  when 'v' then 'virtual'
  when 'x' then 'external table'
 end as "data storage mode"
from pg_class as c
inner join pg_namespace as n
 on c.relnamespace = n.oid
where n.nspname = 'poc'
 and c.relstorage = 'c'
order by n.nspname, c.relname ;

select

c.oid,

n.nspname as schemaname,

c.relname as tablename,

pg_get_table_distributedby(c.oid) distributedby,

case c.relstorage

when 'a' then ' append-optimized'

when 'c' then 'column-oriented'

when 'h' then 'heap'

when 'v' then 'virtual'

when 'x' then 'external table'

end as "data storage mode"

from pg_class as c

inner join pg_namespace as n

on c.relnamespace = n.oid

where n.nspname = 'poc'

and c.relstorage = 'c'

order by n.nspname, c.relname ;

在Greenplum中，建表时指定分布键的语法如下：

cssCopy codeCREATE TABLE table_name (
   column1 datatype [optional parameters] [distribution_method],
   column2 datatype [optional parameters] [distribution_method],
   ...,
   CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)
) DISTRIBUTED BY (column1, column2, ...);

cssCopy codeCREATE TABLE table_name (

column1 datatype [optional parameters] [distribution_method],

column2 datatype [optional parameters] [distribution_method],

...,

CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)

) DISTRIBUTED BY (column1, column2, ...);

其中，DISTRIBUTED BY子句用于指定分布键，可以指定一个或多个列。一般来说，指定的分布键字段组合的大小应该控制在2-4个左右，这样可以保证分布键的效果，即让数据尽可能均匀地分布到各个节点上，提高查询性能。如果组合的字段过多，可能会导致某些节点上的数据倾斜，进而影响查询性能。

需要注意的是，指定的分布键字段不一定需要是表的主键，但应该是常用于查询和筛选的字段。另外，为了进一步提高查询性能，还可以考虑在表上创建合适的索引。

请参考：https://www.xmmup.com/greenplumchuangjianheguanlibiao.html#can_shugp_create_table_random_default_distribution

创建数据库

createdb -h localhost -p 5432 dhdw

1	createdb -h localhost -p 5432 dhdw

创建GP文件系统


文件系统名

gpfsdw

子节点，视segment数创建目录

mkdir -p /gpfsdw/seg1
mkdir -p /gpfsdw/seg2
chown -R gpadmin:gpadmin /gpfsdw

主节点

mkdir -p /gpfsdw/master
chown -R gpadmin:gpadmin /gpfsdw
gpfilespace -o gpfilespace_config
gpfilespace -c gpfilespace_config

文件系统名

gpfsdw

子节点，视segment数创建目录

mkdir -p /gpfsdw/seg1

mkdir -p /gpfsdw/seg2

chown -R gpadmin:gpadmin /gpfsdw

主节点

mkdir -p /gpfsdw/master

chown -R gpadmin:gpadmin /gpfsdw

gpfilespace -o gpfilespace_config

gpfilespace -c gpfilespace_config

创建GP表空间


psql gpdb

create tablespace TBS_DW_DATA filespace gpfsdw;

SET default_tablespace = TBS_DW_DATA;

psql gpdb

create tablespace TBS_DW_DATA filespace gpfsdw;

SET default_tablespace = TBS_DW_DATA;

删除GP数据库


gpdeletesystem -d /gpmaster/gpseg-1 -f

1 2	gpdeletesystem -d /gpmaster/gpseg-1 -f

查看GP日志


SELECT * FROM gp_toolkit.__gp_log_master_ext d 
where d.logseverity not in ('LOG') 
and d.logtime >= now()-interval '7 day'
order by d.logtime desc 
limit 50;

SELECT * FROM gp_toolkit.__gp_log_segment_ext d
where d.logseverity not in ('LOG') 
and d.logtime >= now()-interval '7 day'
order by d.logtime desc 
limit 50;

SELECT * FROM gp_toolkit.__gp_log_master_ext d

where d.logseverity not in ('LOG')

and d.logtime >= now()-interval '7 day'

order by d.logtime desc

limit 50;

SELECT * FROM gp_toolkit.__gp_log_segment_ext d

where d.logseverity not in ('LOG')

and d.logtime >= now()-interval '7 day'

order by d.logtime desc

limit 50;

OBJECT的DDL操作统计


SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time

FROM pg_stat_operations

WHERE objname = '<name>';

SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time

FROM pg_stat_operations

WHERE objname = '<name>';

队列查询


SELECT * FROM pg_resqueue_status;

1 2	SELECT * FROM pg_resqueue_status;

gpfdist外部表

参考：https://www.xmmup.com/greenplumzhongdeshujujiazaigongjuzhigpfdisthegpload.html

注意：
1、数据库服务端必须能访问客户端，且telnet 客户端IP 8081是通的。


# 启动服务
mkdir -p /home/gpadmin/txt
gpfdist -d /home/gpadmin/txt -p 8081  -l /home/gpadmin/txt/gpfdist.log  &

# 创建外部表
drop EXTERNAL TABLE test;
CREATE EXTERNAL TABLE test (
id int,
name text)
LOCATION ('gpfdist://127.0.0.1:8081/1.txt')
FORMAT 'TEXT' (
    DELIMITER '|' NULL ''
FILL MISSING FIELDS)
SEGMENT REJECT LIMIT 1 percent;

select * from test;

# 创建普通表
create table test1 select * from test;

# 索引
CREATE INDEX idx_test ON test1 USING bitmap (id);

# 查询数据
select id , count(*) from test group by id order by count(*);

# 启动服务

mkdir -p /home/gpadmin/txt

gpfdist -d /home/gpadmin/txt -p 8081 -l /home/gpadmin/txt/gpfdist.log &

# 创建外部表

drop EXTERNAL TABLE test;

CREATE EXTERNAL TABLE test (

id int,

name text)

LOCATION ('gpfdist://127.0.0.1:8081/1.txt')

FORMAT 'TEXT' (

DELIMITER '|' NULL ''

FILL MISSING FIELDS)

SEGMENT REJECT LIMIT 1 percent;

select * from test;

# 创建普通表

create table test1 select * from test;

# 索引

CREATE INDEX idx_test ON test1 USING bitmap (id);

# 查询数据

select id , count(*) from test group by id order by count(*);


# 创建可写外部表
CREATE WRITABLE EXTERNAL TABLE unload_expenses
( LIKE expenses )
LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',
'gpfdist://etlhost-2:8081/expenses2.out')
FORMAT 'TEXT' (DELIMITER ',')
DISTRIBUTED BY (exp_id);

# 写权限
GRANT INSERT ON writable_ext_table TO <name>;

# 写数据
INSERT INTO writable_ext_table SELECT * FROM regular_table;

# 创建可写外部表

CREATE WRITABLE EXTERNAL TABLE unload_expenses

( LIKE expenses )

LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',

'gpfdist://etlhost-2:8081/expenses2.out')

FORMAT 'TEXT' (DELIMITER ',')

DISTRIBUTED BY (exp_id);

# 写权限

GRANT INSERT ON writable_ext_table TO <name>;

# 写数据

INSERT INTO writable_ext_table SELECT * FROM regular_table;

gpload导入数据


# 创建控制文件

# 加载数据

gpload -f my_load.yml

# 创建控制文件

# 加载数据

gpload -f my_load.yml

copy导出导入数据


COPY country FROM '/data/gpdb/country_data'
WITH DELIMITER '|' LOG ERRORS INTO err_country
SEGMENT REJECT LIMIT 10 ROWS;

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out';

COPY country FROM '/data/gpdb/country_data'

WITH DELIMITER '|' LOG ERRORS INTO err_country

SEGMENT REJECT LIMIT 10 ROWS;

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out';

执行sql文件

psql gpdbname –f yoursqlfile.sql

1	psql gpdbname –f yoursqlfile.sql

或者psql登陆后执行

\i yoursqlfile.sql

1	\i yoursqlfile.sql

查询gp最后vacuum时间

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables 
ORDER BY last_vacuum;

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze

FROM pg_stat_user_tables

ORDER BY last_vacuum;

磁盘空间不足

1.vacuum
-- 生成 vacuum ANALYZE命令（或者使用 VACUUM FULL）
select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r';

-- 脚本执行命令
-- VCOMMAND="VACUUM ANALYZE"
-- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b   where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a  $DBNAME

 2. 清理日志
-- pg_log目录下并使用约定命名方式：gpdb-YYYY-MM-DD.log

1.vacuum

-- 生成 vacuum ANALYZE命令（或者使用 VACUUM FULL）

select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r';

-- 脚本执行命令

-- VCOMMAND="VACUUM ANALYZE"

-- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a $DBNAME

2. 清理日志

-- pg_log目录下并使用约定命名方式：gpdb-YYYY-MM-DD.log

将查询结果导出到文件

nohup psql -c "show all;" >./test.log &
-- ps -fe | grep test.log

1 2	nohup psql -c "show all;" >./test.log & -- ps -fe \| grep test.log

添加和删除standby master

添加和删除standby master的命令：

-- 添加standby master
gpinitstandby -s mdw2 -S /opt/greenplum/data/master/gpseg-1 -P 5432

-- 删除standby master
gpinitstandby -r

-- 查询standby master
gpstate -f

-- 添加standby master

gpinitstandby -s mdw2 -S /opt/greenplum/data/master/gpseg-1 -P 5432

-- 删除standby master

gpinitstandby -r

-- 查询standby master

gpstate -f

添加segment镜像mirror

-- 添加segment镜像mirror
gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg1/mirror"
gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg2/mirror"
gpaddmirrors -o mirror_config
cat mirror_config
gpaddmirrors -i mirror_config
gpstate -m

-- 添加segment镜像mirror

gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg1/mirror"

gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg2/mirror"

gpaddmirrors -o mirror_config

cat mirror_config

gpaddmirrors -i mirror_config

gpstate -m

需要注意一点的是：greenplum目前只提供添加mirror命令，而没有提供删除mirror命令，不过可以通过其它方式删除，在生产库不建议操作（我做了一次把库搞挂了）

Master节点故障恢复

1、若master故障，则直接在standby master上执行如下命令将standby master激活为主master：

gpactivatestandby -d /opt/greenplum/data/master/gpseg-1

1	gpactivatestandby -d /opt/greenplum/data/master/gpseg-1

若有VIP也需要切换VIP。

然后原来的master节点可以删除数据后，然后作为新的standby加入：

gpinitstandby -a -s mdw1

1	gpinitstandby -a -s mdw1

2、若standby master故障，则分情况：若是宕机，其实可以正常启动，使用如下命令正常启动：

nohup /usr/local/greenplum-db-6.23.0/bin/postgres -D /opt/greenplum/data/master/gpseg-1 -p 5432 -E &

1	nohup /usr/local/greenplum-db-6.23.0/bin/postgres -D /opt/greenplum/data/master/gpseg-1 -p 5432 -E &

若数据库文件损坏不能正常启动，则需要将standby节点数据删除，然后重新初始化一下standby服务器即可，需要先删除再添加：

gpinitstandby -r
gpinitstandby -s mdw1 -S /opt/greenplum/data/master/gpseg-1   -P 5432

1 2	gpinitstandby -r gpinitstandby -s mdw1 -S /opt/greenplum/data/master/gpseg-1 -P 5432

3、master和standby master就是PG中的主从复制，要想实现自动故障转移，则需要借助keepalived或repmgr或patroni或pgpool实现高可用自动切换。

Segment节点故障恢复

当一个primary segment节点故障，那么它所对应的mirror segment节点会自动接替primary的状态，继续保证整个集群的数据完整性
当一个mirror segment节点出现故障，它不会影响整个集群的可用性，但是需要尽快修复，保证所有的primary segment都有备份
如果primary segment 和它所对应的mirror segment 节点都出现故障，那么greenplum认为集群数据不完整，整个集群将不再提供服务，直到primary segment 或 mirror segment恢复

primary segment节点和mirror segment节点的故障修复方式是一样的，都执行如下命令即可：gprecoverseg -a

恢复方式都一样，若没有业务连接，则可以直接进行恢复：

-- 恢复segment
gprecoverseg -a

-- 重平衡回到最初状态
gprecoverseg -r

-- 恢复segment

gprecoverseg -a

-- 重平衡回到最初状态

gprecoverseg -r

若有业务连接，则需要关闭和重启集群：

-- 关闭集群
gpstop -a -M fast

-- 以restricted方式启动数据库
gpstart -a -R

-- 修复集群(默认增量，若不能恢复，可以考虑加-F参数进行全量恢复，恢复时间和数据库大小成正比)
-- 特别注意：在GP低版本中，这个命令很快就完成了，但是其实恢复还在后台进行，可以使用gpstate -e查看进度
gprecoverseg -a

-- 检测状态（一直等到Data Status 这个属性全部都是Synchronized即可进行下一步操作）
gpstate -m

-- 查看恢复的进度
gpstate -e

-- 重平衡回到最初状态（前提是执行gpstate -m后Data Status 这个属性全部都是Synchronized）
-- 执行时间和数据库大小有少量关系
gprecoverseg -r

-- 重启集群
gpstop -a -M fast -r

-- 关闭集群

gpstop -a -M fast

-- 以restricted方式启动数据库

gpstart -a -R

-- 修复集群(默认增量，若不能恢复，可以考虑加-F参数进行全量恢复，恢复时间和数据库大小成正比)

-- 特别注意：在GP低版本中，这个命令很快就完成了，但是其实恢复还在后台进行，可以使用gpstate -e查看进度

gprecoverseg -a

-- 检测状态（一直等到Data Status 这个属性全部都是Synchronized即可进行下一步操作）

gpstate -m

-- 查看恢复的进度

gpstate -e

-- 重平衡回到最初状态（前提是执行gpstate -m后Data Status 这个属性全部都是Synchronized）

-- 执行时间和数据库大小有少量关系

gprecoverseg -r

-- 重启集群

gpstop -a -M fast -r

在执行gprecoverseg恢复segment后，原有的primary和mirror会发生变动，可能导致所有的primary都在同一台几点，会导致性能问题，所以应该执行命令进行重平衡gprecoverseg -r就可以让集群回到最初的配置角色了。

扩容GreenPlum系统

1、主要是4步扩容：

su - gpadmin

-- 1、使用gpexpand创建初始化文件，输入Y，然后个回车即可
cd /home/gpadmin/conf
gpexpand -f /home/gpadmin/conf/seg_hosts

-- 2、利用生成的初始化文件，初始化Segment并且创建扩容schema
-- 该步骤若报错，可以修复错误后，再重复运行如下命令
gpexpand -i gpexpand_inputfile_20230313_131204

-- 3、重新分布数据，最长时间1小时
gpexpand -d 1:00:00

-- 4、移除扩容schema
gpexpand -c

su - gpadmin

-- 1、使用gpexpand创建初始化文件，输入Y，然后个回车即可

cd /home/gpadmin/conf

gpexpand -f /home/gpadmin/conf/seg_hosts

-- 2、利用生成的初始化文件，初始化Segment并且创建扩容schema

-- 该步骤若报错，可以修复错误后，再重复运行如下命令

gpexpand -i gpexpand_inputfile_20230313_131204

-- 3、重新分布数据，最长时间1小时

gpexpand -d 1:00:00

-- 4、移除扩容schema

gpexpand -c

2、监控视图，在postgres数据库中：

select * from  gpexpand.status;
select * from  gpexpand.status_detail;
select * from  gpexpand.expansion_progress;

gpstate -x

select * from gpexpand.status;

select * from gpexpand.status_detail;

select * from gpexpand.expansion_progress;

gpstate -x

例行清理和分析

Greenplum数据库中使用的MVCC事务并发模型的设计意味着被删除或者被更新的数据行仍在磁盘上占据物理空间，即便它们已经对新事务不可见。如果数据库进行了很多更新和删除，会有很多过期行存在并且它们所使用的空间必须使用 VACUUM命令来回收。VACUUM命令还会收集表级的统计信息，例如行数和页面数，因此即便无需从被更新或者被删除行回收空间，也还是有必要去清理追加优化表。

清理一个追加优化表遵循一种和清理堆表不同的处理逻辑。在每一个Segment上，会创建一个新的Segment文件并且把可见行从当前Segment复制到该文件中。当Segment文件被拷贝完时，将会安排删除原始的Segment文件并且让新的Segment文件变得可用。这要求足够的可用磁盘空间用于拷贝可见行，直到原始的Segment文件被删除为止。

如果一个Segment文件中隐藏行和所有行的比率低于一个阈值（默认是10），该Segment文件不会被紧缩。该阈值可以通过gp_appendonly_compaction_threshold服务器配置参数配置。VACUUM FULL 忽略gp_appendonly_compaction_threshold的值并且不管该比率为多少都会重写Segment文件。

可以使用gp_toolkit模式中的__gp_aovisimap_compaction_info()函数来查看追加优化表上的VACUUM操作的效果。

有关__gp_aovisimap_compaction_info()函数的信息请见Greenplum Database Reference Guide “检查追加优化表”部分

可以使用gp_appendonly_compaction服务器配置参数为追加优化表禁用 VACUUM操作。

有关清理数据库的细节请见清理数据库。

有关gp_appendonly_compaction_threshold服务器配置参数和VACUUM 命令的信息请见Greenplum Database Reference Guide。

事务ID管理

Greenplum的MVCC事务机制依赖于比较事务ID（XID）号来判断当前数据对于其他事务的可见性。事务ID号使用一种模232的算法来比较，因此一个运行了超过二十亿事务的 Greenplum系统可能会遇到事务ID回卷，即过去的事务变成了未来的事务。这意味着过去的事务的输出变得不可见。因此，每过二十亿个事务就有必要VACUUM每个数据库中的每个表至少一次。

Greenplum数据库只对涉及DDL或者DML操作的事务分配XID值，通常也只有这些事务需要XID。

Important: Greenplum数据库会监控事务ID。如果没有定期清理数据库，Greenplum 数据库将产生警告和错误。

当事务ID中相当多的一部分变得不再可用并且事务ID回卷还没有发生时，Greenplum数据库会发出下面的警告：

WARNING: database “database_name” must be vacuumed within number_of_transactions transactions

当这个警告被发出时，需要一次VACUUM操作。如果没有执行VACUUM 操作，当Greenplum数据库在事务ID回卷发生前达到一个限制，它会停止创建新的事务。在停止创建事务以避免可能的数据丢失时，Greenplum数据库会发出这个错误：

FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

1	FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

Greenplum数据库配置参数xid_warn_limit控制何时显示该警告。参数 xid_stop_limit控制何时Greenplum数据库停止创建事务。

从一次事务ID限制错误中恢复

当Greenplum数据库由于不频繁的VACUUM维护而达到 xid_stop_limit事务ID限制时，它会变得没有响应。为了从这种情况中恢复过来，作为数据库管理员执行下面的步骤：

关闭Greenplum数据库。
临时将xid_stop_limit降低10,000,000。
启动Greenplum数据库。
在所有受影响的数据库上运行VACUUM FREEZE。
将xid_stop_limit重置为原来的值。
重启Greenplum数据库。

有关这些配置参数的信息请见Greenplum Database Reference Guide。

有关事务ID回卷的信息请见PostgreSQL documentation.

系统目录维护

多次使用CREATE和DROP命令的数据库更新会增长系统目录尺寸并且影响系统性能。例如，运行很多次DROP TABLE语句会降低总体系统性能，因为在目录表上的元数据查询期间会需要更多扫描时间。性能损失会在数千次或者数万次DROP TABLE语句之间发生，具体时间取决于系统。

应该定期维护系统目录来回收已删除对象所占据的空间。如果长时间没有运行这种定期回收操作，那可能需要运行一个更彻底的回收操作来清理系统目录。这个主题会描述这两种方式。

常规系统目录维护

推荐周期性地在系统目录上运行REINDEX和VACUUM来清理系统索引和表中已删除对象所占用的空间。如果常规的数据库操作包括很多DROP语句，那么每天在非峰值时间用VACUUM命令运行一次系统目录维护是安全且适当的。用户可以在系统可用时执行这种操作。

下面是Greenplum数据库系统目录维护步骤。

在系统表上执行REINDEX命令以重建系统表索引。该操作移除索引膨胀并提高VACUUM 性能。
Note: 当在系统表上执行REINDEX操作时，表上会产生锁，此时可能会对当前正在运行的查询产生比较大的影响。建议在系统负载较低时执行REINDEX操作，以避免对正在运行的业务操作产生较大的干扰。
在系统表上执行VACUUM操作。
在系统表上执行ANALYZE操作，用以更新统计信息。

下面的示例脚本在一个Greenplum数据库系统目录上执行一次REINDEX、VACUUM 以及ANALYZE操作：将脚本中的替换为真实数据库名。

#!/bin/bash
DBNAME="<database-name>"
SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b 
WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'"
reindexdb --system -d $DBNAME
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
analyzedb -s pg_catalog -d $DBNAME

#!/bin/bash

DBNAME="<database-name>"

SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b

WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'"

reindexdb --system -d $DBNAME

psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME

analyzedb -s pg_catalog -d $DBNAME

Note: 如果在系统维护时间内已经开始了正常的系统目录维护操作，但是由于时间原因想要停止某一维护进程，此时可以运行Greenplum数据库函数pg_cancel_backend() 以安全停止该Greenplum数据库进程。

深度系统目录维护

如果很长时间都没有执行一次系统目录维护操作，该目录可能因为废弃空间而膨胀。这会导致简单的元数据操作都会等待很长时间。在psql中用\d命令列出用户表需要超过两秒的等待，就是目录膨胀的一种征兆。

如果发现系统目录膨胀的征兆，就必须在计划好的停机时段用VACUUM FULL执行一次深度系统目录维护操作。在这一时段中，停止系统上的所有目录活动，这种VACUUM FULL 系统目录维护过程会对系统目录加排他锁。

运行定期系统目录维护操作可以防止对这种更高开销操作的需求。

以下是深度系统目录维护操作的步骤。

停止Greenplum数据库系统上的所有活动元数据操作。
在系统表上执行REINDEX操作以重建系统表索引。该操作移除索引上的膨胀并提高 VACUUM操作性能。
在系统表上执行VACUUM FULL命令，具体查看下面的注释。
在系统表上执行ANALYZE命令以更新系统表的统计信息。

Note: 通常来说，表pg_attribute是最大的系统表。如果pg_attribute 表膨胀的特别厉害，该表上的VACUUM FULL操作可能需要更长的时间并且可能需要分开执行。以下两种情况的出现预示着pg_attribute表膨胀很厉害并且需要长时间的VACUUM FULL 操作：

pg_attribute表包含大量记录。
gp_toolkit.gp_bloat_diag视图中的信息显示pg_attribute的状态为significant amount of bloat

为查询优化进行清理和分析

Greenplum数据库使用一种基于代价的查询优化器，它依赖于数据库的统计信息。准确的统计信息帮助查询优化器更好的评估选择度以及一个查询操作检索的行数。这些评估会帮助它选择最有效的查询计划。ANALYZE 命令会为查询优化器收集列级的统计信息。

可以在同一个命令中同时执行VACUUM和ANALYZE操作。例如：

=# VACUUM ANALYZE mytable;

1	=# VACUUM ANALYZE mytable;

当在一个显著膨胀的表（显著膨胀的表磁盘空间被已删除或者已废弃行占据）上运行VACUUM ANALYZE 命令时，该命令可能会产生不正确的统计信息。对于大型的表，ANALYZE命令会从行的一个随机采样中计算统计信息。它会通过计算采样中每页的平均行数与表中实际页数的成绩来估算表中的总行数。如果采样包含很多空页，估计出的行计数可能会不准确。

对于一个表，可以在gp_toolkit视图gp_bloat_diag中查看有关未使用磁盘空间（被已删除或者已废弃行占据的空间）量的信息。如果一个表的bdidiag列包含值 significant amount of bloat suspected，那么相当多的表磁盘空间由未使用空间组成。在一个表被清理后，相关项会被加入到gp_bloat_diag视图中。

要从表中移除未使用的磁盘空间，可以在该表上运行命令VACUUM FULL。由于对表锁的需求， VACUUM FULL可能无法在非维护时段运行。

作为一种临时的变通方案，可以运行ANALYZE来计算列统计信息，然后在该表上运行 VACUUM来生成准确的行计数。这个例子在cust_info表上先运行ANALYZE，然后运行VACUUM。

ANALYZE cust_info;
VACUUM cust_info;

1 2	ANALYZE cust_info; VACUUM cust_info;

Important: 如果想要在启用了GPORCA（默认启用）的情况下对分区表执行查询，必须用ANALYZE命令在分区表根分区上收集统计信息。有关GPORCA的信息，请见GPORCA概述。

Note: 可以使用Greenplum数据库工具analyzedb来更新表统计信息。表可以被并行地分析。对于追加优化表，只有统计信息不是当前值时，analyzedb才会更新统计信息。有关analyzedb 工具的信息，请见analyzedb。

常规的索引重建

对于B-树索引，一个刚刚构建的索引访问起来比一个已经更新过很多次的索引要快一点，因为在新构建的索引中逻辑上相邻的页面在物理上也相邻。定期地重建旧的索引可以提升访问速度。如果一个页面上除了索引以外的数据都被删除，那么索引页上对应的也会产生浪费的空间。重建索引操作会回收这些被浪费的空间。在Greenplum数据库中，删除索引 (DROP INDEX)再重建 (CREATE INDEX)通常比直接使用REINDEX 命令要快。

对于有索引的表列，批量更新或者插入之类的一些操作可能会执行得更慢，因为需要更新索引。为了提高带有索引的表上的批量操作性能，可以先删除掉索引，然后执行批量操作，最后再重建索引。

管理Greenplum数据库日志文件

数据库服务器日志文件

Greenplum数据库的日志输出常常会体量很大，尤其是在调试级别时，用户不需要无限期保存它。管理员可以应该定期清理日志文件。

Greenplum数据库在Master和所有的Segment实例上都启用了日志文件轮转。日志文件被创建在Master以及每个Segment的数据目录中的pg_log子目录下，这些文件使用命名规则：gpdb-YYYY-MM-DD_hhmmss.csv。数据库管理员需要编写脚本或程序定期清理Master以及每个Segment日志目录pg_log下的旧文件。

日志轮转可以被当前日志文件的大小或当前日志文件的年龄触发。log_rotation_size 配置参数设置触发日志轮转的单个日志文件大小。当日志文件大小等于或大于特定的大小时，该文件会停止写入，系统重新创建一个新的日志文件。log_rotation_size的值的单位为KB。默认为1048576，即1GB。如果log_rotation_size设置为0，表示基于文件大小的日志轮转被禁用。

log_rotation_age配置参数定义了出发日志轮转的日志文件年龄。当文件自创建时开始到达特定长度的时间时，该文件停止写入，系统重新创建一个新的日志文件。默认的log_rotation_age 时间为1d，即当前日志文件创建24小时之后。如果log_rotation_age设置为0，表示基于时间的轮转被禁用。

更多查看数据库日志文件的信息，请见查看数据库服务器日志文件.

管理工具日志文件

reenplum数据库管理工具的日志文件默认被写入到~/gpAdminLogs。管理日志文件的命名规则是：

script_name_date.log

1	script_name_date.log

日志详情的格式是：

timestamp:utility:host:user:[INFO|WARN|FATAL]:message

1	timestamp:utility:host:user:[INFO\|WARN\|FATAL]:message

每次一个工具运行时，就会向其每日的日志文件中增加与其执行相关的日志信息。

收集统计信息，回收空间

定期使用Vacuum analyze tablename 回收垃圾和收集统计信息，尤其在大数据量删除，导入以后，非常重要

vacuum分两种，一种是analyze，优化查询计划的，还有一种是清理垃圾数据。

postgres删除工作，并不是真正删除数据，而是在被删除的数据上，做一个标记，只有执行vacuum时，才会真正的物理删除，这个非常重用，有些经常更新的表，各种查询、更新效率会越来越慢，这个多是因为没有做vacuum的原因。

以下脚本用于生成收集统计信息的shell脚本：

-- 日志位置： /opt/greenplum/data/master/gpseg-1/db_analyze/
SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb
from pg_database d
 WHERE d.datname not in ('template0','template1');

-- 做vacuum及更新统计信息
vacuumdb -a -z

-- 日志位置： /opt/greenplum/data/master/gpseg-1/db_analyze/

SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb

from pg_database d

WHERE d.datname not in ('template0','template1');

-- 做vacuum及更新统计信息

vacuumdb -a -z

缺失统计信息的表可以通过如下SQL查询

 -- 没有统计信息且可能需要ANALYZE的表。 qian'ti
select a.*,
    'analyze ' || a.smischema || '.' || a.smitable||';' anaylze_tb
from
    gp_toolkit.gp_stats_missing a 
order by
    smischema,
    smicols desc;

SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
        'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb
FROM pg_stat_all_tables a
WHERE a.last_analyze is null
and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024
and a.schemaname not in ('pg_toast')
ORDER BY  pg_relation_size(schemaname||'.'||relname) desc
LIMIT 50;

-- 没有统计信息且可能需要ANALYZE的表。 qian'ti

select a.*,

'analyze ' || a.smischema || '.' || a.smitable||';' anaylze_tb

from

gp_toolkit.gp_stats_missing a

order by

smischema,

smicols desc;

SELECT

schemaname||'.'||relname as table_name,

pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,

n_dead_tup,

n_live_tup,

'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb

FROM pg_stat_all_tables a

WHERE a.last_analyze is null

and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024

and a.schemaname not in ('pg_toast')

ORDER BY pg_relation_size(schemaname||'.'||relname) desc

LIMIT 50;

对表做DDL操作会导致统计信息失效，而gp_toolkit将统计信息失效的表也归纳为统计信息缺失。

ANALYZE会给目标表加SHARE UPDATE EXCLUSIVE锁，也就是与UPDATE，DELETE，还有DDL语句冲突。

定时收集统计信息

vacuumdb

缺点：不能并行，执行较慢

优点：占用资源少

su - gpadmin

crontab -e

0 2 * * *  . /usr/local/greenplum-db/greenplum_path.sh && vacuumdb -a -Z > /tmp/vacuumdb.log

su - gpadmin

crontab -e

0 2 * * * . /usr/local/greenplum-db/greenplum_path.sh && vacuumdb -a -Z > /tmp/vacuumdb.log

analyzedb

优点：可以并行，执行较快

缺点：占用资源多

-- 日志位置： /opt/greenplum/data/master/gpseg-1/db_analyze/
SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb
from pg_database d
 WHERE d.datname not in ('template0','template1');

su - gpadmin
crontab -e
0 2 * * *  sh /home/gpadmin/analyzegp.sh > /tmp/analyzegp.log

cat >> /home/gpadmin/analyzegp.sh <<"EOF"

. /usr/local/greenplum-db/greenplum_path.sh
analyzedb -d db1 -a -p 10
analyzedb -d db2 -a -p 10
...
analyzedb -d dbn -a -p 10
EOF

chmod +x /home/gpadmin/analyzegp.sh

-- 日志位置： /opt/greenplum/data/master/gpseg-1/db_analyze/

SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb

from pg_database d

WHERE d.datname not in ('template0','template1');

su - gpadmin

crontab -e

0 2 * * * sh /home/gpadmin/analyzegp.sh > /tmp/analyzegp.log

cat >> /home/gpadmin/analyzegp.sh <<"EOF"

. /usr/local/greenplum-db/greenplum_path.sh

analyzedb -d db1 -a -p 10

analyzedb -d db2 -a -p 10

...

analyzedb -d dbn -a -p 10

EOF

chmod +x /home/gpadmin/analyzegp.sh

分析执行计划

explain：在提交大的查询之前，使用explain分析执行计划、发现潜在优化机会，避免将系统资源熬尽。

少写了个analyze，如果只是explain，统计出来的执行时间，是非常坑爹的，如果希望获得准确的执行时间，必须加上analyze。

集群修复

查看节点状态


select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态

select * from gp_segment_configuration where status='d' or  mode <>'s'--观察数据库是否发生切换

select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态

select * from gp_segment_configuration where status='d' or mode <>'s'--观察数据库是否发生切换

修复前负载判定(是否是实例宕机，是否需要kill会话,是否需要重启等)

在查看集群状态为异常后，进一步查看是否存在超过一个小时以上的会话，是否存在锁等情况

select * from gp_segment_configuration;
-- 以上语句查询结果中status存在'd'结果，则是存在实例宕机，可以往下继续判断
-- 如果均为'u'，可以参考后面的2.6小节
select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start;

select * from gp_segment_configuration;

-- 以上语句查询结果中status存在'd'结果，则是存在实例宕机，可以往下继续判断

-- 如果均为'u'，可以参考后面的2.6小节

select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start;

查看节点负载，可以使用1.2中的vmstat命令，也可以使用nmon监控工具（需安装）

c 查看CPU相关信息
m 查看内存相关信息

d 查看磁盘相关信息

n 查看网络相关信息

t 查看相关进程信息

h 查看帮助相关信息

日志备份([可选]判定实例>判定路径>备份日志)

    Select con.time,
    con.dbid,
    seg.content,
    seg.status,
    seg.port,
    seg.hostname,
    seg.datadir ,
    con."desc"
    from gp_segment_configuration seg ,gp_configuration_history con
    where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10;

Select con.time,

con.dbid,

seg.content,

seg.status,

seg.port,

seg.hostname,

seg.datadir ,

con."desc"

from gp_segment_configuration seg ,gp_configuration_history con

where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10;

集群实例宕机后首要是先恢复集群状态，但这恢复的时候数据节点日志往往会被清理掉，所以可以先备份当天节点日志后，先恢复集群，后面再查看日志寻找宕机的具体原因，根据seg.hostname和seg.datadir，以及 con.time备份对应实例pg_log目录下对应日期的日志

常规修复(以防突然断电或远程断开等情况，一般建议后台运行)

通过gpstate 或gp_configuration 发现有实例down 掉以后，使用该命令进行恢复。

nohup gprecoverseg -a &

1	nohup gprecoverseg -a &

若存在主备切换，则需要在修复完成后进行实例切回

 nohup gprecoverseg -ra &

1	nohup gprecoverseg -ra &

查看修复进度

gpstate -e

1	gpstate -e

全量修复方式

nohup gprecoverseg -Fa &

1	nohup gprecoverseg -Fa &

若存在主备切换，则需要在修复完成后进行实例切回

nohup gprecoverseg -ra &

1	nohup gprecoverseg -ra &

非实例宕机的

如果集群状态显示异常，但在2.1中查询结果并不存在实例宕机的情况，可以先查看是否是集群用户存在密码过期导致无法互信的情况

查看互信情况，发现其中一台服务器无法连接，ssh时并提示密码过期

这样就需要重新设置改服务器gpadmin用户的密码或者过期时间即可

用户管理

创建role/schema

创建用户可以使用CREATE USER 或者CREATE ROLE命令，唯一区别是CREATE USER默认情况下假定LOGIN，而CREATE ROLE默认情况下假定NOLOGIN.
如创建test用户可以登陆资源队列为 pg_default，密码为passwd：

 create role test with login  resource queue pg_default password 'passwd';

1	create role test with login resource queue pg_default password 'passwd';

创建模式

 Create schema test;

1	Create schema test;

如果是为角色创建一个同名模式：如

Create schema  authorization test;

1	Create schema authorization test;

创建资源队列

create resource queue rq_df with(active_statements=1,priority=medium);

1	create resource queue rq_df with(active_statements=1,priority=medium);

修改用户资源队列

alter role test with resource queue rq_df;

1	alter role test with resource queue rq_df;

查看用户资源队列对应情况

select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

1	select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

授权与回收权限

根据需要授权用户使用相应权限

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
    TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }
        ON { [TABLE] table_name [, ...]
             | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]
    GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }
        ON { SEQUENCE sequence_name [, ...]
             | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |

TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }

ON { [TABLE] table_name [, ...]

| ALL TABLES IN SCHEMA schema_name [, ...] }

TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]

GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }

ON { SEQUENCE sequence_name [, ...]

| ALL SEQUENCES IN SCHEMA schema_name [, ...] }

TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

例如：
授权用户使用模式权限(用户需要先有schema的使用权限，才能使用其他权限)

 GRANT USAGE ON SCHEMA schema to user;

1	GRANT USAGE ON SCHEMA schema to user;

授权用户查询表权限

grant select on tablename to user;

1	grant select on tablename to user;

postgresql 9.0 版本以后，如果是一个用户对另一个用户新建的表需要有查询等权限，可以使用一些语句，不用再对新建表去重新授权

alter default privileges for user test in schema test grant select on tables to test1;
--举例中，test用户在test模式下再建新表时，test1用户自动对test模式下新建的表有select权限

1 2	alter default privileges for user test in schema test grant select on tables to test1; --举例中，test用户在test模式下再建新表时，test1用户自动对test模式下新建的表有select权限

回收权限则是revoke命令，后面to 改为from即可，如回收用户使用模式权限

REVOKE  USAGE ON SCHEMA schema from user;

1	REVOKE USAGE ON SCHEMA schema from user;

在上例中，回收上面的权限的语句为：

alter default privileges for user test in schema test revoke select on tables from test1;

1	alter default privileges for user test in schema test revoke select on tables from test1;

权限查看

查看哪些用户对具体表有哪些权限

select * from information_schema.table_privileges where table_name ='test';

1	select * from information_schema.table_privileges where table_name ='test';

查看具体用户有哪些表的哪些权限

select * from information_schema.table_privileges where grantee='test' or grantor='test';

1	select * from information_schema.table_privileges where grantee='test' or grantor='test';

锁定role

—锁定用户

alter role test with nologin;

1	alter role test with nologin;

删除schema

—删除schema

DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT];

1	DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE \| RESTRICT];

删除role

—删除用户

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

DROP ROLE [IF EXISTS] test;

1	DROP ROLE [IF EXISTS] test;

访问策略管理

策略文件说明

Greenplum数据库连接访问及验证机制由pg_hba.conf配置文件控制，在Greenplum数据库系统中，master主机上的pg_hba.conf文件控制客户端的访问及验证，而segment节点上的pg_hba.conf文件已经默认只与master主机连接，不接受其他外部客户端的连接。pg_hba.conf目录为$MASTER_DATA_DIRECTORY。更新该策略文件后，使用gpstop -u生效。

—pg_hba.conf格式说明

该文件有如下5列，列间为空格，不区分大小写。

 登陆客户端类型 允许访问数据库 数据库角色名 允许连接客户端IP(段) 验证方式
    登陆客户端类型：host|local    --远程登录|本地登录
    允许访问数据库：dbname|all  --具体数据库名|所有数据库
    数据库角色名：rolename|all  --具体用户名|所有用户
    允许连接客户端IP(段)        --设置IP地址或IP地址段,格式如下：
    192.168.32.100/32        --指定IP地址192.168.32.100
    192.168.32.0/24            --指定192.168.32.0网段
    192.168.0.0/16            --指定192.168.0.0网段
    192.0.0.0/8                --指定192.0.0.0网段
    验证方式：ident|md5|password|trust|reject  --说明如下：
    ident：本地验证，即使用操作系统gpadmin用户验证。
    md5：密码验证
    password：发送明文密码至数据库验证(建议不用)
    trust:免密登录
    reject:拒绝登录，即黑名单。建议黑名单放在pg_hba.conf文件最末

登陆客户端类型允许访问数据库数据库角色名允许连接客户端IP(段) 验证方式

登陆客户端类型：host|local --远程登录|本地登录

允许访问数据库：dbname|all --具体数据库名|所有数据库

数据库角色名：rolename|all --具体用户名|所有用户

允许连接客户端IP(段) --设置IP地址或IP地址段,格式如下：

192.168.32.100/32 --指定IP地址192.168.32.100

192.168.32.0/24 --指定192.168.32.0网段

192.168.0.0/16 --指定192.168.0.0网段

192.0.0.0/8 --指定192.0.0.0网段

验证方式：ident|md5|password|trust|reject --说明如下：

ident：本地验证，即使用操作系统gpadmin用户验证。

md5：密码验证

password：发送明文密码至数据库验证(建议不用)

trust:免密登录

reject:拒绝登录，即黑名单。建议黑名单放在pg_hba.conf文件最末

策略配置及生效

gpstop -u

gpstop -u

策略登录验证

psql -d gp  -h ip -U test

1	psql -d gp -h ip -U test

正常连接到数据库则策略添加成功

系统表的维护

Greenplum与其他所有关系型数据库一样，拥有一套管理数据库内部对象及关联关系的元数据表，我们称之为Greenplum系统表。Greenplum的产品内核是基于postgresql数据库基础上开发完成的，因此，Greenplum系统表很多继承于postgresql数据库。

Greenplum的系统表大致可分为这几类：

（1）数据库部对象的元数据，如：pg_database、pg_namespace、pg_class、pg_attribute、pg_type、pg_exttable等。

这类系统表既涵盖了全局的对象定义，也涵盖了每个数据库内的各种对象定义。这类系统表的元数据不是分布式的存储，而是每一个数据库实例（不论是master实例还是segment实例）中都各有一份完整的元数据。但也有例外，如：gp_distribution_policy（分布键定义）表则只在master上才有元数据。

对于这类系统表，各个实例之间元数据保持一致十分重要。

（2）维护Greenplum集群状态的元数据，如：gp_segment_configuration、gp_configuration_history、pg_stat_replication等。

这类系统表主要由master实例负责维护，就如segment实例状态管理的两张表gp_segment_configuration和gp_configuration_history的数据是由master的专用进程fts负责维护的。

（3）Persistent table，如：gp_persistent_database_node、gp_persistent_filespace_node、gp_persistent_relation_node、gp_persistent_tablespace_node。

这类系统表同样是存在于每一个数据库实例中。在每个实例内，persistenttable与pg_class/pg_relation_node/pg_database等系统表有着严格的主外键关系。这类系统表也是primary实例与mirror实例之间实现同步的重要参考数据。

在Greenplum集群出现故障时，会有可能导致系统表数据有问题。系统表出现问题会导致很多种故障产生，如：某些数据库对象不可用，实例恢复不成功，实例启动不成功等。针对系统表相关的问题，我们应该结合各个实例的日志信息，系统表的检查结果一起定位问题，本文将介绍一些定位、分析及解决问题的方法和技巧。

使用gpcc视图查询系统性能

以下查询都需要切换到gpperfmon数据库。

系统资源


-- 每隔15秒收集1次所有主机的系统信息

SELECT
   ctime::date || ' ' 
     || CASE WHEN extract(hour from ctime) < 10 THEN '0' ELSE '' END ||extract(hour from ctime)
     ||':'|| CASE WHEN extract(MINUTE from ctime) < 10 THEN '0' ELSE '' END || extract(MINUTE from ctime)
     AS time1
 , hostname
 , ROUND(AVG(cpu_sys::numeric),2) AS cpu_sys 
 , ROUND(avg(cpu_user::numeric),2) AS cpu_user
 , ROUND(avg((cpu_sys+cpu_user)::numeric),2) AS cpu_use
 , ROUND(avg((cpu_iowait)::numeric),2) AS cpu_iowait
 , ROUND(avg(gsh.cpu_idle::numeric),2) AS cpu_idle
 , ROUND(avg(mem_total::numeric/1024/1024/1024)) AS mem_total_GB
 , ROUND(avg(mem_used::numeric/1024/1024/1024),2) AS mem_used_GB
 -- , ROUND(avg(mem_actual_used::numeric/1024/1024/1024),2) AS mem_actual_GB
 , ROUND(avg((mem_buffers+mem_cached)::numeric/1024/1024/1024),2) AS mem_buffer_cache_GB
 -- , ROUND(avg(((gsh.mem_total-mem_used)::numeric)/1024/1024/1024),2) AS mem_available_GB
 , ROUND(avg(gsh.swap_total::numeric/1024/1024/1024)) as swap_total
 , ROUND(avg(gsh.swap_used::numeric/1024/1024/1024),2) as swap_used
 , ROUND(avg(gsh.swap_page_in::numeric),2) as swap_page_in
 , ROUND(avg(gsh.swap_page_out::numeric),2) as swap_page_out
 , ROUND(avg(load0::numeric),2) AS load0
 , ROUND(avg(load1::numeric),2) AS load1
 , ROUND(avg(load2::numeric),2) AS load2
 , ROUND((avg(disk_rb_rate::numeric) / 1024 / 1024),2) AS disk_R_MBs
 , ROUND((avg(disk_wb_rate::numeric) / 1024 / 1024),2) AS disk_W_MBs
 , ROUND((avg(net_rb_rate::numeric) / 1024 / 1024),2) AS net_I_MBs
 , ROUND((avg(net_wb_rate::numeric) / 1024 / 1024),2) AS net_O_MBs
FROM
   gpmetrics.gpcc_system_history gsh
WHERE hostname LIKE 'mdw%'
AND ctime >= '2023-09-14 16:30:00'
AND ctime < '2023-09-14 19:00:00'
GROUP BY 1,2
ORDER BY 1
;

-- 系统内存和swap信息
SELECT DISTINCT gsh.hostname,
ROUND(gsh.mem_total::numeric/1024/1024/1024) AS mem_total_G, 
ROUND(gsh.swap_total::numeric/1024/1024/1024) AS swap_total_G
FROM  gpmetrics.gpcc_system_history gsh
where ctime >=  NOW() - INTERVAL '1 minute'
and gsh.mem_total > 0
order by 1
limit 10;

-- 每隔15秒收集1次所有主机的系统信息

SELECT

ctime::date || ' '

|| CASE WHEN extract(hour from ctime) < 10 THEN '0' ELSE '' END ||extract(hour from ctime)

||':'|| CASE WHEN extract(MINUTE from ctime) < 10 THEN '0' ELSE '' END || extract(MINUTE from ctime)

AS time1

, hostname

, ROUND(AVG(cpu_sys::numeric),2) AS cpu_sys

, ROUND(avg(cpu_user::numeric),2) AS cpu_user

, ROUND(avg((cpu_sys+cpu_user)::numeric),2) AS cpu_use

, ROUND(avg((cpu_iowait)::numeric),2) AS cpu_iowait

, ROUND(avg(gsh.cpu_idle::numeric),2) AS cpu_idle

, ROUND(avg(mem_total::numeric/1024/1024/1024)) AS mem_total_GB

, ROUND(avg(mem_used::numeric/1024/1024/1024),2) AS mem_used_GB

-- , ROUND(avg(mem_actual_used::numeric/1024/1024/1024),2) AS mem_actual_GB

, ROUND(avg((mem_buffers+mem_cached)::numeric/1024/1024/1024),2) AS mem_buffer_cache_GB

-- , ROUND(avg(((gsh.mem_total-mem_used)::numeric)/1024/1024/1024),2) AS mem_available_GB

, ROUND(avg(gsh.swap_total::numeric/1024/1024/1024)) as swap_total

, ROUND(avg(gsh.swap_used::numeric/1024/1024/1024),2) as swap_used

, ROUND(avg(gsh.swap_page_in::numeric),2) as swap_page_in

, ROUND(avg(gsh.swap_page_out::numeric),2) as swap_page_out

, ROUND(avg(load0::numeric),2) AS load0

, ROUND(avg(load1::numeric),2) AS load1

, ROUND(avg(load2::numeric),2) AS load2

, ROUND((avg(disk_rb_rate::numeric) / 1024 / 1024),2) AS disk_R_MBs

, ROUND((avg(disk_wb_rate::numeric) / 1024 / 1024),2) AS disk_W_MBs

, ROUND((avg(net_rb_rate::numeric) / 1024 / 1024),2) AS net_I_MBs

, ROUND((avg(net_wb_rate::numeric) / 1024 / 1024),2) AS net_O_MBs

FROM

gpmetrics.gpcc_system_history gsh

WHERE hostname LIKE 'mdw%'

AND ctime >= '2023-09-14 16:30:00'

AND ctime < '2023-09-14 19:00:00'

GROUP BY 1,2

ORDER BY 1

;

-- 系统内存和swap信息

SELECT DISTINCT gsh.hostname,

ROUND(gsh.mem_total::numeric/1024/1024/1024) AS mem_total_G,

ROUND(gsh.swap_total::numeric/1024/1024/1024) AS swap_total_G

FROM gpmetrics.gpcc_system_history gsh

where ctime >= NOW() - INTERVAL '1 minute'

and gsh.mem_total > 0

order by 1

limit 10;

系统负载SQL（历史SQL语句）


SELECT sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END)  AS dur0_20
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
               AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END)    AS dur20_40
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
               AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END)    AS dur40_60
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
               AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END)   AS dur60_120
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
               AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END)   AS dur120_300
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
               AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END)   AS dur300_600
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS dur600plus
FROM gpmetrics.gpcc_queries_history
WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15';

SELECT
     elp20_40
   , elp20_40 / cnt20_40     avg20_40
   , elp40_60
   , elp40_60 / cnt40_60     avg40_60
   , elp60_120
   , elp60_120 / cnt60_120   avg60_120
   , elp120_300
   , elp120_300 / cnt120_300 avg120_300
   , elp300_600
   , elp300_600 / cnt300_600 avg300_600
   , elp600plus
   , elp600plus / cnt600plus avg600plus
FROM (
SELECT
     sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END)   AS cnt0_20
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
              AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END)    AS cnt20_40
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
              AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END)    AS cnt40_60
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
              AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END)   AS cnt60_120
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
              AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END)   AS cnt120_300
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
              AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END)   AS cnt300_600
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS cnt600plus
   , sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp0_20
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
              AND tfinish - tsubmit < INTERVAL '40s' THEN tfinish - tsubmit ELSE interval '0s' END)    AS elp20_40
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
              AND tfinish - tsubmit < INTERVAL '60s' THEN tfinish - tsubmit ELSE interval '0s' END)    AS elp40_60
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
              AND tfinish - tsubmit < INTERVAL '120s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp60_120
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
              AND tfinish - tsubmit < INTERVAL '300s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp120_300
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
              AND tfinish - tsubmit < INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp300_600
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp600plus
FROM gpmetrics.gpcc_queries_history
WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15'
) dt;

SELECT
  substring(md5(username) FROM 1 FOR 7)
, cnt_1
, time_1::interval(0)
, CASE WHEN cnt_1 > 0 THEN EXTRACT(EPOCH FROM time_1/cnt_1) ELSE NULL END avg_1
, cnt_0
, time_0::interval(0)
, CASE WHEN cnt_0 > 0 THEN EXTRACT(EPOCH FROM time_0/cnt_0) ELSE NULL END avg_0
FROM (
SELECT
  username
, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_1
, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_1
, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_0
, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_0
FROM gpmetrics.gpcc_queries_history
WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15'
GROUP BY username
) dt
ORDER BY cnt_1 + cnt_0 DESC;

-- 查询单条SQL执行频率(注意：不能配置gpcc中忽略1秒的查询这个选项)
SELECT (tfinish - tsubmit) exec_time, gqh.* 
from  gpmetrics.gpcc_queries_history gqh
WHERE ctime >= '2023-06-10 08:30' AND ctime < '2023-07-15'
and tfinish - tsubmit >= INTERVAL '120s'  and  tfinish - tsubmit <= INTERVAL '300s'
and gqh.username <>'xxt' 
and gqh.query_text like 'delete from aa a%'
ORDER BY gqh.tstart;

SELECT substr(gqh.ctime::VARCHAR,0,14),substr(gqh.query_text,0,100) query_text,count(*)
from  gpmetrics.gpcc_queries_history gqh
WHERE ctime >= '2023-07-19 00:01' AND ctime <= '2023-07-20 23:59'
and gqh.username <>'xxt'
and gqh.query_text like 'SELECT c.oid, a.attnum, a.attname%'
group by substr(gqh.ctime::VARCHAR,0,14),substr(gqh.query_text,0,100)
ORDER BY 1;

-- 历史SQL执行计划
select * from gpmetrics.gpcc_plannode_history gph WHERE gph.ssid=3961340 and ccnt=3  limit 10;

SELECT (tfinish - tsubmit) exec_time, gqh.* from  gpmetrics.gpcc_queries_history gqh
WHERE ctime >= '2023-05-01 00:00' AND ctime < '2023-05-02'
and gqh.username <>'xxt' 
and gqh.ssid=3961340 and gqh.ccnt=3
ORDER BY gqh.tstart;

-- 执行总次数在10次以上，平均耗时在10秒以上的SQL语句
select query_text, cnt 执行次数,
EXTRACT(HOUR FROM all_time) || 'h ' ||   EXTRACT(MINUTE FROM all_time) || 'm ' || ROUND(EXTRACT(SECOND FROM all_time)::numeric, 2) ||'s' 总耗时,
EXTRACT(HOUR FROM all_time/cnt) || 'h ' ||   EXTRACT(MINUTE FROM all_time/cnt) || 'm ' || ROUND(EXTRACT(SECOND FROM all_time/cnt)::numeric, 2) ||'s' 平均耗时
 from (
SELECT  substr(gqh.query_text,0,200) query_text, 
count(*) cnt, 
sum(tfinish - tsubmit) all_time
 from  gpmetrics.gpcc_queries_history gqh
WHERE ctime >= '2023-07-14 08:30' AND ctime < '2023-07-15'
and gqh.username <>'xxt'
GROUP BY  substr(gqh.query_text,0,200) 
having count(*) > 10
) aa where all_time/cnt > INTERVAL '10s'   order by aa.all_time desc;

-- 全表扫描的SQL
select gqh.ctime,gqh.username,gqh.db,gqh.query_text, string_agg(gph.relation_name::text, ',') AS relation_name
from  gpmetrics.gpcc_queries_history gqh,
    gpmetrics.gpcc_plannode_history gph 
WHERE gqh.ssid=gph.ssid 
and gqh.ccnt=gph.ccnt
and gqh.ctime=gph.ctime
and  gph.node_type='Table Scan'
and  gph.ctime>='2023-07-17'
group by  gqh.ctime,gqh.username,gqh.db,gqh.query_text
ORDER BY 1
limit 1000
;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

SELECT sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END) AS dur0_20

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'

AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END) AS dur20_40

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'

AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END) AS dur40_60

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'

AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END) AS dur60_120

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'

AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END) AS dur120_300

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'

AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END) AS dur300_600

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS dur600plus

FROM gpmetrics.gpcc_queries_history

WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15';

SELECT

elp20_40

, elp20_40 / cnt20_40 avg20_40

, elp40_60

, elp40_60 / cnt40_60 avg40_60

, elp60_120

, elp60_120 / cnt60_120 avg60_120

, elp120_300

, elp120_300 / cnt120_300 avg120_300

, elp300_600

, elp300_600 / cnt300_600 avg300_600

, elp600plus

, elp600plus / cnt600plus avg600plus

FROM (

SELECT

sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END) AS cnt0_20

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'

AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END) AS cnt20_40

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'

AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END) AS cnt40_60

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'

AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END) AS cnt60_120

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'

AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END) AS cnt120_300

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'

AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END) AS cnt300_600

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS cnt600plus

, sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp0_20

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'

AND tfinish - tsubmit < INTERVAL '40s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp20_40

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'

AND tfinish - tsubmit < INTERVAL '60s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp40_60

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'

AND tfinish - tsubmit < INTERVAL '120s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp60_120

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'

AND tfinish - tsubmit < INTERVAL '300s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp120_300

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'

AND tfinish - tsubmit < INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp300_600

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp600plus

FROM gpmetrics.gpcc_queries_history

WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15'

) dt;

SELECT

substring(md5(username) FROM 1 FOR 7)

, cnt_1

, time_1::interval(0)

, CASE WHEN cnt_1 > 0 THEN EXTRACT(EPOCH FROM time_1/cnt_1) ELSE NULL END avg_1

, cnt_0

, time_0::interval(0)

, CASE WHEN cnt_0 > 0 THEN EXTRACT(EPOCH FROM time_0/cnt_0) ELSE NULL END avg_0

FROM (

SELECT

username

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_1

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_1

, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_0

, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_0

FROM gpmetrics.gpcc_queries_history

WHERE ctime >= '2023-07-14' AND ctime < '2023-07-15'

GROUP BY username

) dt

ORDER BY cnt_1 + cnt_0 DESC;

-- 查询单条SQL执行频率(注意：不能配置gpcc中忽略1秒的查询这个选项)

SELECT (tfinish - tsubmit) exec_time, gqh.*

from gpmetrics.gpcc_queries_history gqh

WHERE ctime >= '2023-06-10 08:30' AND ctime < '2023-07-15'

and tfinish - tsubmit >= INTERVAL '120s' and tfinish - tsubmit <= INTERVAL '300s'

and gqh.username <>'xxt'

and gqh.query_text like 'delete from aa a%'

ORDER BY gqh.tstart;

SELECT substr(gqh.ctime::VARCHAR,0,14),substr(gqh.query_text,0,100) query_text,count(*)

from gpmetrics.gpcc_queries_history gqh

WHERE ctime >= '2023-07-19 00:01' AND ctime <= '2023-07-20 23:59'

and gqh.username <>'xxt'

and gqh.query_text like 'SELECT c.oid, a.attnum, a.attname%'

group by substr(gqh.ctime::VARCHAR,0,14),substr(gqh.query_text,0,100)

ORDER BY 1;

-- 历史SQL执行计划

select * from gpmetrics.gpcc_plannode_history gph WHERE gph.ssid=3961340 and ccnt=3 limit 10;

SELECT (tfinish - tsubmit) exec_time, gqh.* from gpmetrics.gpcc_queries_history gqh

WHERE ctime >= '2023-05-01 00:00' AND ctime < '2023-05-02'

and gqh.username <>'xxt'

and gqh.ssid=3961340 and gqh.ccnt=3

ORDER BY gqh.tstart;

-- 执行总次数在10次以上，平均耗时在10秒以上的SQL语句

select query_text, cnt 执行次数,

EXTRACT(HOUR FROM all_time) || 'h ' || EXTRACT(MINUTE FROM all_time) || 'm ' || ROUND(EXTRACT(SECOND FROM all_time)::numeric, 2) ||'s' 总耗时,

EXTRACT(HOUR FROM all_time/cnt) || 'h ' || EXTRACT(MINUTE FROM all_time/cnt) || 'm ' || ROUND(EXTRACT(SECOND FROM all_time/cnt)::numeric, 2) ||'s' 平均耗时

from (

SELECT substr(gqh.query_text,0,200) query_text,

count(*) cnt,

sum(tfinish - tsubmit) all_time

from gpmetrics.gpcc_queries_history gqh

WHERE ctime >= '2023-07-14 08:30' AND ctime < '2023-07-15'

and gqh.username <>'xxt'

GROUP BY substr(gqh.query_text,0,200)

having count(*) > 10

) aa where all_time/cnt > INTERVAL '10s' order by aa.all_time desc;

-- 全表扫描的SQL

select gqh.ctime,gqh.username,gqh.db,gqh.query_text, string_agg(gph.relation_name::text, ',') AS relation_name

from gpmetrics.gpcc_queries_history gqh,

gpmetrics.gpcc_plannode_history gph

WHERE gqh.ssid=gph.ssid

and gqh.ccnt=gph.ccnt

and gqh.ctime=gph.ctime

and gph.node_type='Table Scan'

and gph.ctime>='2023-07-17'

group by gqh.ctime,gqh.username,gqh.db,gqh.query_text

ORDER BY 1

limit 1000

;

查询pg_log日志

视图gpmetrics.gpcc_pg_log_history只记录日志级别为FATAL、ERROR和WARNING的日志。

视图gpmetrics.gp_log_master_ext有记录LOG级别的日志，但是时间比较短。

-- 找到发生切换的时间点
select * from gp_configuration_history ORDER BY  time desc limit 100;

-- 查询时间视图
select 'gp_log_master_ext' tb,min(logtime),max(logtime) from gpmetrics.gp_log_master_ext
union all
select 'gpcc_pg_log_history' tb,min(logtime),max(logtime) from gpmetrics.gpcc_pg_log_history;

-- 查询切换时的详细日志（有时候查不出来，需要去直接查询文件）
SELECT
    d.logtime,
    d.loguser,
    d.logdatabase,
    d.loghost,
    d.logsessiontime,
    d.logseverity,
    d.logmessage,
    d.logdebug,
    d.logdetail,
    logcontext
from
    gpmetrics.gpcc_pg_log_history d 
where
    d.logseverity not in ( 'LOG' ) 
    and d.logstate not in ( '58P01' ) -- and d.logtime >= now()-interval '7 day' 
    and d.logtime >= '2023-07-11 10:30' 
    and d.logtime <= '2023-07-11 17:34'
    and ( d.logmessage like 'Out of memory%' or d.logdetail like '%Cannot allocate memory%' or d.logdetail like '%FATAL:  out of memory%')
order by d.logtime 
    limit 500;

SELECT d.logtime,
    d.loguser,
    d.logdatabase,
    d.loghost,
    d.logsessiontime,
    d.logseverity,
    d.logmessage,
    d.logdebug,
    d.logdetail,
    logcontext 
FROM gpmetrics.gp_log_master_ext d 
where d.logseverity not in ('LOG') 
and d.logstate not in ('58P01')
-- and d.logtime >= now()-interval '7 day' 
 and d.logtime >= '2023-07-10 10:00' 
 and d.logtime <= '2023-07-10 11:01'
order by d.logtime  
;

SELECT *
from
    gpmetrics.gp_log_master_ext d
where d.logtime >='2023-07-19'
and d.logdebug like 'SELECT c.oid, a.attnum, a.attname%'
order by d.logtime 
;

-- 查看OS文件日志
ps -ef|grep post| grep bin

cd /opt/greenplum/data/master/gpseg-1/pg_log

-- 找到发生切换的时间点

select * from gp_configuration_history ORDER BY time desc limit 100;

-- 查询时间视图

select 'gp_log_master_ext' tb,min(logtime),max(logtime) from gpmetrics.gp_log_master_ext

union all

select 'gpcc_pg_log_history' tb,min(logtime),max(logtime) from gpmetrics.gpcc_pg_log_history;

-- 查询切换时的详细日志（有时候查不出来，需要去直接查询文件）

SELECT

d.logtime,

d.loguser,

d.logdatabase,

d.loghost,

d.logsessiontime,

d.logseverity,

d.logmessage,

d.logdebug,

d.logdetail,

logcontext

from

gpmetrics.gpcc_pg_log_history d

where

d.logseverity not in ( 'LOG' )

and d.logstate not in ( '58P01' ) -- and d.logtime >= now()-interval '7 day'

and d.logtime >= '2023-07-11 10:30'

and d.logtime <= '2023-07-11 17:34'

and ( d.logmessage like 'Out of memory%' or d.logdetail like '%Cannot allocate memory%' or d.logdetail like '%FATAL: out of memory%')

order by d.logtime

limit 500;

SELECT d.logtime,

d.loguser,

d.logdatabase,

d.loghost,

d.logsessiontime,

d.logseverity,

d.logmessage,

d.logdebug,

d.logdetail,

logcontext

FROM gpmetrics.gp_log_master_ext d

where d.logseverity not in ('LOG')

and d.logstate not in ('58P01')

-- and d.logtime >= now()-interval '7 day'

and d.logtime >= '2023-07-10 10:00'

and d.logtime <= '2023-07-10 11:01'

order by d.logtime

;

SELECT *

from

gpmetrics.gp_log_master_ext d

where d.logtime >='2023-07-19'

and d.logdebug like 'SELECT c.oid, a.attnum, a.attname%'

order by d.logtime

;

-- 查看OS文件日志

ps -ef|grep post| grep bin

cd /opt/greenplum/data/master/gpseg-1/pg_log

告警日志

SELECT  * 
FROM    gpmetrics.gpcc_alert_history d 
where   d.transaction_time >= '2023-07-11'
ORDER BY 1
;

SELECT *

FROM gpmetrics.gpcc_alert_history d

where d.transaction_time >= '2023-07-11'

ORDER BY 1

;

数据库查询数历史

SELECT  * 
FROM    gpmetrics.gpcc_database_history d
where d.ctime>='2023-07-17 08:07:00'
order by 1;

-- gpcc集群指标中的查询量
SELECT
    substr(d.ctime::VARCHAR,0,17) ctime_m,
    sum(d.queries_total) queries_total,
    sum(d.queries_running) queries_running,
    sum(d.queries_queued) queries_queued,
    sum(d.queries_blocked) queries_blocked,
    round(avg(d.queries_finished)) queries_finished
FROM    gpmetrics.gpcc_database_history d 
WHERE   ctime >= '2023-07-19 08:14' AND ctime < '2023-07-19 08:20' 
GROUP BY    ctime_m 
order BY 1;

-- (注意：不能配置gpcc中忽略1秒的查询这个选项)
SELECT substr(gqh.ctime::VARCHAR,0,17) ctime_m,count(*)/4 queries
from  gpmetrics.gpcc_queries_history gqh
WHERE ctime >= '2023-07-19 08:14' AND ctime < '2023-07-19 08:20'
group by ctime_m
order by 1;

SELECT *

FROM gpmetrics.gpcc_database_history d

where d.ctime>='2023-07-17 08:07:00'

order by 1;

-- gpcc集群指标中的查询量

SELECT

substr(d.ctime::VARCHAR,0,17) ctime_m,

sum(d.queries_total) queries_total,

sum(d.queries_running) queries_running,

sum(d.queries_queued) queries_queued,

sum(d.queries_blocked) queries_blocked,

round(avg(d.queries_finished)) queries_finished

FROM gpmetrics.gpcc_database_history d

WHERE ctime >= '2023-07-19 08:14' AND ctime < '2023-07-19 08:20'

GROUP BY ctime_m

order BY 1;

-- (注意：不能配置gpcc中忽略1秒的查询这个选项)

SELECT substr(gqh.ctime::VARCHAR,0,17) ctime_m,count(*)/4 queries

from gpmetrics.gpcc_queries_history gqh

WHERE ctime >= '2023-07-19 08:14' AND ctime < '2023-07-19 08:20'

group by ctime_m

order by 1;

磁盘空间使用历史

-- 有时不准
SELECT 
   ctime::date || ' ' 
    -- || CASE WHEN extract(hour from ctime) < 10 THEN '0' ELSE '' END ||extract(hour from ctime)
 -- ||':'|| CASE WHEN extract(MINUTE from ctime) < 10 THEN '0' ELSE '' END || extract(MINUTE from ctime)
     AS time1
, hostname,
d.filesystem,
d.data_dirs
, ROUND(AVG(total_bytes::numeric/1024/1024/1024),2) AS total_bytes
, ROUND(AVG(bytes_used::numeric/1024/1024/1024),2) AS bytes_used
, ROUND(AVG(bytes_available::numeric/1024/1024/1024),2) AS bytes_available 
FROM    gpmetrics.gpcc_disk_history d 
where
    d.hostname like 'sdw2'
    and d.filesystem not in ('/boot')
    and d.data_dirs not in ('{}')
AND ctime >= '2023-01-01 11:00:00'
AND ctime < '2023-10-16 00:00:00'
    GROUP BY 1,2,3,4
    order by 1,2,3,4
    limit 100;

-- 有时不准

SELECT

ctime::date || ' '

-- || CASE WHEN extract(hour from ctime) < 10 THEN '0' ELSE '' END ||extract(hour from ctime)

-- ||':'|| CASE WHEN extract(MINUTE from ctime) < 10 THEN '0' ELSE '' END || extract(MINUTE from ctime)

AS time1

, hostname,

d.filesystem,

d.data_dirs

, ROUND(AVG(total_bytes::numeric/1024/1024/1024),2) AS total_bytes

, ROUND(AVG(bytes_used::numeric/1024/1024/1024),2) AS bytes_used

, ROUND(AVG(bytes_available::numeric/1024/1024/1024),2) AS bytes_available

FROM gpmetrics.gpcc_disk_history d

where

d.hostname like 'sdw2'

and d.filesystem not in ('/boot')

and d.data_dirs not in ('{}')

AND ctime >= '2023-01-01 11:00:00'

AND ctime < '2023-10-16 00:00:00'

GROUP BY 1,2,3,4

order by 1,2,3,4

limit 100;

索引膨胀率

select * from gpmetrics.gpcc_index_info;

1	select * from gpmetrics.gpcc_index_info;

跟踪SQL

控制写入日志文件的有关查询工作进程创建和查询管理的信息量。默认值为OFF，不记录信息。

set gp_log_gang=debug;

1	set gp_log_gang=debug;

参考

https://mp.weixin.qq.com/s/G5cPCt7remAuDJHGadOI2w
http://t.zoukankan.com/butterflies-p-12056514.html

https://blog.csdn.net/weixin_45428187/article/details/125600114

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/74e3477d2a3f3548.md

打赏赞(5)

标签： DBA脚本 GreenPlum 日常维护日常运维脚本脚本分享

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

AI (2)
ClickHouse (6)
DM达梦 (9)
Docker (48)
GBase (37)
Greenplum (110)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (5)
MySQL (211)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (11)
Oracle (634)
OS (389)
PostgreSQL (262)
Python (26)
Redis (21)
SQL Server (182)
SQLite (1)
StarRocks (21)
TDSQL (5)
TiDB (19)
YMatrix (5)
中间件 (10)
云 (10)
人大金仓KingBase (9)
其它 (83)
培训考证 (45)
大数据 (6)
存储 (8)
巡检监控 (47)
工具 (101)
数据迁移 (16)
架构篇 (12)
生活 (13)
电脑 (18)
网络 (96)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部