Greenplum 实时数据仓库实践（9）——Greenplum监控与运维

2022-04-13 18:09:32 浏览数 (1)

目录

9.1 权限与角色管理

9.1.1 Greenplum中的角色与权限

9.1.2 管理角色及其成员

9.1.3 管理对象权限

9.1.4 口令加密

9.2 数据导入导出

9.2.1 file协议及其外部表

9.2.2 gpfdist协议及其外部表

9.2.3 基于Web的外部表

9.2.4 外部表错误处理

9.2.5 使用gpload导入数据

9.2.6 使用COPY互拷数据

9.2.7 导出数据

9.2.8 格式化数据文件

9.3 性能优化

9.3.1 常用优化手段

9.3.2 控制溢出文件

9.3.3 查询剖析

9.4 例行监控

9.4.1 检查系统状态

9.4.2 检查磁盘空间使用

9.4.3 检查数据分布倾斜

9.4.4 查看数据库对象的元数据信息

9.4.5 查看会话的内存使用

9.4.6 查看工作文件使用信息

9.4.7 查看服务器日志文件

9.5 例行维护

9.5.1 定期vacuum

9.5.2 定期维护系统目录

9.5.3 加强的系统目录维护

9.5.4 为查询优化执行vacuum与analyze

9.5.5 统计信息自动收集

9.5.6 重建索引

9.5.7 管理数据库日志文件

9.6 推荐的监控与维护任务

9.6.1 数据库活动监控

9.6.2 硬件和操作系统监控

9.6.3 系统目录监控

9.6.4 数据库维护

9.6.5 补丁与升级

小结

想要一个数据库长久健康的运行，离不开完备的运维工作，切忌只运而不维。针对Greenplum分布式数据库，集群由大量服务器组成，对运维人员或DBA，不仅要关注数据库本身，还要注意集群中各硬件的状况，及时发现并处理问题。本篇介绍权限与角色管理、数据导入导出、性能优化、例行监控、例行维护、推荐的监控与维护任务六方面常规工作内容，目标是满足Greenplum系统维护、使用等方面的要求，保证提供稳定高效的数据库服务。

9.1 权限与角色管理

从“4.6 允许客户端连接”一节中已知，pg_hba.conf文件限定了允许连接Greenplum的客户端主机、用户名、访问的数据库，认证方式等。而用户名、口令，以及用户对数据库对象的使用权限保存在Greenplum的pg_authid、pg_roles、pg_class等元数据表中。

9.1.1 Greenplum中的角色与权限

Greenplum采用基于角色的访问控制机制。通过角色机制，简化了用户和权限的关联性。Greenplum系统中的权限分为两种：系统权限和对象权限。系统权限是指系统规定用户使用数据库的权限，如连接数据库、创建数据库、创建用户等。对象权限是指在表、序列、函数等数据库对象上执行特殊动作的权限，其权限类型有select、insert、update、delete、references、create、connect、temporary、execute和usage等。

Greenplum的角色与Oracle、SQL Server等数据库中的角色概念有所不同。这些系统中的所谓角色，是权限的组合和抽象，创建角色最主要的目的是简化对用户的授权。举一个简单的例子，假设需要给五个用户每个授予相同的五种权限，如果没有角色，需要授权二十五次，而如果把五种权限定义成一种角色，只需要先进行一次角色定义，再授权五次即可。

然而Greenplum中的角色既可以代表一个数据库用户，又可以代表一组权限。角色所拥有的预定义的系统权限是通过角色属性实现的。角色可以是数据库对象的属主，也可以给其他角色赋予访问对象的权限。角色可以是其他角色的成员，成员角色可以从父角色继承对象权限。

Greenplum系统可能包含多个数据库角色（用户或组），这些角色并不是运行服务器上操作系统的用户和组。为方便起见，可能希望维护操作系统用户名和Greenplum角色名的关系，因为很多客户端应用程序，如psql，使用当前操作系统用户名作为缺省的角色，gpadmin就以最典型的例子。

用户通过Master实例连接Greenplum，Master使用pg_hba.conf文件里的条目验证用户的角色和访问权限。之后Master以当前登录的角色，从后台向Segment实例发布SQL命令。系统级定义的角色对所有数据库都是有效的。为了创建更多角色，首先需要使用超级用户gpadmin连接Greenplum。

配置角色与权限时，应该注意以下问题：

  • 保证gpadmin系统用户安全。Greenplum需要一个UNIX用户ID安装和初始化Greenplum系统，这个系统用户ID就是gpadmin。gpadmin用户是Greenplum中缺省的数据库超级用户，也是Greenplum安装及其底层数据文件的文件系统属主。这个缺省的管理员账号是Greenplum的基础设计，缺少这个用户系统无法运行，并且没有方法能够限制gpadmin用户对数据库的访问。应该只使用gpadmin账号执行诸如扩容和升级之类的系统维护任务。任何以这个用户登录Greenplum主机的人，都可以读取、修改和删除任何数据，尤其是系统目录相关的数据库访问权力。因此，gpadmin用户的安全非常重要，仅应该提供给关键的系统管理员使用。应用的数据库用户应该永不作为gpadmin登录。
  • 赋予每个登录用户不同的角色。出于记录和审核目的，每个登录Greenplum的用户都应该被赋予相应的数据库角色。对于应用程序或者Web服务，最好为每个应用或服务创建不同的角色。
  • 使用组管理访问权限。
  • 限制具有超级用户角色属性的用户。超级用户角色绕过Greenplum中所有的访问权限检查和资源队列，所以只应该将超级用户权限授予系统管理员。

9.1.2 管理角色及其成员

这里的角色指的是一个可以登录到数据库，并开启一个数据库会话的用户。建议在创建角色时为其指定资源队列，否则缺省使用pg_default。CREATE ROLE命令用于创建一个角色，例如：

代码语言：javascript复制
create role jsmith with login;

一个数据库角色有很多属性，用以定义该角色可以在数据库中执行的任务，或者具有的系统权限。表9-1描述了有效的角色属性。

属性

描述

SUPERUSER | NOSUPERUSER

确定一个角色是否是超级用户。只有超级用户才能创建新的超级用户。缺省值为NOSUPERUSER

CREATEDB | NOCREATEDB

确定角色是否被允许创建数据库。缺省值为NOCREATEDB

CREATEROLE | NOCREATEROLE

确定角色是否被允许创建和管理其他角色。缺省值为NOCREATEROLE

INHERIT | NOINHERIT

确定角色是否从其所在的组继承权限。具有INHERIT属性的角色可以自动使用所属组已经被授予的数据库权限，无论角色是组的直接成员还是间接成员。缺省值为INHERIT

LOGIN | NOLOGIN

确定角色是否可以登录。具有LOGIN属性的角色可以将角色作为用户登录。没有此属性的角色被用于管理数据库权限（即用户组）。缺省值为NOLOGIN

CONNECTION LIMIT connlimit

如果角色能够登录，此属性指定角色可以建立多少个并发连接。缺省值为-1，表示没有限制

PASSWORD ‘password’

设置角色的口令。如果不使用口令认证，可以忽略此选项。如果没有指定口令，口令将被设置为null，此时该用户的口令认证总是失败。一个null口令也可以显示的写成PASSWORD NULL

ENCRYPTED | UNENCRYPTED

控制口令是否加密存储在系统目录中。缺省行为由password_encryption配置参数所决定，当前设置是MD5，如果要改为SHA-256加密，设置此参数为password。如果给出的口令字符串已经是加密格式，那么它被原样存储，而不管指定ENCRYPTED还是UNENCRYPTED。这种设计允许在dump/restore时重新导入加密的口令

VALID UNTIL ‘timestamp’

设置一个日期和时间，在该时间点后角色的口令失效。如果忽略此选项，口令将永久有效

RESOURCE QUEUE queue_name

赋予角色一个命名的资源队列用于负载管理。角色发出的任何语句都受到该资源队列的限制。注意，这个RESOURCE QUEUE属性不会被继承，必须在每个用户级（登录）角色设置

DENY {deny_interval | deny_point}

在此时间区间内禁止访问

表9-1 角色属性

可以在创建角色时，或者创建角色后使用ALTER ROLE命令指定这些属性：

代码语言：javascript复制
alter role jsmith with password 'passwd123';  
alter role jsmith valid until 'infinity';  
alter role jsmith login;  
alter role jsmith resource queue adhoc;  
alter role jsmith deny day 'sunday';

使用drop role或drop user命令删除角色（用户）。在删除角色前，先要收回角色所拥有的全部权限，或者先删除与角色相关联的所有对象，否则删除角色时会提示“cannot be dropped because some objects depend on it”错误。

通常将多个权限合成一组，能够简化对权限的管理。使用这种方法，对于一个组中的用户，其权限可以被整体授予和回收。在Greenplum中的实现方式为，创建一个表示组的角色，然后将用户角色授予组角色的成员。下面的SQL命令使用CREATE ROLE创建一个名为admin组角色，该组角色具有CREATEROLE和CREATEDB系统权限。

代码语言：javascript复制
create role admin createrole createdb;

一旦组角色存在，就可以使用GRANT和REVOKE命令添加或删除组成员（用户角色）：

代码语言：javascript复制
grant admin to john, sally;  
revoke admin from bob;

为简化对象权限的管理，应当只为组级别的角色授予适当的权限。成员用户角色继承组角色的对象权限：

代码语言：javascript复制
grant all on table mytable to admin;  
grant all on schema myschema to admin;  
grant all on database mydb to admin;

角色属性LOGIN、SUPERUSER、CREATEDB和CREATEROLE不会当做普通的数据库对象权限被继承。为了让用户成员使用这些属性，必须执行SET ROLE设置一个角色具有这些属性。在上面的例子中，我们已经为admin指定了CREATEDB和CREATEROLE属性。sally是admin的成员，当以sally连接到数据库后，执行以下命令，使sally可以拥有父角色的CREATEDB和CREATEROLE属性。

代码语言：javascript复制
set role admin;

有关角色属性信息可以在系统表pg_authid中找到，pg_roles是基于系统表pg_authid的视图。系统表pg_auth_members存储了角色与其成员的关系。

9.1.3 管理对象权限

当一个对象（表、视图、序列、数据库、函数、语言、模式或表空间）被创建，它的权限被赋予属主。属主通常是执行CREATE语句的角色。对于大多数类型的对象，其初始状态是只允许属主或超级用户在对象上做任何操作。为了允许其他角色使用对象，必须授予适当的权限。Greenplum对每种对象类型支持的权限如表9-2所示。

对象类型

权限

Tables、Views、Sequences

SELECT、INSERT、RULE、ALL

External Tables

SELECT、RULE、ALL

Databases

CONNECT、CREATE、TEMPORARY | TEMP、ALL

Functions

EXECUTE

Procedural Languages

USAGE

Schemas

CREATE、USAGE、ALL

Custom Protocol

SELECT、INSERT、RULE、ALL

表9-2 对象权限

必须为每个对象单独授权。例如，授予数据库上的ALL权限，并不会授予数据库中全部对象的访问权限，而只是授予了该数据库自身的数据库级别的全部权限（CONNECT、CREATE、TEMPORARY等）。

使用标准的GRANT和REVOKE SQL语句为角色授予或回收一个对象权限：

代码语言：javascript复制
grant insert on mytable to jsmith;  
revoke all privileges on mytable from jsmith;

可以使用DROP OWNED和REASSIGN OWNED命令为一个角色删除或重新赋予对象属主权限。只有对象的属主或超级用户能够执行此操作：

代码语言：javascript复制
reassign owned by sally to bob;  
drop owned by visitor;

Greenplum不支持行级和列级的访问控制，但是可以通过视图来模拟，限制查询的行或列。此时角色被授予对视图而不是基表的访问权限。对象权限存储在pg_class.relacl列中。Relacl是PostgreSQL支持的数组属性，该数组成员是抽象的数据类型aclitem。每个ACL实际上是一个由多个aclitem构成的链表。

9.1.4 口令加密

Greenplum缺省使用MD5为用户口令加密，通过适当配置服务器参数，也能实现口令的SHA-256加密存储。为了使用SHA-256加密，客户端认证方法必须设置为PASSWORD而不是缺省的MD5。口令虽然以加密形式存储在系统表中，但仍然以明文在网络间传递。为了避免这种情况，应该建立客户端与服务器之间的SSL加密通道。

1. 系统级启用SHA-256加密

代码语言：javascript复制
# 设置缺省的口令加密算法
gpconfig -c password_hash_algorithm -v 'SHA-256'
# 重载参数使之动态生效
gpstop -u
# 查看
gpconfig -s password_hash_algorithm

2. 会话级启用SHA-256加密

代码语言：javascript复制
-- 用gpadmin登录Greenplum后执行
set password_hash_algorithm = 'SHA-256';
-- 查看
show password_hash_algorithm;

