Greenplum 实时数据仓库实践(9)——Greenplum监控与运维
目录
想要一个数据库长久健康的运行,离不开完备的运维工作,切忌只运而不维。针对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命令用于创建一个角色,例如:
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命令指定这些属性:
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系统权限。
create role admin createrole createdb;
一旦组角色存在,就可以使用GRANT和REVOKE命令添加或删除组成员(用户角色):
grant admin to john, sally;
revoke admin from bob;
为简化对象权限的管理,应当只为组级别的角色授予适当的权限。成员用户角色继承组角色的对象权限:
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属性。
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语句为角色授予或回收一个对象权限:
grant insert on mytable to jsmith;
revoke all privileges on mytable from jsmith;
可以使用DROP OWNED和REASSIGN OWNED命令为一个角色删除或重新赋予对象属主权限。只有对象的属主或超级用户能够执行此操作:
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加密
# 设置缺省的口令加密算法
gpconfig -c password_hash_algorithm -v 'SHA-256'
# 重载参数使之动态生效
gpstop -u
gpconfig -s password_hash_algorithm
2. 会话级启用SHA-256加密
-- 用gpadmin登录Greenplum后执行
set password_hash_algorithm = 'SHA-256';
-- 查看
show password_hash_algorithm;
3. 验证口令加密方式生效 (1)建立一个具有login权限的新角色,并设置口令。
create role testdb with password 'testdb12345#' login;
(2)修改客户端认证方法,允许存储SHA-256加密的口令。 下面的shell命令将在pg_hba.conf文件的第一行添加一条记录。注意pg_hba.conf文件中记录的匹配顺序。
sed -i '1ihost all testdb 0.0.0.0/0 password' /data/master/gpseg-1/pg_hba.conf
(3)重载pg_hba.conf配置
gpstop -u
(4)以刚创建的testdb用户登录数据库,在提示时输入正确的口令。
psql -d postgres -h mdw -U testdb
验证口令被以SHA-256哈希方式存储,加密后的口令存储在pg_authid.rolpasswod字段中。作为超级用户登录,执行下面的查询:
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协议的外部表,可以很容易地将外部数据导入普通表中,如下例所示。
# 分隔符的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://协议。
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的例子。
# 处理当前目录中的文件,使用缺省的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进程。
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外部表的步骤如下:
- 启动gpfdist文件服务器。
- 定义外部表。
- 将数据文件放置于外部表定义中指定的位置。
- 使用SQL命令查询外部表。
Greenplum提供可读与可写两种gpfdist外部表,但一个外部表不能既可读又可写。一个gpfdist可读外部表的例子如下所示。
# 分别在两个主机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语句建立可写外部表并插入数据。
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文件:
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主机上的命令。
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脚本文件。
create external web table log_output (linenum int, message text)
execute '/home/gpadmin/get_log_data.sh' ON 5
format 'text' (delimiter '|');
Greenplum集群中每台主机的相同位置上都必须有同一个可执行的脚本,否则查询会报错:
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脚本内容如下:
#!/bin/bash
echo "1|aaa"
echo "2|bbb"
则该表将返回10条(每个段两条)数据:
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外部表。
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子句,否则报错:
ERROR: ON clause may not be used with a writable external table
myprogram1.sh的内容如下:
#!/bin/bash
while read line
echo "File:${line}" >> /home/gpadmin/programs/a.txt
done
下面将脚本设置为可执行,并复制到集群所有主机的相同目录下:
chmod 755 ./programs/myprogram1.sh
scp -r programs 210.73.209.102:/home/gpadmin/
scp -r programs 140.210.73.66:/home/gpadmin/
现在向外部表中插入数据:
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表。
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不检查约束错误,但可以在查询外部表时过滤约束错误。例如,消除重复键值错误:
insert into table_with_pkeys select distinct * from external_table;
下面的例子记录错误信息,并设置错误行阈值为10。错误数据通过Greenplum的内部函数gp_read_error_log('external_table_name')访问。
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的控制文件内容:
---
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
- 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:
LOCAL_HOSTNAME:
- smdw-1
- smdw-2
下面看一个gpload示例。我们先在smdw上准备本地文件数据如下:
[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表:
\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:
[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表确认执行结果:
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导入数据的例子。
[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命令中可以指定文件格式、分隔符、字符集等属性:
copy test from '/tmp/file0' with (format csv, delimiter '|', encoding 'latin1');
下面的例子将表数据导出到Master的本地文件中。如果文件不存在则建立文件,否则会用导出数据覆盖文件原来的内容。
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拷贝数据,通过管道可以实现类似于流复制的功能,例如:
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文件,制造一行格式错误的数据。
[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命令。与导出不同,导入会向表中追加数据。
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,所以违反约束,没有数据被拷贝,也不会更新错误日志。
[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时可能出现如下错误:
ERROR: invalid byte sequence for encoding "UTF8": 0x00
这是一个已知错误,解决方法是先替换掉文件中的\0字符串再执行copy:
# 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从客户端本地读取文件:
\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子句拷贝源表的列定义与分布策略。
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权限。
grant insert on unload_expenses TO admin;
导出数据并查看输出文件。
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:
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的控制文件中,声明一个单字符作为列分隔符。分隔符必须出现在字段值之间,不要在一行的开头或结尾放置分隔符。如使用管道符(|)作为列分隔符:
data value 1|data value 2|data value 3
下面的建表命令显示以管道符作为列分隔符:
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的控制文件中指定转义符。假设有以下三个字段的数据:
backslash = \
vertical bar = |
exclamation point = !
指定管道符(|)为列分隔符,反斜杠(\)为转义符。则对应的数据行格式如下:
backslash = \\ | vertical bar = \| | exclamation point = !
可以对八进制或十六进制序列应用转义符。在装载进Greenplum时,转义后的值就是八进制或十六进制的ASCII码所表示的字符。例如,取址符(&)可以使用十六进制的(\0x26)或八进制的(\046)表示。
如果要在CREATE EXTERNAL TABLE、COPY命令的ESCAPE子句,或者gpload的控制文件中禁用转义,可如下设置:
ESCAPE 'OFF'
该设置常用于输入数据中包含很多反斜杠(如Web日志数据)的情况。
(2)CSV文件转义 可以在CREATE EXTERNAL TABLE、COPY的ESCAPE子句,或者gpload的控制文件中指定转义符。假设有以下三个字段的数据:
Free trip to A,B
Special rate "1.79"
指定逗号(,)为列分隔符,一个双引号(")为转义符。则数据行格式如下:
"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控制,缺省是开启的。
[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,可以满足大多数场景,一般不需要修改这个参数。
如果溢出文件的数量超过该参数的值,数据库会返回一个错误:
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子句,可能不会真的检索所有记录。例如:
=# 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得到的执行计划,参考一下下面这个简单的例子:
=# 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的溢出文件的数量。例如:
# 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优化器的输出:
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优化器的输出:
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,可以发现哪些算子会用到溢出文件,使用了多少内存,需要多少内存。例如:
. . .
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的状态与配置
# 概要信息
gpstate
# 配置详细信息
gpstate -s
2. 查看mirror段的状态与配置
# mirror状态
gpstate -m
# primary与mirror的映射
gpstate -c
# standby master状态
gpstate -f
3. 查看当前会话信息和锁等待 可以使用psql的\set命令实现类似于MySQL的show processlist的功能,显示当前会话信息。例如,在gpadmin操作系统用户主目录下创建.psqlrc文件,内容容下:
\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变量分别查看当前会话和锁等待信息:
[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的查询:
select pg_cancel_backend(1234);
还可以为pg_cancel_backend()函数提供一个可选的消息参数,用于通知该查询的用户,告知为何终止了其执行的事务。例如:
select pg_cancel_backend(1234,'因系统维护暂停使用');
执行该事务的用户会收到如下信息:
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数据库系统具体实例宕机记录语句如下所示,可根据具体时间需求限定查询条件:
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. 查看系统空间使用率
df -h
Greenplum集群中任何主机的磁盘空间使用率不要超过70%。
2. 查看Segment剩余空间(单位KB)
select * from gp_toolkit.gp_disk_free order by dfsegment;
3. 检查分布式数据库和表的大小
-- 数据库使用空间(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时没有指定分布键而自动以第一个字段作为分布键。通常可能会表现出查询性能差,甚至出现内存不足的报错。数据倾斜会直接影响表扫描的性能,同时也会影响相关的关联查询和分组汇总等计算的性能。
检验数据分布是否均匀非常重要,无论是初次加载数据之后,还是增量数据加载之后。有时,数据量不大时可能不会明显的表现出倾斜,所以需要定期检查倾斜情况。
-- 查看表的分布键
\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出现了故障,有可能与计算倾斜有关。处理计算倾斜时,首先可以看一下溢出文件的情况,如果有计算倾斜但又没有出现溢出文件,可能这种倾斜并不会造成严重的后果。
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. 避免极端倾斜警告 执行执行哈希联接操作的查询时,可能会收到以下警告消息:
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表的列定义、索引、约束、规则、分布键、分区键、分区子表等信息。
\d+ sales_order
所有数据库对象都有一个对应的\d命令,如果加上S表示输出系统对象,加上+表示输出详细信息。\?命令显示一个简要psql帮助信息。
可以使用系统视图pg_stat_operations和pg_stat_partition_operations查看对象(如表)上执行的操作。例如,要查看表的创建时间以及上次清空和分析表的时间:
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数据库中创建视图:
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架构添加到用户的模式搜索路径中:
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的实用程序,可以在日志文件中搜索与指定条件匹配的条目。缺省时,此实用程序在默认日志记录位置搜索日志文件。例如要显示主日志文件的最后三行:
gplogfilter -n 3
要同时搜索所有Segment日志文件,可通过gpssh实用程序运行gplogfilter。例如,要显示每个Segment日志文件的最后三行:
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回卷失败,并分析数据库。
#!/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替换为实际受影响的数据库名):
# 停库
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对系统目录进行维护是安全和适当的。可以在系统可用时执行此操作,例如在每天业务低峰期定时对每个数据库执行下面的脚本。
#!/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操作:
=# 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来生成准确的行计数,例如:
analyze cust_info;
vacuum cust_info;
运行不带参数的ANALYZE会更新数据库中所有表的统计信息,这可能是一个运行时间很长的过程,不建议这样做。当数据发生更改时,应该有选择地分析表,或者使用analyzedb实用程序。analyzedb程序更新表统计信息,同时分析表。对于AO表,analyzedb仅在统计信息不是最新的情况下更新统计信息。
在大表上运行分析可能需要很长时间,如果无法对非常大的表的所有列运行分析,则只能使用 ANALYZE table(column, ...)为选定列生成统计信息,确保包含join、where、sort、group by或having中使用的列。
对于分区表,可以选择仅在已更改的分区(如新增分区)上运行分析。分区表可以在父表或叶子子表上运行ANALYZE。中间层的子分区表不存储任何数据或统计信息,因此对它们运行ANALYZE不起作用。可以在pg_partitions系统目录表中查询分区表名称:
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天前创建的日志文件。
# 在master上执行
gpssh -f all_host -e 'find /data/master/gpseg-1/pg_log -mtime +10 -type f -delete'