3. 验证口令加密方式生效 （1）建立一个具有login权限的新角色，并设置口令。

代码语言：javascript复制
create role testdb with password 'testdb12345#' login;

（2）修改客户端认证方法，允许存储SHA-256加密的口令。 下面的shell命令将在pg_hba.conf文件的第一行添加一条记录。注意pg_hba.conf文件中记录的匹配顺序。

代码语言：javascript复制
sed -i '1ihost all testdb 0.0.0.0/0 password' /data/master/gpseg-1/pg_hba.conf

（3）重载pg_hba.conf配置

代码语言：javascript复制
gpstop -u

（4）以刚创建的testdb用户登录数据库，在提示时输入正确的口令。

代码语言：javascript复制
psql -d postgres -h mdw -U testdb

验证口令被以SHA-256哈希方式存储，加密后的口令存储在pg_authid.rolpasswod字段中。作为超级用户登录，执行下面的查询：

代码语言：javascript复制
postgres=# select rolpassword from pg_authid where rolname = 'testdb';
                              rolpassword                               
------------------------------------------------------------------------
 sha25650c2445bab257f4ea94ee12e5a6bf1400b00a2c317fc06b6ff9b57975bd1cde1
(1 row)

9.2 数据导入导出

本节介绍Greenplum的各种数据导入导出方法。所选择的方法依赖于数据源的特性，如位置、数据量、格式、需要的转换等。最简单的情况下，一条COPY命令就可将Greenplum主实例上的文本文件导入表中。对于少量数据，这种方式不需要更多步骤，并提供了良好的性能。COPY命令在Master主机上的单个文件与数据库表之间拷贝数据。这种方式拷贝的数据量受限于文件所在系统所允许的单一文件最大字节数。对于大数据集，更为有效的数据装载方式是利用多个Segments并行导入数据。该方式允许同时从多个文件系统导入数据，实现很高的数据传输速率。用gpfdist创建的外部表会使用所有Segment导入或导出数据，并且完全并行操作。

无论使用哪种方法，导入完数据都应运行ANALYZE。ANALYZE或VACUUM ANALYZE（只对系统目录表）为查询优化器更新表的统计信息，以做出最好的查询计划，避免由于数据增长或缺失统计信息导致性能问题。

9.2.1 file协议及其外部表

file://协议用于指定操作系统文件位置的URI中。URI包括主机名、端口和文件路径。每个文件必须位于Greenplum数据库超级用户（gpadmin）可访问的Segment主机上。URI中使用的主机名必须与gp_segment_configuration系统目录表中注册的段主机名匹配。LOCATION子句可以有多个URI。

通过定义file协议的外部表，可以很容易地将外部数据导入普通表中，如下例所示。

代码语言：javascript复制
# 分隔符的16进制
select to_hex(ascii('|'));
# 创建外部表
c dw gpadmin
create external table files_zz_ext (
   fid varchar(128), 
   server varchar(45), 
   ffid varchar(255), 
   flen bigint, 
   filemd5 varchar(64), 
   ttime integer, 
   lvtime integer, 
   vtimes integer, 
   stat smallint ) 
location ('file://mdw:5432/data/zz/files_000', 
          'file://mdw:5432/data/zz/files_001', 
          'file://mdw:5432/data/zz/files_002',
          'file://mdw:5432/data/zz/files_003',
          'file://smdw:5432/data/zz/files_004', 
          'file://smdw:5432/data/zz/files_005',
          'file://smdw:5432/data/zz/files_006',
          'file://sdw3:5432/data/zz/files_007', 
          'file://sdw3:5432/data/zz/files_008',
          'file://sdw3:5432/data/zz/files_009',
format 'text' (delimiter E'x7c' null ''); 

-- 修改外部表属主
alter external table files_zz_ext owner to dwtest;

# 导入数据
set gp_autostats_mode=none;
insert into files_zz1 select * from files_zz_ext;
# 分析表
vacuum freeze analyze files_zz1;

在LOCATION子句中指定的URI数是将并行工作以访问外部表的Segment实例数。对于每个URI，Greenplum将指定主机上的一个Segment分配给文件。为了在导入数据时获得最大的并行性，最好将数据分散到与Segment数量相同的多个文件中，这可确保所有Segment都参与工作。每个Segment主机上的外部文件数不能超过该主机上的Segment实例数。例如，如果集群中每个Segment主机有四个实例，则可以在每个Segment主机上放置四个外部文件。基于file://协议的表只能是可读外部表。

系统视图pg_max_external_files显示每个外部表允许的最大外部表文件数，该视图仅适用于file://协议。

代码语言：javascript复制
postgres=# SELECT * FROM pg_max_external_files;
 hostname | maxfiles 
---------- ----------
 smdw     |        6
 mdw      |        6
 sdw3     |        6
(3 rows)

9.2.2 gpfdist协议及其外部表

1. gpfdist gpfdist是一个并行文件分布程序，用于对本地文件的并行访问。它是一个操作外部表的HTTP服务器，使Segment可以从多个文件系统的外部表并行装载数据。可以在多个不同的主机上运行gpfdist实例，并能够并行使用它们。

可以选择在Master以外的其他机器上运行gpfdist，例如一个专门用于ETL处理的主机。使用gpfdist命令启动gpfdist，该命令位于Master主机和每个Segment主机的$GPHOME/bin目录中。可以在当前目录位置或者指定任意目录启动gpfdist，缺省的端口是8080。下面是一些启动gpfdist的例子。

代码语言：javascript复制
# 处理当前目录中的文件，使用缺省的8080端口
gpfdist &

# 指定要导入的文件目录、HTTP端口号、消息与错误日志文件，进程在后台运行
gpfdist -d /home/gpadmin/load_data/ -p 8081 -l /home/gpadmin/log &

# 在同一个ETL主机上运行多个gpfdist实例，每个实例使用不同的目录和端口
gpfdist -d /home/gpadmin/load_data1/ -p 8081 -l /home/gpadmin/log1 &  
gpfdist -d /home/gpadmin/load_data2/ -p 8082 -l /home/gpadmin/log2 &

# gpfdist不允许在根目录上启动服务，可通过/../的方式间接实现
nohup gpfdist -p 8080 -d /../ &

Greenplum没有提供停止gpfdist的命令，要直接使用操作系统的kill命令停止gpfdist进程。

代码语言：javascript复制
ps -ef | grep gpfdist | grep -v grep | awk '{print $2}' | xargs kill -9

2. gpfdist外部表 在外部数据文件所在的主机上运行gpfdist命令，外部表定义中使用gpfdist://协议引用一个运行的gpfdist实例。gpfdist自动解压缩gzip（.gz）和bzip2（.bz2）文件。可以使用通配符（*）或其它其他C语言风格的模式匹配多个需要读取的文件。指定的文件应该位于启动gpfdist实例时指定的目录下。

为了创建一个gpfdist外部表，需要指定输入文件的格式和外部数据源的位置。使用gpfdist或gpfdists协议（gpfdist的安全版本）之一访问外部表数据源。一条CREATE EXTERNAL TABLE语句中使用的协议必须唯一。

使用gpfdist外部表的步骤如下：

  1. 启动gpfdist文件服务器。
  2. 定义外部表。
  3. 将数据文件放置于外部表定义中指定的位置。
  4. 使用SQL命令查询外部表。

Greenplum提供可读与可写两种gpfdist外部表，但一个外部表不能既可读又可写。一个gpfdist可读外部表的例子如下所示。

代码语言：javascript复制
# 分别在两个主机mdw、smdw上启动gpfdist服务
nohup gpfdist -p 8081 -d /../ &

-- 创建外部表
set search_path to ext;
create external table test_ext1 (
  userid bigint,
  avid bigint,
  playcount bigint,
  praisecount bigint,
  commentcount bigint,
  sharecount bigint,
  updatetime timestamp
) 
location 
('gpfdist://mdw:8081/data/*.txt', 'gpfdist://smdw:8081/data/*.txt')
format 'text' (delimiter '|');

-- 向普通表中装载数据
insert into space.work_heat_user_operate select * from ext.test_ext;

下面的SQL语句建立可写外部表并插入数据。

代码语言：javascript复制
create writable external table example1 (name text, date date, amount float4, category text, desc1 text)  
       location ('gpfdist://mdw:8081/data/sales.out') format 'text' ( delimiter '|' null ' ')  
       distributed by (name);  

insert into example1 values ('aaa','2022-01-01',100.1,'aaa','aaa');  
insert into example1 values ('bbb','2022-01-02',200.1,'bbb','bbb');

结果是在mdw上建立了如下内容的/data/sales.out文件：

代码语言：javascript复制
aaa|2022-01-01|100.1|aaa|aaa
bbb|2022-01-02|200.1|bbb|bbb

可以使用insert into target_table select ... from external_table或create table target_table as select ... from external_table命令从外部表向普通表导入数据。同样也可以使用insert into external_table select ... from normal_table向可写外部表导出数据。drop external table命令只删除外部表定义，并不会删除外部文件的任何内容。

9.2.3 基于Web的外部表

外部表可以是基于文件的或基于Web的。基于文件的外部表访问静态平面文件。在查询运行时数据是静态的，数据可重复读。基于Web的外部表通过Web服务器的http协议或通过执行操作系统命令或脚本，访问动态数据源。数据不可重复读，因为在查询运行时数据可能改变。

CREATE EXTERNAL WEB TABLE语句创建一个web外部表。web外部表允许Greenplum将动态数据源视作一个常规数据库表。可以定义基于命令或基于URL的web外部表，但不能在一条建表命令中混用两种定义。

1. 基于命令的web外部表 用一个shell命令或脚本的输出定义基于命令的web表数据。在CREATE EXTERNAL WEB TABLE语句的EXECUTE子句指定需要执行的命令。外部表中的数据是命令运行时的数据。EXECUTE子句在特定Master或Segment上运行shell命令或脚本。脚本必须是gpadmin用户可执行的，并且位于所有Master和Segment主机的相同位置上，Segment并行运行命令。

外部表定义中指定的命令从数据库执行，数据库不能从.bashrc或.profile获取环境变量，因此需要在EXECUTE子句中设置环境变量。下面的外部表运行一个Greenplum Master主机上的命令。

代码语言：javascript复制
create external web table output (output text)  
execute 'PATH=/home/gpadmin/programs; export PATH; myprogram.sh'  
    on master   
format 'text';

下面的命令定义一个web表，在五个段上运行一个名为get_log_data.sh脚本文件。

代码语言：javascript复制
create external web table log_output (linenum int, message text)   
execute '/home/gpadmin/get_log_data.sh' ON 5   
format 'text' (delimiter '|');

Greenplum集群中每台主机的相同位置上都必须有同一个可执行的脚本，否则查询会报错：

代码语言：javascript复制
dw=# select * from log_output;
ERROR:  external table log_output command ended with error. sh: /home/gpadmin/get_log_data.sh: No such file or directory  (seg7 slice1 140.210.73.66:6001 pid=10461)
DETAIL:  Command: execute:/home/gpadmin/get_log_data.sh

对该外部表的查询会返回每个Segment输出的并集，如get_log_data.sh脚本内容如下：

代码语言：javascript复制
#!/bin/bash  
echo "1|aaa"  
echo "2|bbb"

则该表将返回10条（每个段两条）数据：

代码语言：javascript复制
dw=# select * from log_output;
 linenum | message 
--------- ---------
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
(10 rows)

下面创建一个执行脚本的可写web外部表。

代码语言：javascript复制
create writable external web table example2  
      (name text, date date, amount float4, category text, desc1 text) 
execute 'PATH=/home/gpadmin/programs; export PATH; myprogram1.sh' 
format 'text' (delimiter '|')  
distributed randomly;

可写外部表不能使用on子句，否则报错：

代码语言：javascript复制
ERROR:  ON clause may not be used with a writable external table

myprogram1.sh的内容如下：

代码语言：javascript复制
#!/bin/bash  
while read line  
do  
    echo "File:${line}" >> /home/gpadmin/programs/a.txt  
done

下面将脚本设置为可执行，并复制到集群所有主机的相同目录下：

代码语言：javascript复制
chmod 755 ./programs/myprogram1.sh
scp -r programs 210.73.209.102:/home/gpadmin/
scp -r programs 140.210.73.66:/home/gpadmin/

现在向外部表中插入数据：

代码语言：javascript复制
insert into example2 values 
('aaa','2022-01-01',100.1,'aaa','aaa'), 
('bbb','2022-01-02',200.1,'bbb','bbb'),
('ccc','2022-01-03',300.1,'ccc','ccc');

插入的数据通过管道输出给myprogram1.sh并执行，输出到a.txt文件。这里插入了三条数据，在我的环境中，构成集群的三台主机上都生成了一个a.txt文件，每个文件中保存了一条数据，可见是三个不同主机上的Segment并行向外部文件写入了数据。

2. 基于URL的web外部表 基于URL的web表使用HTTP协议从Web服务器访问数据，web表数据是动态的。在LOCATION子句中使用http://指定文件在Web服务器上的位置。web数据文件必须在所有Segment主机能够访问的Web服务器上。URL的数量对应访问该web表时并行的最少Segment数量。下面的例子定义了一个从多个URL获取数据的web表。

代码语言：javascript复制
create external web table ext_expenses (  
    name text, date date, amount float4, category text, description text)   
location ('http://mdw/sales/file.csv',  
          'http://mdw/exec/file.csv',  
          'http://mdw/finance/file.csv',  
          'http://mdw/ops/file.csv',  
          'http://mdw/marketing/file.csv',  
          'http://mdw/eng/file.csv'   
      )  
format 'csv';

9.2.4 外部表错误处理

使用CREATE TABLE AS SELECT或INSERT INTO命令查询外部表数据时，如果数据包含错误，缺省行为是整条命令失败，没有数据被导入到目标数据库表中。SEGMENT REJECT LIMIT子句允许隔离外部表中格式错误的数据，并继续导入格式正确的行。使用SEGMENT REJECT LIMIT设置一个错误阈值，指定拒绝的数据行数（缺省）或一个占总行数的百分比（1 -~ 100）。如果错误行数达到了SEGMENT REJECT LIMIT的值，整个外部表操作失败，没有数据行被处理。限制的错误行数是相对于一个段而不是整个操作的。如果错误行数没有达到SEGMENT REJECT LIMIT值，操作处理所有正确的行，丢弃错误行，或者可选地将格式错误的行写入日志表。LOG ERRORS子句允许保存错误行以备后续检查。

设置SEGMENT REJECT LIMIT会使Greenplum以单行错误隔离模式扫描外部数据。当外部数据行出现多余属性、缺少属性、数据类型错误、无效的客户端编码序列等格式错误时，单行错误隔离模式将错误行丢弃或写入日志表。Greenplum不检查约束错误，但可以在查询外部表时过滤约束错误。例如，消除重复键值错误：

代码语言：javascript复制
insert into table_with_pkeys select distinct * from external_table;

下面的例子记录错误信息，并设置错误行阈值为10。错误数据通过Greenplum的内部函数gp_read_error_log('external_table_name')访问。

代码语言：javascript复制
create external table ext_expenses ( name text, date date, amount float4, category text, desc1 text )  
       location ('gpfdist://mdw:8081/data/*', 'gpfdist://smdw:8081/data/*')  
       format 'text' (delimiter '|')  
       log errors segment reject limit 10 rows;

9.2.5 使用gpload导入数据

Greenplum的gpload应用程序使用可读外部表和并行文件系统gpfdist或gpfdists导入数据。它并行处理基于文件创建的外部表，允许用户在单一配置文件中配置数据格式、外部表定义，以及gpfdist或gpfdists的设置。

gpload需要依赖某些Greenplum安装中的文件，如gpfdist和Python，还要能通过网络访问所有Segment主机。gpload的控制文件是一个YAML（Yet Another Markup Language）格式的文件，在其中指定Greenplum连接信息、gpfdist配置信息、外部表选项、数据格式等。下面是一个名为my_load.yml的控制文件内容：

代码语言：javascript复制
---
VERSION: 1.0.0.1
DATABASE: dw
USER: gpadmin
HOST: mdw
PORT: 5432
GPLOAD:
   INPUT:
    - SOURCE:
         LOCAL_HOSTNAME:
           - smdw
         PORT: 8081
         FILE:
           - /home/gpadmin/staging/*.txt
    - COLUMNS:
           - name: text
           - date: date
           - amount: float4
           - category: text
           - desc1: text
    - FORMAT: text
    - DELIMITER: '|'
    - ERROR_LIMIT: 25
   OUTPUT:
    - TABLE: t1
    - MODE: INSERT
   SQL:
    - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
    - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"

gpload控制文件使用YAML 1.1文档格式，为了定义数据装载的各种步骤，它定义了自己的schema。控制文件必须是一个有效的YAML文档。gpload程序按顺序处理控制文件文档，并使用空格识别文档中各段之间的层次关系，因此空格的使用非常重要。不要使用TAB符代替空格，YAML文档中不要出现TAB符。

LOCAL_HOSTNAME指定运行gpload的本地主机名或IP地址。如果机器配置了多块网卡，可以为每块网卡指定一个主机名，允许同时使用多块网卡传输数据。比如smdw上配置了两块网卡，可以如下配置LOCAL_HOSTNAME：

代码语言：javascript复制
LOCAL_HOSTNAME:  
 - smdw-1  
 - smdw-2

下面看一个gpload示例。我们先在smdw上准备本地文件数据如下：

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~/staging]$cat a.txt 
aaa|2022-01-01|100.1|aaa|aaa
bbb|2022-01-02|100.2|bbb|bbb
[gpadmin@vvml-z2-greenplum~/staging]$cat b.txt 
aaa|2022-01-03|200.1|aaa|aaa  
bbb|2022-01-04|200.2|bbb|bbb

然后建立目标表和audit表：

代码语言：javascript复制
c dw gpadmin
create table t1 ( name text, date date, amount float4, category text, desc1 text );  
create table audit(flag varchar(10),st timestamp);

最后执行gpload：

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~/staging]$gpload -f my_load.yml
2022-01-11 09:29:31|INFO|gpload session started 2022-01-11 09:29:31
2022-01-11 09:29:32|INFO|setting schema 'rds' for table 't1'
2022-01-11 09:29:32|INFO|started gpfdist -p 8081 -P 8082 -f "/home/gpadmin/staging/*.txt" -t 30
2022-01-11 09:29:32|INFO|running time: 0.45 seconds
2022-01-11 09:29:32|INFO|rows Inserted          = 4
2022-01-11 09:29:32|INFO|rows Updated           = 0
2022-01-11 09:29:32|INFO|data formatting errors = 0
2022-01-11 09:29:32|INFO|gpload succeeded
[gpadmin@vvml-z2-greenplum~/staging]$

查询目标表和audit表确认执行结果：

代码语言：javascript复制
dw=# select * from t1;
 name |    date    | amount | category | desc1 
------ ------------ -------- ---------- -------
 bbb  | 2022-01-02 |  100.2 | bbb      | bbb
 bbb  | 2022-01-04 |  200.2 | bbb      | bbb
 aaa  | 2022-01-01 |  100.1 | aaa      | aaa
 aaa  | 2022-01-03 |  200.1 | aaa      | aaa  
(4 rows)

dw=# select * from audit;
 flag  |             st             
------- ----------------------------
 start | 2022-01-11 09:29:32.053015
 end   | 2022-01-11 09:29:32.246904
(2 rows)

9.2.6 使用COPY互拷数据

COPY是Greenplum的SQL命令，它在外部文件和表之间互拷数据。COPY FROM命令将本地文件追加到数据表中，而COPY TO命令将数据表中的数据覆盖写入本地文件。COPY命令是非并行的，数据在Master实例上以单进程处理，因此只推荐对非常小的数据文件使用COPY命令。本地文件必须在Master主机上，缺省的文件格式是逗号分隔的CSV文本文件。下面是一个用copy导入数据的例子。

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~/staging]$scp -r /home/gpadmin/staging/ 114.112.77.198:/home/gpadmin/
[gpadmin@vvml-z2-greenplum~/staging]$psql -h mdw -d dw
psql (9.4.24)
Type "help" for help.

dw=# create table t2 (like t1);  
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
dw=# copy t2 from '/home/gpadmin/staging/a.txt' with delimiter '|';
COPY 2
dw=# select * from t2;
 name |    date    | amount | category | desc1 
------ ------------ -------- ---------- -------
 bbb  | 2022-01-02 |  100.2 | bbb      | bbb
 aaa  | 2022-01-01 |  100.1 | aaa      | aaa
(2 rows)

copy命令中可以指定文件格式、分隔符、字符集等属性：

代码语言：javascript复制
copy test from '/tmp/file0' with (format csv, delimiter '|', encoding 'latin1');

下面的例子将表数据导出到Master的本地文件中。如果文件不存在则建立文件，否则会用导出数据覆盖文件原来的内容。

代码语言：javascript复制
dw=# copy (select * from t2) to '/home/gpadmin/staging/c.txt' with delimiter '|';
COPY 2

[gpadmin@vvml-z2-greenplum~/staging]$ssh 114.112.77.198 cat /home/gpadmin/staging/c.txt
bbb|2022-01-02|100.2|bbb|bbb
aaa|2022-01-01|100.1|aaa|aaa
[gpadmin@vvml-z2-greenplum~/staging]$

Greenplum利用客户端与Master服务器之间的连接，能从STDIN或STDOUT拷贝数据，通过管道可以实现类似于流复制的功能，例如：

代码语言：javascript复制
psql -h src -d srcdb -c 'copy test to stdout' | psql -h des -d desdb -c 'copy test from stdin'

缺省时，COPY在遇到第一个错误就会停止运行。如果数据含有错误，操作失败，没有数据被装载。如果以单行错误隔离模式运行COPY，将跳过含有错误格式的行，装载具有正确格式的行。如果数据违反了NOT NULL或CHECK等约束条件，操作仍然是‘all-or-nothing’输入模式，整个操作失败，没有数据被装载。

修改a.txt文件，制造一行格式错误的数据。

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~/staging]$cat a.txt 
aaa,2022-01-01,100.1,aaa,aaa
bbb|2022-01-02|100.2|bbb|bbb

执行copy命令。与导出不同，导入会向表中追加数据。

代码语言：javascript复制
dw=# copy t2 from '/home/gpadmin/staging/a.txt'  
dw-# with delimiter '|' log errors segment reject limit 5 rows;  
NOTICE:  found 1 data formatting errors (1 or more input rows), rejected related input data
COPY 2
dw=# select * from t2;
 name |    date    | amount | category | desc1 
------ ------------ -------- ---------- -------
 bbb  | 2022-01-02 |  100.2 | bbb      | bbb
 bbb  | 2022-01-02 |  100.2 | bbb      | bbb
 aaa  | 2022-01-01 |  100.1 | aaa      | aaa
(3 rows)

dw=# select gp_read_error_log('t2');
                                                 gp_read_error_log                                                  
--------------------------------------------------------------------------------------------------------------------
 ("2022-01-11 09:47:30.923811 08",t2,<stdin>,1,,"missing data for column ""date""","aaa,2022-01-01,100.1,aaa,aaa",)
(1 row)

再次修改文件，将name字段对应的数据置空，因为该字段定义为NOT NULL，所以违反约束，没有数据被拷贝，也不会更新错误日志。

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~/staging]$cat a.txt 
|2022-01-01|100.1|aaa|aaa
bbb|2022-01-02|100.2|bbb|bbb

dw=# truncate table t2; 
TRUNCATE TABLE
dw=# alter table t2 alter column name set not null;
ALTER TABLE
dw=# copy t2 from '/home/gpadmin/staging/a.txt'  
dw-# with (FORMAT CSV, delimiter E'|', FORCE_NULL(name)) 
dw-# log errors segment reject limit 5 rows;
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (null, 2022-01-01, 100.1, aaa, aaa).
CONTEXT:  COPY t2, line 1: "|2022-01-01|100.1|aaa|aaa"
dw=# select * from t2;
 name | date | amount | category | desc1 
------ ------ -------- ---------- -------
(0 rows)

dw=# select gp_read_error_log('t2');
                                                 gp_read_error_log                                                  
--------------------------------------------------------------------------------------------------------------------
 ("2022-01-11 09:47:30.923811 08",t2,<stdin>,1,,"missing data for column ""date""","aaa,2022-01-01,100.1,aaa,aaa",)
(1 row)

copy时可能出现如下错误：

代码语言：javascript复制
ERROR:  invalid byte sequence for encoding "UTF8": 0x00

这是一个已知错误，解决方法是先替换掉文件中的字符串再执行copy：

代码语言：javascript复制
# cat命令
cat audit_obj_detail_article.txt | sed 's/\0//g' > audit_obj_detail_article.txt.1
# 或者perl命令，更快的方法
perl -p -i -e "s/x5c0//g" audit_obj_detail_article.txt

与SQL命令copy读取Master上的文件不同，psql的命令copy从客户端本地读取文件：

代码语言：javascript复制
copy test from '/tmp/file0' delimiter '|';

9.2.7 导出数据

一个可写外部表允许用户从其他数据库表选择数据行并输出到文件、命名管道或应用。如前面的example1和example2所示，可以定义基于gpfdist或Web的可写外部表。对于使用gpfdist协议的外部表，Segment将它们的数据发送给gpfdist，gpfdist将数据写入命名文件中。gpfdist必须运行在Segment能够在网络上访问的主机上。gpfdist指向一个输出主机上的文件位置，将从Segment接收到的数据写入文件。一个可写web外部表的数据作为数据流发送给应用，例如，从Greenplum导出数据并发送给一个连接其他数据库的应用或向别处装载数据的ETL工具。可写web外部表使用EXECUTE子句指定一个运行在Segment主机上的shell命令、脚本或应用，接收输入数据流。

可以选择为可写外部表声明分布策略。缺省时，可写外部表使用随机分布。如果要导出的源表是哈希分布的，为外部表定义相同的分布键列会提升数据导出性能，因为这消除了数据行在内部互联网络上的移动。如果导出一个特定表的数据，可以使用LIKE子句拷贝源表的列定义与分布策略。

代码语言：javascript复制
dw=# create writable external table unload_expenses ( like t1 )  
dw-# location ('gpfdist://mdw:8081/data/expenses1.out', 'gpfdist://smdw:8081/data/expenses2.out')  
dw-# format 'text' (delimiter ',');  
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE EXTERNAL TABLE

可写外部表只允许INSERT操作。如果执行导出的用户不是外部表的属主或超级用户，必须授予对外部表的INSERT权限。

代码语言：javascript复制
grant insert on unload_expenses TO admin;

导出数据并查看输出文件。

代码语言：javascript复制
dw=# insert into unload_expenses select * from t1; 
INSERT 0 4

# mdw上的输出文件
[gpadmin@vvml-z2-greenplum~]$cat /data/expenses1.out
bbb,2022-01-02,100.2,bbb,bbb
bbb,2022-01-04,200.2,bbb,bbb
[gpadmin@vvml-z2-greenplum~/staging]$

# smdw上的输出文件
[gpadmin@vvml-z2-greenplum~]$cat /data/expenses2.out
aaa,2022-01-01,100.1,aaa,aaa
aaa,2022-01-03,200.1,aaa,aaa  
[gpadmin@vvml-z2-greenplum~]$

如example2所示，也可以定义一个可写的外部web表，发送数据行到脚本或应用。脚本文件必须接收输入流，而且必须存在于所有Segment的主机的相同位置上，并可以被gpadmin用户执行。Greenplum集群中的所有Segment都执行脚本，无论Segment是否有需要处理的输出行。

允许外部表执行操作系统命令或脚本会带来相应的安全风险。为了在可写外部web表定义中禁用EXECUTE，可在Master的postgresql.conf文件中设置gp_external_enable_exec服务器配置参数为off：

代码语言：javascript复制
gp_external_enable_exec = off

正如前面说明COPY命令时所看到的，COPY TO命令也可以用来导出数据。它使用Master主机上的单一进程，将表中数据拷贝到Master主机上的一个文件（或标准输入）中。COPY TO命令重写整个文件，而不是追加记录。

9.2.8 格式化数据文件

使用Greenplum工具导入或导出数据时，必须指定数据的格式。CREATE EXTERNAL TABLE、gpload和COPY都包含指定数据格式的子句。数据可以是固定分隔符的文本或逗号分隔值（CSV）格式。外部数据必须是Greenplum可以正确读取的格式。

1. 行分隔符 Greenplum需要数据行以换行符（LF，Line feed，ASCII值0x0A）、回车符（CR，Carriage return，ASCII值0x0D）或回车换行符（CR LF，0x0D 0x0A）作为行分隔符。LF是类UNIX操作系统中标准的换行符。而Windows或Mac OS X使用CR或CR LF。所有这些表示一个新行的特殊符号都被Greenplum作为行分隔符所支持。

2. 列分隔符 文本文件和CSV文件缺省的列分隔符分别是TAB（ASCII值为0x09）和逗号（ASCII值为0x2C）。在定义数据格式时，可以在CREATE EXTERNAL TABLE或COPY命令的DELIMITER子句，或者gpload的控制文件中，声明一个单字符作为列分隔符。分隔符必须出现在字段值之间，不要在一行的开头或结尾放置分隔符。如使用管道符（|）作为列分隔符：

代码语言：javascript复制
data value 1|data value 2|data value 3

下面的建表命令显示以管道符作为列分隔符：

代码语言：javascript复制
create external table ext_table (name text, date date)  
location ('gpfdist://host:port/filename.txt)  
format 'text' (delimiter '|');

3. 表示空值 空值（NULL）表示一列中的未知数据。可以指定数据文件中的一个字符串表示空值。文本文件中表示空值的缺省字符串为N，CSV文件中表示空值的缺省字符串为不带引号的空串（两个连续的逗号）。定义数据格式时，可以在CREATE EXTERNAL TABLE、COPY命令的NULL子句，或者gpload的控制文件中，声明其他字符串表示空值。例如，若不想区分空值与空串，就可以指定空串表示NULL。使用Greenplum导出工具时，任何与声明代表NULL的字符串相匹配的数据项都被认为是空值。

4. 转义 列分隔符与行分隔符在数据文件中具有特殊含义。如果实际数据中也含有这个符号，必须对这些符号进行转义，以使Greenplum将它们作为普通数据而不是列或行的分隔符。文本文件缺省的转义符为一个反斜杠（），CSV文件缺省的转义符为一个双引号（"）。

（1）文本文件转义 可以在CREATE EXTERNAL TABLE、COPY的ESCAPE子句，或者gpload的控制文件中指定转义符。假设有以下三个字段的数据：

代码语言：javascript复制
backslash = 
vertical bar = |
exclamation point = !

指定管道符（|）为列分隔符，反斜杠（）为转义符。则对应的数据行格式如下：

代码语言：javascript复制
backslash = \ | vertical bar = | | exclamation point = !

可以对八进制或十六进制序列应用转义符。在装载进Greenplum时，转义后的值就是八进制或十六进制的ASCII码所表示的字符。例如，取址符（&）可以使用十六进制的（x26）或八进制的（46）表示。

如果要在CREATE EXTERNAL TABLE、COPY命令的ESCAPE子句，或者gpload的控制文件中禁用转义，可如下设置：

代码语言：javascript复制
ESCAPE 'OFF'

该设置常用于输入数据中包含很多反斜杠（如Web日志数据）的情况。

（2）CSV文件转义 可以在CREATE EXTERNAL TABLE、COPY的ESCAPE子句，或者gpload的控制文件中指定转义符。假设有以下三个字段的数据：

代码语言：javascript复制
Free trip to A,B
5.89
Special rate "1.79"

指定逗号（,）为列分隔符，一个双引号（"）为转义符。则数据行格式如下：

代码语言：javascript复制
"Free trip to A,B","5.89","Special rate ""1.79"""

将字段值置于双引号中能保留字符串中头尾的空格。

5. 字符编码 在将一个Windows操作系统上生成的数据文件装载到Greenplum前，先使用dos2unix系统命令去除只有Windows使用的字符，如删除文件中的CR（'x0D'）。

9.3 性能优化

Greenplum为查询动态分配资源，数据所在的位置、查询所使用的段数量、集群的总体健康状况等因素都会影响查询性能。

9.3.1 常用优化手段

当进行了适当的服务器参数设置后，Greenplum内部系统会自动实施某些优化，理解它们对于开发高性能应用大有裨益。对用户来说，表设计与SQL语句的写法对性能的影响很大，然而这些技术对大部分数据库系统来说是通用的，如规范化设计、索引设计、连接时驱动表的选择、利用提示影响优化器等等。有很多这方面的资料，本篇不展开讨论这些内容。

Greenplum数据库会动态消除不相关的分区，并且为执行计划中不同的算子优化内存分配。这些增强使得查询扫描更少的数据，内存得到更优化的分配，加快查询，提升并发支持能力。

1. 动态分区消除 Greenplum 有静态与动态两种分区消除。静态消除发生在编译期间，在执行计划生成的时候，已经知道哪些分区会被使用。而动态消除发生在运行时，也就是说在运行的时候才会知道哪些分区会被用到。例如，WHERE字句里面包含一个函数或者子查询用于返回分区键的值。查询过滤条件的值可用于动态分区消除时，查询处理速度将得到提升。该特性由服务器配置参数gp_dynamic_partition_pruning控制，缺省是开启的。

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~]$gpconfig -s gp_dynamic_partition_pruning
Values on all segments are consistent
GUC          : gp_dynamic_partition_pruning
Master  value: on
Segment value: on

2. 内存优化 Greenplum针对查询中的不同算子分配最佳内存，为非内存密集型算子分配固定尺寸的内存，剩余的内存分配给内存密集型算子，并且在查询处理的各个阶段，及时释放已完成算子的可释放内存，并重新分配给后续算子。

3. 自动终止资源失控的查询 当服务器中所有查询占用的内存超过一定阈值，Greenplum可以终止某些查询。Greenplum会计算得到一个为Segment分配的内存限额，再结合可配的系统参数计算阈值。阈值计算公式为：memory threshold = gp_vmem_protect_limit * runaway_detector_activation_percent。

gp_vmem_protect_limit参数设置在开启资源队列的情况下，每个Segment的最大内存使用量，缺省为8192M。runaway_detector_activation_percent参数设置触发自动终止查询的内存限额百分比，缺省值为90，即当内存使用量达到gp_vmem_protect_limit的90%，数据库将开始终止查询。从内存消耗量最大的查询开始，一直到内存使用量低于指定的百分比为止。如果runaway_detector_activation_percent设置为100，将禁用内存检测和自动查询终止。

当一个查询没有达到希望的执行速度时，应该从以下方面检查造成查询缓慢的可能原因。

  • 检查集群健康状况，如是否有Segment宕机，是否存在磁盘损坏等。
  • 检查表的统计信息，确认是否需要执行分析。
  • 检查查询的执行计划确定瓶颈。对于某些算子如Hash Join，如果没有足够的内存，该操作会使用溢出文件（spill files）。相对于完全在内存中执行的操作，磁盘溢出文件会慢得多。
  • 检查资源队列状态。pg_resqueue系统目录表保存资源队列信息。还可以查询pg_resqueue_status视图检查资源队列的运行时状态。

9.3.2 控制溢出文件

Greenplum在执行SQL时，如果分配的内存不足，会将文件溢出到磁盘上，通常称为workfile。这是Greenplum内的标准称呼，因为相关的参数、视图、函数的名字都是以workfile来命名的。gp_workfile_limit_files_per_query参数用于控制一个查询使用的最大溢出文件数量，缺省值为100000，可以满足大多数场景，一般不需要修改这个参数。

如果溢出文件的数量超过该参数的值，数据库会返回一个错误：

代码语言：javascript复制
ERROR: number of workfiles per query limit exceeded

有时数据库可能产生大量溢出文件：

  • 存在严重的数据倾斜。关于数据倾斜的检查，参见后面“检查数据分布倾斜”。
  • 为查询分配的内存太少。可以通过max_statement_mem和statement_mem参数来控制查询可用的最大内存，或者通过资源组或资源队列来控制。

可以通过修改查询语句优化SQL以降低内存需求，更改数据分布避免数据倾斜，或修改内存配置来成功运行查询命令。gp_toolkit.gp_workfile_*视图用来查看溢出文件信息，这些视图对于查询性能问题的排查非常有帮助。

9.3.3 查询剖析

遇到性能不良的查询时，最常用的调查手段就是查看执行计划。Greenplum选择与每个查询相匹配的查询计划，查询计划定义了Greenplum在并行环境中如何运行查询。如果SQL本身的逻辑非常糟糕，可能数据库无论如何也无法产生好的执行计划，例如大表之间的非等值关联。

查询优化器根据数据库系统维护的统计信息选择成本最低的查询计划。成本以磁盘I/O作为考量，以查询需要读取的磁盘页数为测量单位。优化器的目标就是制定最小化执行成本的查询计划，但往往生成符合预期的执行计划才是最优结果。

和其他SQL数据库一样，Greenplum也是用EXPLAIN命令查看一个给定查询的执行计划。EXPLAIN会显示查询优化器估计出的计划成本。EXPLAIN ANALYZE命令会实际执行查询语句，它除了显示估算的查询成本，还会显示实际执行时间，从这些信息可以分析优化器所做的估算与实际之间的接近程度。

Greenplum中老的PostgreSQL优化器与GPORCA并存，缺省的查询优化器为GPORCA。Greenplum尽可能使用GPORCA生成执行计划。GPORCA和老优化器的EXPLAIN输出不同。

1. 读取EXPLAIN的输出 执行计划是一棵有很多个算子构成的树，其中每个算子是一个独立的计算操作，例如表扫描、关联、聚合或排序等。从下到上来看执行计划，每个算子的计算结果作为上面一个算子的输入。

执行计划最底部的算子，往往是表扫描算子：Seq Scan、Index Scan、Bitmap Index Scan。如果查询有关联、聚合或者排序，在扫描算子之上会有其他算子来执行这些操作。最顶端的算子往往是Greenplum的移动算子（重分布、广播或汇总）。移动算子负责将处理过程中产生记录在Segment之间移动。

EXPLAIN的输出中每个算子都有一行，其显示基本的算子类型和该算子的成本估算：

  • cost：访问的磁盘页数量，就是说，1.0等于一个连续的磁盘页操作。第一个值是获得第一条记录的成本，第二个值是获得所有记录的总成本。总成本假设会检索所有的记录，但有时并不会真的检索所有记录，比如使用了LIMIT子句，可能不会真的检索所有记录。例如：
代码语言：javascript复制
=# EXPLAIN SELECT * FROM pg_class;
Seq Scan on pg_class (cost=0.00..17.19 rows=1019 width=265)
Optimizer: Postgres query optimizer
=# EXPLAIN SELECT * FROM pg_class LIMIT 1;
Limit (cost=0.00..0.02 rows=1 width=265)
-> Seq Scan on pg_class (cost=0.00..17.19 rows=1019 width=265)
Optimizer: Postgres query optimizer

注意，GPORCA优化器和PostgreSQL优化器生成的执行计划中，cost不具有可比性。这两个优化器，使用不同的成本估算模型和算法来评估执行计划的成本。对比两个优化器之间的cost值是没有实际意义的。

另外，对于任意优化器生成的执行计划的cost值来说，只对当前的查询和当前的统计信息有意义，不同的语句会生成不同cost的执行计划。即便如此，如果看到一个数量级非常大的cost，可能执行计划的确是有问题的。有时cost的值会严重失真，例如统计信息失真的情况下，这时的cost将变的不再真实。

  • rows：该算子输出的记录数，值可能与真实数量有较大的出入，其会反映WHERE子句的条件对记录的过滤。顶端算子评估的数量，在理想状态下与真实返回的、更新的或者删除的数据量接近。
  • width：该算子产生的每条记录的尺寸（字节数）。这里会去除掉表中没有被涉及到的字段的尺寸，因此不一定能真实体现计算的数据每条记录的尺寸。对于列存表，这样做是准确的，但对于行存表，真实处理时，行存表的一条记录是一个tuple，不会因为只使用了少量字段而把tuple拆解。

一个上层算子的cost包含其所有子算子的cost，最顶端算子的cost包含了整个执行计划的总cost，这就是优化器要试图减小的数字。另外，cost仅仅反映了优化器所在意的代价。除了这些，cost不包含结果集传输到客户端的开销或耗时的预估。

要说明如何阅读EXPLAIN得到的执行计划，参考一下下面这个简单的例子：

代码语言：javascript复制
=# EXPLAIN SELECT * FROM names WHERE name = 'Joelle';
QUERY PLAN
------------------------------------------------------------
Gather Motion 2:1 (slice1) (cost=0.00..20.88 rows=1 width=13)
-> Seq Scan on 'names' (cost=0.00..20.88 rows=1 width=13)
Filter: name::text ~~ 'Joelle'::text

从下向上查看这个执行计划，执行计划从顺序扫描names表开始。WHERE子句被用作一个filter条件，这意味着，扫描操作将根据条件检查扫描的每一行，并只输出符合条件的记录。

扫描算子的输出传递给汇总移动算子。在Greenplum中，汇总移动是Segment向Master发送记录的操作，该场景下，有2个Segment向1个Master发送（2:1）记录。每个算子都在执行计划的一个Slice中。在Greenplum中，一个执行计划可能会被分为多个Slice，以确保计算任务可以在Segment之间并行工作，往往不同的Slice可能会被Motion算子分开。

评估的开始成本为00.00（无cost）且总成本为20.88个磁盘页。优化器评估这个查询将返回一行记录，单条记录的尺寸为13个字节。

2. 读取EXPLAIN ANALYZE的输出 EXPLAIN ANALYZE会真正的执行语句，而不仅仅是生成执行计划。EXPLAIN ANALYZE依然会输出优化器的评估cost，同时会输出真实执行的cost。据此，可以评估优化器生成的执行计划与真实的执行情况是否接近。EXPLAIN ANALYZE还会额外输出如下信息（GPORCA和PostgreSQL优化器会有差异）：

  • 执行该查询总的耗时（以毫秒计）。
  • 执行计划的每个Slice使用的内存，以及分配给该查询的总的内存量。
  • 参与一个算子计算的Segment数量，只统计有记录返回的Segment。
  • 算子中输出记录数最多的Segment输出的记录数。如果有多个Segment输出的记录数相同，则显示耗时最长的Segment的信息。
  • 算子的内存使用情况，对于工作内存不足的算子，将显示性能最低的Segment的溢出文件的数量。例如：
代码语言：javascript复制
# PostgreSQL优化器
Extra Text: (seg0) . . . ; 100038 spill groups.
. . .
* (slice2) Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max (seg0). Work_mem: 925K bytes max, 6721K bytes wanted.
Memory used: 2048kB
Memory wanted: 13740kB

# GPORCA优化器
Sort Method: external merge Disk: 1664kB
. . .
* (slice2) Executor memory: 2256K bytes avg x 2 workers, 2256K bytes max (seg0). Work_mem: 2105K bytes max, 5216K bytes wanted.
Memory used: 2048kB
Memory wanted: 5615kB
  • 算子中输出记录数最多的Segment，输出第一条记录所用的时间（以毫秒计），输出最后一条记录所用的时间。如果两个时间相同，开始时间会被省略。随着执行计划从下向上被执行，时间可能有重叠。

我们使用一个相对复杂一点的查询来说明。先看一下GPORCA优化器的输出：

代码语言：javascript复制
EXPLAIN ANALYZE
SELECT customer_id,count(*) FROM sales GROUP BY 1;
Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..476.54 rows=85709 width=12) (actual time=376.394..452.434 rows=99351 loops=1)
-> HashAggregate (cost=0.00..471.92 rows=42855 width=12) (actual time=377.094..421.520 rows=49765 loops=1)
Group Key: customer_id
Extra Text: (seg0) 49765 groups total in 32 batches; 1 overflows; 169919 spill groups.
(seg0) Hash chain length 2.0 avg, 16 max, using 42789 of 72704 buckets; total 8 expansions.
-> Redistribute Motion 2:2 (slice1; segments: 2) (cost=0.00..441.24 rows=250500 width=4) (actual time=2.788..171.396 rows=250602 loops=1)
Hash Key: customer_id
-> Seq Scan on sales (cost=0.00..436.24 rows=250500 width=4) (actual time=0.019..46.502 rows=250755 loops=1)
Planning time: 31.606 ms
(slice0) Executor memory: 87K bytes.
(slice1) Executor memory: 58K bytes avg x 2 workers, 58K bytes max (seg0).
* (slice2) Executor memory: 3106K bytes avg x 2 workers, 3106K bytes max (seg0). Work_mem: 1849K bytes max, 4737K bytes wanted.
Memory used: 2048kB
Memory wanted: 5036kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 466.982 ms

从下往上看，将看到每个算子的额外信息。花费的总时间为466.982毫秒。顺序扫描表的操作，输出记录数最多的Segment，执行计划评估的记录数是250250条，实际输出的是250755条，输出第一条的用时是0.019毫秒，输出最后一条的用时是46.502毫秒。重分布算子，输出第一条的用时是2.788毫秒，输出最后一条的用时是171.396毫秒。总的内存使用量是2048kB，而wanted是5036kB，在HASH聚合算子中因为内存不足，使用了spill溢出文件。

下面在再看一下PostgreSQL优化器的输出：

代码语言：javascript复制
EXPLAIN ANALYZE
SELECT customer_id,count(*) FROM sales GROUP BY 1;
Gather Motion 2:1 (slice2; segments: 2) (cost=11066.78..11923.86 rows=85708 width=12) (actual time=567.520..653.070 rows=99351 loops=1)
-> HashAggregate (cost=11066.78..11923.86 rows=42854 width=12) (actual time=566.833..619.733 rows=49765 loops=1)
Group Key: sales.customer_id
Extra Text: (seg0) 49765 groups total in 32 batches; 1 overflows; 218258 spill groups.
(seg0) Hash chain length 1.7 avg, 12 max, using 38835 of 69632 buckets; total 2 expansions.
-> Redistribute Motion 2:2 (slice1; segments: 2) (cost=8067.00..9781.16 rows=42854 width=12) (actual time=12.020..363.668 rows=227461 loops=1)
Hash Key: sales.customer_id
-> HashAggregate (cost=8067.00..8067.00 rows=42854 width=12) (actual time=10.862..212.384 rows=227625 loops=1)
Group Key: sales.customer_id
Extra Text: (seg0) Hash chain length 4.4 avg, 15 max, using 52054 of 53248 buckets; total 2 expansions.
-> Seq Scan on sales (cost=0.00..5562.00 rows=250500 width=4) (actual time=0.018..66.742 rows=250755 loops=1)
Planning time: 0.113 ms
(slice0) Executor memory: 87K bytes.
(slice1) Executor memory: 1076K bytes avg x 2 workers, 1076K bytes max (seg0).
* (slice2) Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max (seg0). Work_mem: 925K bytes max, 4673K bytes wanted.
Memory used: 2048kB
Memory wanted: 9644kB
Optimizer: Postgres query optimizer
Execution time: 666.468 ms

这里不再详细解读PostgreSQL优化器的输出。需要注意，不同算子的耗时是有交叉和重叠的，这是因为，GP的执行器是流水线操作，下一步操作并不一定需要等待上一步完全执行完才开始执行。有些操作需要等待上一步的完成，例如Hash Join必须要等Hash操作完成才能开始。

3. 分析查询计划中的问题 若一个查询表现出很差的性能，查看执行计划可能会有助于找到问题所在。下面是一些需要查看的事项：

  • 执行计划中是否有某些算子耗时特别长？找到占据大部分查询时间的算子。例如，如果一个索引扫描比预期的时间长，可能该索引已经过期，需要考虑重建索引。还可尝试使用enable_之类的参数(对于PostgreSQL优化器来说，这些参数很重要)，检查是否可以强制优化器选择不同的执行计划，这些参数可以设置特定的算子为开启或关闭状态。
  • 优化器的评估是否接近实际情况？执行EXPLAIN ANALYZE查看优化器评估的记录数与真实运行时的记录数是否一致。如果差异很大，可能需要在相关表的某些字段上收集统计信息。不过，如果SQL本身已经完全无法运行出结果，EXPLAIN ANALYZE将无法进行，该方法仅对运行慢的SQL有效。
  • 选择性强的条件是否较早出现？选择性越强的条件应该越早被使用，从而使得在计划树中向上传递的记录越少。如果执行计划在选择性评估方面没有对查询条件作出正确的判断，可能需要在相关表的某些字段上收集统计信息。不过，收集了准确的统计信息仍可能无法使得选择性的评估更准确，因为Greenplum的选择性评估是基于MCV模型的，没有被统计信息记录的值，需要通过线性插值算法得到其存在概率，这种评估本身误差就较大，当需要同时对多个条件进行评估时，这种误差会呈几何倍数放大。有时，将太过复杂的SQL进行必要的拆解会更有效。
  • 优化器是否选择了最佳的关联顺序？如查询使用多表关联，需要确保优化器选择了选择性最好的关联顺序。那些可以消除大量记录的关联应该尽早的被执行，从而使得在计划树中向上传递的记录快速减少。如果优化器没有选择最佳的关联顺序，可以尝试设置join_collapse_limit=1（GPORCA由optimizer_join_order_threshold参数控制）并在SQL语句中构造特定的关联顺序，从而可以强制优化器选择指定的关联顺序。还可以尝试在相关表的某些字段上收集统计信息。
  • 优化器是否选择性的扫描分区表？如果使用了分区，优化器是否只扫描了查询条件匹配的相关分区（Partitions selected）。
  • 优化器是否恰当的选择了HASH聚合或HASH关联算子？HASH操作通常比其他类型的关联和聚合要快。记录在内存中进行比较和排序比在磁盘上操作要快很多。要使得优化器能选择HASH算子，必须确保有足够的内存来存放记录。可以尝试增加工作内存来提升性能。当缺省的内存配置不充裕时，如果已经足够，再增加不会提升性能，所以不要盲目的以为增加内存就一定可以提升性能，内存只是一个通常不太会出问题的因素。如果可能，执行EXPLAIN ANALYZE，可以发现哪些算子会用到溢出文件，使用了多少内存，需要多少内存。例如：
代码语言：javascript复制
. . .
Extra Text: (seg0) 49765 groups total in 32 batches; 1 overflows; 218258 spill groups.
. . .
* (slice2) Executor memory: 2114K bytes avg x 2 workers, 2114K bytes max (seg0). Work_mem: 925K bytes max, 4673K bytes wanted.
Memory used: 2048kB
Memory wanted: 9644kB

需要注意的是wanted信息只是一个提示，是基于溢出文件尺寸来评估的，可能与实际需要的内存有出入。

9.4 例行监控

9.4.1 检查系统状态

gpstate工具主要用于显示Greenplum数据库运行状态，详细配置等信息，如Segment节点是否宕机，Master及Segment配置信息，系统使用端口，Primary实例与Mirror实例的匹配关系等。该命令默认列出数据库运行状态汇总信息，常用于日常巡检。

1. 查看master与segment的状态与配置

代码语言：javascript复制
# 概要信息
gpstate
# 配置详细信息
gpstate -s

2. 查看mirror段的状态与配置

代码语言：javascript复制
# mirror状态
gpstate -m
# primary与mirror的映射
gpstate -c
# standby master状态
gpstate -f

3. 查看当前会话信息和锁等待 可以使用psql的set命令实现类似于MySQL的show processlist的功能，显示当前会话信息。例如，在gpadmin操作系统用户主目录下创建.psqlrc文件，内容容下：

代码语言：javascript复制
timing on
pset pager off
set active_session 'select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),waiting,state,query from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query_start desc;'
set lock_wait 'select * from pg_catalog.gp_dist_wait_status();'

在psql客户端中执行命令时，会在每条命令输出最后加上命令执行时间，去掉输出中的分页显示，并可通过active_session和lock_wait变量分别查看当前会话和锁等待信息：

代码语言：javascript复制
[gpadmin@vvml-z2-greenplum~]$psql -d dw -h mdw 
Timing is on.
Pager usage is off.
psql (9.4.24)
Type "help" for help.

dw=# select * from t1;
 name |    date    | amount | category | desc1 
------ ------------ -------- ---------- -------
 bbb  | 2022-01-02 |  100.2 | bbb      | bbb
 bbb  | 2022-01-04 |  200.2 | bbb      | bbb
 aaa  | 2022-01-01 |  100.1 | aaa      | aaa
 aaa  | 2022-01-03 |  200.1 | aaa      | aaa  
(4 rows)

Time: 52.562 ms
dw=# :active_session
 pid | usename | datname | application_name | client_addr | age | waiting | state | query 
----- --------- --------- ------------------ ------------- ----- --------- ------- -------
(0 rows)

Time: 10.633 ms
dw=# :lock_wait
 segid | waiter_dxid | holder_dxid | holdTillEndXact | waiter_lpid | holder_lpid | waiter_lockmode | waiter_locktype | waiter_sessionid | holder_sessionid 
------- ------------- ------------- ----------------- ------------- ------------- ----------------- ----------------- ------------------ ------------------
(0 rows)

Time: 25.420 ms

4. 终止会话 pg_cancel_backend()和pg_terminate_backend()两个函数用于手工终止会话。pg_cancel_backend()取消会话的当前操作，回滚事务，但不退出会话。pg_terminate_backend()回滚事务，终止并退出会话。例如，要终止pid为1234的查询：

代码语言：javascript复制
select pg_cancel_backend(1234);

还可以为pg_cancel_backend()函数提供一个可选的消息参数，用于通知该查询的用户，告知为何终止了其执行的事务。例如：

代码语言：javascript复制
select pg_cancel_backend(1234,'因系统维护暂停使用');

执行该事务的用户会收到如下信息：

代码语言：javascript复制
ERROR: canceling statement due to user request: "因系统维护暂停使用"

尽量不要使用操作系统的kill命令来终止Greenplum系统的任何进程，而要使用pg_cancel_backend函数或者pg_terminate_backend函数来完成。当然也不是完全不可以用，除非有把握确保不会导致数据库损坏。通过kill -9或者kill -11可能会导致数据库崩溃，且无法记录异常日志，以至于无法进行RCA(root cause analysis)。另外，kill -9或者kill -11即便没有导致数据库宕机，也会导致所有连接中断，这个副作用是必然会发生的。

5. 查看系统的变更信息 gp_configuration_history表记录系统的变更信息，包括错误检查及错误恢复操作，例如，添加一个新的主实例及其镜像实例，系统就会将该事件记录到gp_configuration_history表，因此，该表所记录的事件信息，有利于Greenplum技术支持人员对系统的故障排查。

查询Greenplum数据库系统具体实例宕机记录语句如下所示，可根据具体时间需求限定查询条件：

代码语言：javascript复制
select gp_configuration_history.* , gp_segment_configuration.Content
  from gp_configuration_history, gp_segment_configuration
 where gp_configuration_history. dbid = gp_segment_configuration.dbid;

9.4.2 检查磁盘空间使用

1. 查看系统空间使用率

代码语言：javascript复制
df -h

Greenplum集群中任何主机的磁盘空间使用率不要超过70%。

2. 查看Segment剩余空间（单位KB）

代码语言：javascript复制
select * from gp_toolkit.gp_disk_free order by dfsegment;

3. 检查分布式数据库和表的大小

代码语言：javascript复制
-- 数据库使用空间（GB）
select sodddatname,sodddatsize/1024/1024/1024 GB from gp_toolkit.gp_size_of_database order by sodddatname;

c dw gpadmin
-- 表使用空间（MB）
select relname as name, sotdsize/1024/1024 as size, sotdtoastsize/1024/1024 as toast, sotdadditionalsize/1024/1024 as other 
  from gp_toolkit.gp_size_of_table_disk as sotd, pg_class 
 where sotd.sotdoid=pg_class.oid order by size desc;
-- 索引使用空间（MB）
select relname as indexname, soisize/1024/1024 as soisize
  from pg_class, gp_toolkit.gp_size_of_index
 where pg_class.oid=gp_size_of_index.soioid 
   and pg_class.relkind='i'
 order by soisize desc;

9.4.3 检查数据分布倾斜

1. 数据倾斜 Greenplum要求数据在Segment上均匀分布，在MPP Share-Nothing数据库中，对于一个查询来说，所有操作都完成才算完成，那么这个总的耗时就是最慢Segment的耗时。如果有数据的倾斜，处理数据越多的Segment，完成计算所需要的时间就越久。所以，如果所有的Segment处理的数据量相当，那么总体的执行时间就会保持一致，如果个别Segment要处理更多的数据，将可能导致严重的资源消耗且拖慢整体的处理时间。

数据倾斜一般是由于选择了错误的分布键而造成的结果，或者是因为在CREATE TABLE时没有指定分布键而自动以第一个字段作为分布键。通常可能会表现出查询性能差，甚至出现内存不足的报错。数据倾斜会直接影响表扫描的性能，同时也会影响相关的关联查询和分组汇总等计算的性能。

检验数据分布是否均匀非常重要，无论是初次加载数据之后，还是增量数据加载之后。有时，数据量不大时可能不会明显的表现出倾斜，所以需要定期检查倾斜情况。

代码语言：javascript复制
-- 查看表的分布键
d  table_name

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

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

2. 计算倾斜 当数据倾斜到个别Segment时，它往往是Greenplum数据库性能和稳定性的罪魁祸首，而计算倾斜则是更隐蔽的问题，可能造成更严重的影响而且难以被发现和解决。当倾斜发生在关联、排序、聚合等各种算子的计算过程中时，事情就变得十分复杂，这种情况我们称之为计算倾斜。

如果单个Segment出现了故障，有可能与计算倾斜有关。处理计算倾斜时，首先可以看一下溢出文件的情况，如果有计算倾斜但又没有出现溢出文件，可能这种倾斜并不会造成严重的后果。

代码语言：javascript复制
select * from gp_toolkit.gp_workfile_usage_per_segment;
select * from gp_toolkit.gp_workfile_usage_per_query;

通过gp_toolkit.gp_workfile_usage_per_segment视图可以查询每个Segment目前使用的Workfile溢出文件的尺寸和文件数量，以清晰地发现哪些Segment有严重的溢出文件问题。通过gp_toolkit.gp_workfile_usage_per_query视图可以查询每个Query在每个Segment上的Workfile的使用情况。显示的信息包括：数据库名称、进程号、会话ID、command count、用户名、查询语句、SegID、溢出文件尺寸、溢出文件数量。

通常，用这两个视图就可以确定正在发生倾斜的查询。要解决这些问题，往往需要重新优化SQL，例如确认统计信息是否严重失真，如果是，应该尝试更新统计信息，找到执行计划中不合理的算子，通过修改可能的参数来干预执行计划，使用WITH子句来分拆SQL以达到隔离执行计划的目的，使用临时表以强制拆分执行步骤，强制执行计划选择两阶段AGG或者三阶段AGG等。总之，优化的最高目标就是，让数据库生成的执行计划符合预期，最佳的预期需要基于对MPP的分布式理解和对数据的理解。

3. 避免极端倾斜警告 执行执行哈希联接操作的查询时，可能会收到以下警告消息：

代码语言：javascript复制
Extreme skew in the innerside of Hashjoin

当哈希连接运算符的输入发生倾斜时，就会发生这种情况。它不会阻止查询成功完成。可以按照以下步骤来避免执行计划中倾斜。 （1）确保分析了查询使用的所有表，包括临时表。 （2）EXPLAIN ANALYZE查看执行计划并查找以下内容：

  • 如果使用多列筛选器的扫描产生的行数超过估计数，将gp_selectivity_damping_factor服务器配置参数设置为2或更高，然后重新测试查询。
  • 如果在连接相对较小（小于5000行）的单个表时发生倾斜，将gp_segments_for_planner服务器配置参数设置为1，然后重新测试查询。

（3）检查查询中应用的筛选器是否与表的分布键匹配。如果筛选器和分发键相同，考虑使用不同的分发键重新分发一些表。 （4）检查连接键的基数。如果它们的基数较低，尝试使用不同的联接列或表上的附加筛选器重写查询，以减少行数。这些更改可能会改变查询语义。

9.4.4 查看数据库对象的元数据信息

Greenplum数据库在其系统目录中跟踪存储在数据库中的对象（如表、视图、索引等）以及全局对象（如角色和表空间）的各种元数据信息。查看数据库对象元数据信息最简单的方法是使用psql客户端的各种d命令。例如，下面的命令将输出sales_order表的列定义、索引、约束、规则、分布键、分区键、分区子表等信息。

代码语言：javascript复制
d  sales_order

所有数据库对象都有一个对应的d命令，如果加上S表示输出系统对象，加上 表示输出详细信息。?命令显示一个简要psql帮助信息。

可以使用系统视图pg_stat_operations和pg_stat_partition_operations查看对象（如表）上执行的操作。例如，要查看表的创建时间以及上次清空和分析表的时间：

代码语言：javascript复制
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='work_heat_user_operate';

9.4.5 查看会话的内存使用

可以创建并使用session_level_memory_consumpion视图，该视图提供有关在Greenplum数据库上运行查询的会话的当前内存利用率信息，包括会话连接到的数据库、会话当前正在运行的查询以及会话进程所消耗的内存等。

使用以下命令在dw数据库中创建视图：

代码语言：javascript复制
psql -d dw -c "create extension gp_internal_tools;"

session_state.session_level_memory_consumption视图提供有关运行SQL查询的会话的内存消耗和空闲时间信息。当基于资源队列的资源管理处于活动状态时，is_runaway列表示Greenplum数据库是否根据会话查询的vmem内存消耗将会话视为失控会话。服务器配置参数runaway_detector_activation_percent控制Greenplum数据库将会话视为失控会话的条件。当基于资源组的资源管理处于活动状态时，is_runaway、runaway_vmem_mb和runaway_command_cnt列不适用。

表9-3列出了session_state.session_level_memory_consumption视图的字段定义。

列名

数据类型

描述

datname

name

会话连接的数据库名称

sess_id

integer

会话ID

usename

name

会话用户名

query

text

会话当前运行的SQL查询

segid

integer

Segment ID

vmem_mb

integer

MB为单位的会话使用的内存量

is_runaway

boolean

会话在Segment上是否标识为失控

qe_count

integer

会话的查询进程数

active_qe_count

integer

会话的活动查询进程数

dirty_qe_count

integer

尚未释放内存的查询进程数，对于未运行的会话，该值为-1

runaway_vmem_mb

integer

会话标记为失控会话时正在消耗的内存量

runaway_command_cnt

integer

将会话标记为失控会话时会话的命令计数

idle_start

timestamp

上次此会话中的查询进程变为空闲的时间

表9-3 session_state.session_level_memory_consumption视图字段

9.4.6 查看工作文件使用信息

Greenplum数据库管理模式gp_toolkit中包含表示工作文件信息的视图。如果Greenplum没有足够的内存来执行查询，它会在磁盘上创建工作文件。此信息可用于故障排除和优化查询。视图中的信息还可被参考指定配置参数gp_workfile_limit_per_query和gp_workfile_limit_per_segment的值。

gp_toolkit中以下工作文件视图：

  • gp_workfile_entries视图中每个算子一行，该算子当前使用Segment上的磁盘空间用于工作文件。
  • gp_workfile_usage_per_query视图中每个查询一行，该查询使用当前Segment上的磁盘空间用于工作文件。
  • gp_workfile_usage_per_segment视图中每个段包含一行，显示当前段上用于工作文件的磁盘空间总量。

使用Greenplum数据库管理模式gp_toolkit可以查询系统目录、日志文件和操作环境中的系统状态信息。gp_toolkit模式包含若干可用SQL命令访问的视图。所有数据库用户都可以访问gp_toolkit模式，某些对象需要超级用户权限。使用类似于下面的命令可将gp_toolkit架构添加到用户的模式搜索路径中：

代码语言：javascript复制
alter role myrole set search_path to myschema,gp_toolkit;

9.4.7 查看服务器日志文件

了解系统日志文件的位置和内容，并定期查看，而不应该仅在出现问题时才想起它们。表9-4显示了各种Greenplum日志文件的位置。在文件路径中：

  • $GPADMIN_HOME指gpadmin操作系统用户的主目录。
  • $MASTER_DATA_DIRECTORY指Master数据目录。
  • $GPDATA_DIR指Segment数据目录。
  • segprefix是段名前缀。
  • N是Segment实例号。
  • date是YYYYMMDD格式的日期。

路径

描述

$GPADMIN_HOME/gpAdminLogs/*

管理程序默认日志目录

$GPADMIN_HOME/gpAdminLogs/gpinitsystem_date.log

系统初始化日志

$GPADMIN_HOME/gpAdminLogs/gpstart_date.log

启动日志

$GPADMIN_HOME/gpAdminLogs/gpstop_date.log

停止日志

$GPADMIN_HOME/gpAdminLogs/gpsegstart.py_host:gpadmin_date.log

Segment主机启动日志

$GPADMIN_HOME/gpAdminLogs/gpsegstop.py_host:gpadmin_date.log

Segment主机停止日志

$MASTER_DATA_DIRECTORY/pg_log/startup.log, $GPDATA_DIR/segprefixN/pg_log/startup.log

Master和Segment实例启动日志

$MASTER_DATA_DIRECTORY/gpperfmon/logs/gpmon.*.log

gpperfmon日志

$MASTER_DATA_DIRECTORY/pg_log/*.csv, $GPDATA_DIR/segprefixN/pg_log/*.csv

Master和Segment日志

$GPDATA_DIR/mirror/segprefixN/pg_log/*.csv

Mirror Segment日志

$GPDATA_DIR/primary/segprefixN/pg_log/*.csv

Primary Segment日志

/var/log/messages

Linux全局系统消息

表9-4 数据库日志文件位置

Greenplum中的每个Master和Segment实例都运行一个PostgreSQL数据库服务器，带有自己的数据库服务器日志文件。日志文件在pg_log目录中创建，以逗号分隔值（CSV）格式写入。某些日志条目不包含所有日志字段的值，例如，只有与查询工作进程关联的日志条目才会具有slice_id。可以通过查询的会话标识符gp_session_id和命令标识符gp_command_count来标识特定查询的相关日志条目。

表9-4列出了Greenplum数据库服务器日志格式。

编号

字段名称

数据类型

描述

1

event_time

timestamp

日志条目写入时间

2

user_name

varchar(100)

数据库用户名

3

database_name

varchar(100)

数据库名

4

process_id

varchar(10)

系统进程ID，前缀为“p”

5

thread_id

varchar(50)

线程计数，前缀为“th”

6

remote_host

varchar(100)

Master上是客户端的主机名/地址，Segment上是Master的主机名/地址

7

remote_port

varchar(10)

Master或Segment实例的端口号

8

session_start_time

timestamp

会话打开连接的时间

9

transaction_id

int

Master上的顶级事务ID，是任何子事务的父级。

10

gp_session_id

text

会话标识符编号，前缀为“con”

11

gp_command_count

text

会话中的命令号，前缀为“cmd”

12

gp_segment

text

Segment content标识符，primary前缀为“seg”，mirror前缀为“mir”，Master始终为-1。

13

slice_id

text

slice ID（正在执行的查询计划的一部分）

14

distr_tranx_id

text

分布事务ID

15

local_tranx_id

text

本地事务ID

16

sub_tranx_id

text

子事务ID

17

event_severity

varchar(10)

值包括：LOG、ERROR、 FATAL、PANIC、DEBUG1、DEBUG2

18

sql_state_code

varchar(10)

与日志消息关联的SQL状态代码

19

event_message

text

日志或错误消息文本

20

event_detail

text

与错误或警告关联的详细消息文本

21

event_hint

text

与错误或警告关联的提示消息文本

22

internal_query

text

内部生成的查询文本

23

internal_query_pos

int

内部生成的查询游标索引文本

24

event_context

text

生成此消息的上下文

25

debug_query_string

text

用户提供的查询字符串，带有用于调试的完整详细信息，可以修改此字符串以供内部使用

26

error_cursor_pos

int

查询字符串中游标索引

27

func_name

text

生成消息的函数

28

file_name

text

生成消息的内部代码文件

29

file_line

int

生成消息的内部代码文件行

30

stack_trace

text

与消息关联的堆栈跟踪文本

表9-4 Greenplum数据库服务器日志格式

Greenplum提供了一个名为gplogfilter的实用程序，可以在日志文件中搜索与指定条件匹配的条目。缺省时，此实用程序在默认日志记录位置搜索日志文件。例如要显示主日志文件的最后三行：

代码语言：javascript复制
gplogfilter -n 3

要同时搜索所有Segment日志文件，可通过gpssh实用程序运行gplogfilter。例如，要显示每个Segment日志文件的最后三行：

代码语言：javascript复制
gpssh -f seg_host_file
=> source /usr/local/greenplum-db/greenplum_path.sh
=> gplogfilter -n 3 /data1/primary/gp*/pg_log/gpdb*.csv

9.5 例行维护

为保持Greenplum数据库系统高效运行，必须定期清除数据库中的过期数据，并更新表统计信息，以便查询优化器获得准确信息以生成正确的执行计划。Greenplum数据库要求定期执行某些任务以实现最佳性能。这里讨论的任务是必需的，DBA可以使用标准UNIX工具（如cron脚本）将其自动化。

9.5.1 定期vacuum

Greenplum使用的MVCC事务并发模型，这种设计意味着被删除或更新的数据行仍然占用磁盘上的物理空间，即使它们对新事务不可见。如果数据库有许多更新和删除，则存在许多过期的行，必须使用VACUUM命令回收它们使用的空间。

Greenplum监视事务ID，超过20亿个事务时可能会产生事务ID回卷，因此有必要至少每20亿次事务对每个数据库的每个表执行一次vacuum操作。如果不定期清理数据库，Greenplum将生成警告或错误。 可以在每天业务低峰期定时对每个数据库执行下面的脚本，释放过期行所占空间，同时释放事务号防止XID回卷失败，并分析数据库。

代码语言：javascript复制
#!/bin/bash
DBNAME=$1
SYSTABLES=" table_schema || '.' || table_name || ';' from information_schema.tables where table_type='BASE TABLE'"

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

如果Greenplum数据库由于不经常进行vacuum维护而达到xid_stop_limit事务ID限制时，它将变得无响应。此时需要DBA执行以下操作来恢复（将dw替换为实际受影响的数据库名）：

代码语言：javascript复制
# 停库
gpstop -af
# 编辑postgresql.conf，临时将xid_stop_limit设为一个小值
xid_stop_limit = 10000000
# 启库
gpstart -a
# 执行vacuum
psql -c dw -c "VACUUM FREEZE"
# 编辑postgresql.conf，恢复xid_stop_limit缺省值
xid_stop_limit = 100000000
# 重启库
gpstop -afr

9.5.2 定期维护系统目录

使用CREATE和DROP命令进行的大量数据库更新会增加系统目录的大小并影响系统性能。例如，运行许多DROP TABLE语句会降低总体系统性能，因为在对目录表执行元数据操作期间会进行过度的数据扫描。通常执行数千到数万条DROP TABLE语句可能发生性能损失。应该定期运行系统目录维护过程，以回收已删除对象占用的空间。 建议定期在系统目录上运行REINDEX和VACUUM，以清除已删除对象在系统索引和表中占用的空间。如果数据库经常包括许多DROP语句，则在非高峰时间每天使用VACUUM对系统目录进行维护是安全和适当的。可以在系统可用时执行此操作，例如在每天业务低峰期定时对每个数据库执行下面的脚本。

代码语言：javascript复制
#!/bin/bash
DBNAME=$1
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 -as pg_catalog -d $DBNAME

如果执行目录维护期间需要停止进程，运行pg_cancel_backend（<PID>）以安全停止Greenplum数据库进程。

9.5.3 加强的系统目录维护

系统目录可能因为长期未执行维护而膨胀，这会导致简单的元数据操作等待时间过长。如果在psql中执行d命令列出用户表需要等待两秒以上，表示系统目录膨胀。此时必须在计划停机期间执行加强的系统目录维护，维护时停止系统上的所有目录活动，因为VACUUM FULL会对系统目录加排它锁。加强的系统目录维护步骤如下： （1）停止应用对Greenplum的访问 （2）reindex pg_catalog.* （3）vacuum full pg_catalog.* （4）analyze pg_catalog.*

系统目录表pg_attribute通常是最大的目录表。以下两种情况说明pg_attribute表膨胀明显，该表上的VACUUM FULL操作可能需要大量时间，并且可能需要单独执行。

  • pg_attribute表包含大量记录。
  • gp_toolkit.gp_bloat_diag视图中出现大量pg_attribute表的诊断消息。

如果定期维护系统目录，不应该需要执行此高成本的过程。

9.5.4 为查询优化执行vacuum与analyze

Greenplum使用基于成本的查询优化器，该优化器依赖于数据库统计数据。准确的统计信息使查询优化器能更好地估计选择性和查询操作检索的行数，这些估计有助于选择最有效的查询计划。如果存储在系统目录表中的统计信息过期，则可能生成低效的执行计划。ANALYZE命令为查询优化器收集并更新列级统计信息。可以在同一命令中运行VACUUM和ANALYZE操作：

代码语言：javascript复制
=# VACUUM ANALYZE mytable;

在膨胀表上运行VACUUM ANALYZE命令可能会产生不正确的统计信息，因为大量表磁盘空间被删除或过时的行所占用。对于大表，ANALYZE命令从随机的行样本计算统计信息。它通过将样本中每页的平均行数乘以表中的实际页数来估计表中的行数。需要权衡统计数据的准确性和生成统计数据所需的时间。可以在系统或会话级别调整default_statistics_target参数值控制样本值数量，范围为1到1000，默认为100。需要重新加载使配置生效。如果采样中包含许多空页，则估计的行计数可能不准确。

可以在gp_toolkit.gp_bloat_diag中查看未使用的磁盘空间（已删除或过时行占用空间）信息。如果表的bdidiag列包含significant amount of bloat suspected，说明大量表磁盘空间由未使用的空间组成。vacuume表后会在gp_bloat_diag视图中增加一条记录。

执行VACUUM FULL table_name可以删除表上未使用的磁盘空间，由于需要上表级排它锁，可能需要一个维护期窗口执行VACUUM FULL。作为临时解决方案，可以先执行ANALYZE来计算列统计信息，然后对表运行VACUUM来生成准确的行计数，例如：

代码语言：javascript复制
analyze cust_info;
vacuum cust_info;

运行不带参数的ANALYZE会更新数据库中所有表的统计信息，这可能是一个运行时间很长的过程，不建议这样做。当数据发生更改时，应该有选择地分析表，或者使用analyzedb实用程序。analyzedb程序更新表统计信息，同时分析表。对于AO表，analyzedb仅在统计信息不是最新的情况下更新统计信息。

在大表上运行分析可能需要很长时间，如果无法对非常大的表的所有列运行分析，则只能使用 ANALYZE table(column, ...)为选定列生成统计信息，确保包含join、where、sort、group by或having中使用的列。

对于分区表，可以选择仅在已更改的分区（如新增分区）上运行分析。分区表可以在父表或叶子子表上运行ANALYZE。中间层的子分区表不存储任何数据或统计信息，因此对它们运行ANALYZE不起作用。可以在pg_partitions系统目录表中查询分区表名称：

代码语言：javascript复制
SELECT partitiontablename from pg_partitions WHERE tablename='parent_table;

如果要在启用GPORCA（默认设置）的分区表上运行查询，必须使用ANALYZE命令收集分区表根分区的统计信息。

9.5.5 统计信息自动收集

通常在加载数据后、创建索引后，或者在插入、更新和删除大量数据之后需要执行ANALYZE操作。ANALYZE只在表上加读锁，因此可以与其他数据库活动并行，但不建议在执行加载、插入、更新、删除大量数据或创建索引的同时运行ANALYZE。

建议配置自动收集统计信息。gp_autostats_mode与gp_autostats_on_change_threshold参数一起确定触发自动分析操作的时间，触发自动统计信息收集时，查询中会添加分析步骤。

gp_autostats_mode的默认值为on_no_stats，在对没有统计信息的表执行CREATE TABLE AS SELECT、INSERT或COPY操作时触发表的统计信息收集。将gp_autostats_mode设置为on_change，并且当受影响的行数超过gp_autostats_on_change_threshold定义的阈值（默认值为2147483647）时，会触发统计信息收集。触发自动统计数据收集的操作有：CREATE TABLE AS SELECT、UPDATE、DELETE、INSERT、COPY。gp_autostats_mode设置为none将禁用自动统计信息收集。

对于分区表，如果从分区表的顶级父表插入数据，则不会触发自动统计信息收集。如果数据直接插入到分区表的叶表中（存储数据的地方），则会触发自动统计信息收集。

9.5.6 重建索引

对于B树索引，新构造的索引访问速度略快于多次更新的索引，因为在新构建的索引中，逻辑上相邻的页面通常在物理上也相邻，因此定期重建旧索引可以提高访问速度。如果一个页面上除了几个索引键以外的所有索引键都已删除，则索引页面上会浪费空间，重新索引将回收浪费的空间。在Greenplum数据库中，删除索引（drop index）然后重新创建索引（CREATE index）通常比使用REINDEX命令更快。

对于具有索引的表列，由于需要同时更新索引，某些操作（如批量更新或表插入）的执行速度可能会较慢。要提高具有索引的表上批处理性能，可以先删除索引，执行批量操作，然后再重新创建索引。

9.5.7 管理数据库日志文件

Greenplum通常会输出大量的日志，尤其是在较高的debug级别。不需要无限期保存日志，管理员应定期清除旧的日志文件。默认情况下，Greenplum为Master数据库实例和Segment数据库实例启用了日志文件轮转。

log_rotation_size参数设置触发轮转的单个日志文件的大小，默认1GB。当当前日志文件大于等于此大小时，将关闭该文件并创建新的日志文件。设置为0则禁用基于大小的日志轮转。log_rotation_age参数指定触发轮转的日志文件创建时间。创建日志文件后经过该参数指定的时间后，将创建一个新的日志文件。默认日志轮换时间1d在当前日志文件创建24小时后创建新日志文件。设置为0则禁用基于时间的日志旋转。

管理员需要执行脚本或程序，定期清理Master和每个Segment实例pg_log目录中的旧日志文件。例如，在Master上执行下面的脚本，删除所有实例10天前创建的日志文件。

代码语言：javascript复制
# 在master上执行
gpssh -f all_host -e 'find /data/master/gpseg-1/pg_log -mtime  10 -type f -delete'
gpssh -f all_host -e 'find /data1/primary/gp*/pg_log -mtime  10 -type f -delete'
gpssh -f all_host -e 'find /data2/primary/gp*/pg_log -mtime  10 -type f -delete'

Greenplum默认将$GPHOME/bin目录下的管理程序的日志文件写入~/gpAdminLogs目录中。每次运行管理程序时，特定程序执行的日志文件都会追加到其每日日志文件中。

9.6 推荐的监控与维护任务

本节给出Greenplum为确保数据库集群的高可用和高性能而建议的监控与维护任务。监控可帮助及早发现和诊断问题，维护可帮助保持系统的稳定状态，并避免因系统表过大或可用磁盘空间减少而导致的性能下降。没有必要在每个集群中实施所有这些建议，用户可根据自己对服务的要求，调整任务调度频率和严重等级定义（警告、严重、致命）。

9.6.1 数据库活动监控

1. 列出下线的Segment，5-10分钟执行一次，返回行则报警，等级为严重。

代码语言：javascript复制
psql -d postgres -c "select * from gp_segment_configuration where status <> 'u';"

纠正措施：

  • 确认相应Segment所在主机有响应。
  • 检查相应Segment的pg_log文件寻找下线原因。
  • 如果没有发现意外错误信息，执行gprecoverseg将相应Segment重新上线。

2. 列出change tracking模式的Segment（对应的mirror宕机），5-10分钟执行一次，返回行则报警，等级为严重。

代码语言：javascript复制
psql -d postgres -c "select * from gp_segment_configuration where mode = 'c';"

纠正措施：

  • 确认相应Segment所在主机有响应。
  • 检查相应Segment的pg_log文件寻找mirror宕机原因。
  • 如果没有发现意外错误信息，执行gprecoverseg将相应Segment重新上线。

3. 列出re-syncin模式的Segment（正在重新同步），5-10分钟执行一次，返回行则报警，等级为严重。

代码语言：javascript复制
psql -d postgres -c "select * from gp_segment_configuration where mode = 'r';"

纠正措施：

  • 如果mode字段的值始终没有从'r'改为's'，检查相应Segment的pg_log文件中是否存在错误。

4. 检查primary/mirror角色改变的Segment（可能造成集群不平衡），5-10分钟执行一次，返回行则报警，等级为严重。

代码语言：javascript复制
psql -d postgres -c "select * from gp_segment_configuration where preferred_role <> role;"

纠正措施：

  • 执行gprecoverseg -r，将Segment置回它们的初始角色。

5. 运行一个分布式查询以测试它是否在所有Segment上运行，5-10分钟执行一次，每个Primary Segment应返回一行，等级为致命。

代码语言：javascript复制
psql -d postgres -c "select gp_segment_id, count(*) from gp_dist_random('pg_class') group by 1 order by gp_segment_id;"

纠正措施：

  • 如果此查询失败，则向群集中的某些Segment的分发有问题。这是一个罕见事件，检查无法调度的主机，确保没有硬件或网络问题。

6. 测试Master镜像状态，5-10分钟执行一次，如果不是streaming则报警，等级为严重。

代码语言：javascript复制
psql -d dw -c 'select pid, state from pg_stat_replication;'

纠正措施：

  • 检查Master和Standby Master的pg_log文件是否有错误。如果没有意外错误并且机器已启动，运行gpinitstandby程序使Standby Master联机。

7. 执行基本检查，5-10分钟执行一次，查看Master是否正常工作，等级为致命。

代码语言：javascript复制
psql -d postgres -c "select count(*) from gp_segment_configuration;"

纠正措施：

  • 如果此查询失败，Master可能宕机。再试几次，然后手动检查Master。如果Master宕机，重启主机以确保活动主机上Master进程，然后激活Standby Master。

8. 检查系统的FATAL和ERROR日志消息，15分钟一次，发现则并报警，等级为警告。

代码语言：javascript复制
psql --pset=pager=off -x -c "select * from gp_toolkit.gp_log_system where logseverity in ('FATAL','ERROR') and logtime > (now() - interval '15 minutes');"

纠正措施：

  • 向DBA发送报警信息加以分析。

9.6.2 硬件和操作系统监控

1. 检查数据库和操作系统的空间使用，5-30分钟执行一次，硬盘使用率75%报警，等级为致命。

纠正措施：

  • 在用户表上使用VACUUM/VACUUM FULL回收过期行占用的空间。

2. 检查网络错误或丢包，每小时执行一次，等级为严重。

纠正措施：

  • 与运维或系统管理团队合作解决错误。

3. 检查RAID错误或RAID性能降级，每5分钟执行一次，等级为严重。

纠正措施：

  • 尽快更换发生故障的磁盘。
  • 与系统管理团队合作，尽快解决其他RAID或控制器错误。

4. 检查I/O带宽与I/O倾斜，在创建群集或怀疑硬件有问题时执行gpcheckperf，预期结果为硬盘读2GB/S，硬盘写1GB/S，网络读写10Gb/S。

纠正措施：

  • 如果结果低于预期，与系统管理团队合作解决问题。

9.6.3 系统目录监控

1. 检查集群中所有主机上的目录一致性，每星期对每个库执行一次，等级为严重。

代码语言：javascript复制
gpcheckcat -O dbname

纠正措施：

  • 对识别出来的问题执行修复脚本（gpcheckcat -g生成）。

2. 检查没有相应pg_attribute条目的pg_class条目。在系统没有用户的停机期间，每个月在每个数据库中运行，等级为严重。

代码语言：javascript复制
gpcheckcat -R pgclass

纠正措施：

  • 对识别出来的问题执行修复脚本（gpcheckcat -g生成）。

3. 检查临时schema和缺少定义的schema。在系统没有用户的停机期间，每个月在每个数据库中运行，等级为严重。

代码语言：javascript复制
gpcheckcat -R namespace

纠正措施：

  • 对识别出来的问题执行修复脚本（gpcheckcat -g生成）。

4. 检查约束和随机分布表。在系统没有用户的停机期间，每个月在每个数据库中运行，等级为严重。

代码语言：javascript复制
gpcheckcat -R distribution_policy

纠正措施：

  • 对识别出来的问题执行修复脚本（gpcheckcat -g生成）。

5. 检查对不存在对象的依赖关系。在系统没有用户的停机期间，每个月在每个数据库中运行，等级为严重。

代码语言：javascript复制
gpcheckcat -R dependency

纠正措施：

  • 对识别出来的问题执行修复脚本（gpcheckcat -g生成）。

9.6.4 数据库维护

1. 检查缺少统计信息的表，在每个库上执行。

代码语言：javascript复制
select * from gp_toolkit.gp_stats_missing;

纠正措施：

  • 在相应表上执行analyze。

2. 检查数据文件中是否存在膨胀表，每个月在每个库上执行，等级为警告。

代码语言：javascript复制
select * from gp_toolkit.gp_bloat_diag;

纠正措施：

  • 在维护窗口期对相应表执行VACUUM FULL。

3. 回收堆表中已删除的行，以便重用它们占用的空间，每天执行一次，等级为致命。

代码语言：javascript复制
vacuum <user_table>;

纠正措施：

  • 定期vacuum更新的表以避免膨胀。

4. 更新表的统计信息，在加载数据后和查询之前执行，等级为致命。

代码语言：javascript复制
analyzedb -d <database> -a

纠正措施：

  • 定期分析更新的表，以便优化器能够生成高效的查询执行计划。

5. 备份数据库。每天，或根据备份计划的要求执行并行备份，等级为致命。

代码语言：javascript复制
gpbackup（社区版没提供）

纠正措施：

  • 最佳做法是准备好当前备份，以防必须恢复数据库。

6. 对系统目录执行vacuum、reindex和analyze。对每个数据库每周执行一次，如果频繁创建和删除数据库对象，则频率应该更高，注意按顺序执行。

代码语言：javascript复制
vacuum 
reindexdb -s <database>
analyzedb -s pg_catalog -d <database>

纠正措施：

  • 优化器从系统表中检索信息以创建查询计划。如果系统表和索引随时间膨胀，扫描系统表会增加查询执行时间。重建索引会使索引没有统计信息，因此重建后执行ANALYZE很重要。

9.6.5 补丁与升级

1. 确保对Linux内核应用了任何错误修复或增强，至少每6个月执行一次内核升级，等级为重要。 2. 升级Greenplum数据库小版本，每季度执行一次，等级为重要。

小结

  • Greenplum中的角色可以是用户或组。组角色主要用于简化权限管理，组中的成员缺省会继承赋予组的权限。数据库对象的属主拥有对象上的所有权限，属主或超级用户（gpadmin）可以将对象权限授予其他用户。用户口令以加密形式存储于pg_authid.rolpassword列，缺省使用MD5，也可以配置成SHA-256加密。
  • 向Greenplum表导入导出数据的常用方法有gpfdist外部表、web外部表、gpload命令行工具、COPY SQL命令等。gpfdist是Greenplum提供的一种文件服务器，它利用集群中的所有Segment并行读写本地文件。向表中导入大量数据后，应该执行ANALYZE SQL命令，为查询优化器更新系统统计信息。
  • Greenplum中新旧查询优化器并存，优先选择新的GPORCA优化器，它针对分区表、子查询、WITH、INSERT、去重聚合等查询类型有所改进。查询计划是在Segment上分片并行执行的。数据本地化情况、为查询分配的段数量对查询性能具有直接影响。同很多数据库系统类似，EXPLAIN用于语句输出查询执行计划。学会读懂EXPLAIN的信息，对于排查性能问题十分有用。EXPLAIN ANALYZE会实际执行SQL语句，并且比单纯的EXPLAIN输出更多的信息。
  • Greenplum的例行监控任务主要包括：检查系统状态、检查磁盘空间使用、检查数据分布倾斜、查看元数据信息、监控内存使用情况、检查工作文件使用信息、查看搜索服务器日志文件等。
  • Greenplum的例行维护任务主要包括：定期执行vacuum与analyze，避免表膨胀和统计数据失准；定期重建索引提高查询性能；定期维护系统目录，清除已删除对象在系统索引和表中占用的空间；设置管理数据库日志文件轮转，定期清理数据库日志文件。
  • Greenplum建议的监控与运维任务主要包括：监控数据状态、监控数据库警告日志、监控硬件和操作系统、监控系统目录、检查缺少统计信息的表、检查表膨胀、定期vacuum和analyze表、定期升级和打补丁等。
数据库管理 数据库 TDSQLMySQL版 云推荐引擎 postgresql

0 人点赞