Greenplum 4.2.2 管理员指南


Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 1 - 译者序 背景简介 近几年,随着 Greenplum(GP)作为一种数据仓库产品在中国的大范围推广,越来越多的 技术人员开始接触这个陌生的数据仓库。其基于一个开源的数据库 PostgreSQL(PG),虽 然说 PG 是一个被全世界广泛使用的开源数据库,但在中国,使用者相对较少。再加上 Greenplum 具备独特的分布式 Share-Nothing 特征,在使用初期很难准确深刻的理解很 多细节。在调优方面和故障处理方面更是无从入手。译者在接触了 Greenplum 两年之 后决定将主要内容翻译为中文,并将译者使用中遇到的问题解决方案适当的添加进来。 该书与官方文档不等价,内容按照译者的方式讲述。本书基于 Greenplum4.2.2.0 版本, 由于 GP 的多个版本之间存在较大的功能差异,本书中涉及的内容未必适用其他版本, 尤其是早期的版本(4.0 之前)。本书中译者经验的叙述不代表官方观点,对于一些风险 较高的操作,最好获得 EMC 官方的支持。建议慎重对待任何建议,从自己对 GP 深入理 解的角度来看待问题才是最合理的。 致读者 本书虽基于官方文档翻译,但灌注了译者诸多的心血,请尊重译者的辛勤工作,如其中 内容不符合你的期望,请不要攻击诽谤译者,未经译者授权不得用于任何商业用途,译 者保留追究的权利。如有任何的建议,可邮件联系译者,谢绝恶意攻击,你的建议译者 会尽力考虑,但未必如你所愿。 你唾手可得的是译者分发的普通版本,基于官方文档做简单的翻译处理,译者不承诺增 加调优排错等超出官方文档内容的知识,比如为不同性能的机器配置非平衡集群,非对 称镜像,无法启动事故处理,日常的管理维护,查询语句调优,系统最优化安装方案等 等诸多方面,这些内容可能会在书中提及,但译者不承诺做深入讲解。如需更多支持可 联系 EMC 技术支持或者与译者联系(建议在相信译者具备这个能力且对其他支持不是特 别满意的情况下考虑)。望读者理解。 译者:陈淼 电邮:miaochen@mail.ustc.edu.cn 版本说明 由于时间原因,译者可能会不定期的放出新的版本,但译者不做任何承诺。望读者不要 有过多的期望。由于译者英文能力有限,语句不是特别通顺或者有纰漏的地方也望各位 理解。 致谢 感谢我的家人对我工作的理解与支持! 感谢我的老婆长期以来对我工作的理解和支持! Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 2 - 第一章:GPDB 架构简介 GPDB 是一个分布式数据库软件,其可以管理和处理分布在多个不同主机上的海量数据。 对于 GPDB 来说,一个 DB 实例实际上是由多个独立的 PostgreSQL 实例组成的,它们分 布在不同的物理主机上,协同工作,呈现给用户的是一个 DB 的效果。Master 是 GPDB 系统的访问入口,其负责处理客户端的连接及 SQL 命令、协调系统中的其他 Instance(Segment)工作,Segment 负责管理和处理用户数据。 这一章节阐述组成 GPDB 系统的组件及如何协同工作:  管理节点 Master  计算节点 Segment  网络  冗余与失效援备  并行数据装载  管理与监控 管理节点 Master Master 作为GPDB 系统的访问入口,其处理客户端连接的访问以及用户提交的 SQL 语句。 GPDB 是基于 PostgreSQL 开发的,终端用户可以像 PostgreSQL 那样与 GPDB 进行交互。 并同样可以通过客户端程序(如 psql)和应用程序接口(APIs(如 JDBC、ODBC))连接 GPDB。 Master 上存储着全局系统表(global system catalog)(包含 GPDB 系统自身元数据的系统表), Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 3 - 但不存储任何的用户数据,用户数据只存储在 Segment 上。Master 负责客户端的认证、 处理 SQL 命令入口、在 Segment 之间分配工作负载、整合每个 Segment 处理的结果、 将最终结果呈现给客户端程序。 计算节点 Segment 在 GPDB 系统中,Segment 才是真正的数据存储和查询处理的地方。用户 table 和相应 的 index 都分布在 GPDB 系统中的各个 Segment 上,每个 Segment 存储着一部分不同的 数据。Segment 实例(Instance)才是真正的数据处理进程。用户不能够直接跳过 Master 访问 Segment,而只能通过 Master 来访问。 在 GPDB 推荐的硬件配置情况下,每个有效的 CPU 核对应一个 Segment Instance,比如 一个 Segment 主机配备了 2 个双核的 CPU,那么可以选择每个 Segment 主机 4 个主实 例(Primary Instance)。 网络 网络层是 GPDB 系统的重要组件,在用户执行查询时,每个 Segment Instance 都需要执 行响应的处理,网络层涉及到 Segment Instance 之间的通信,网络层可以使用标准的以 太网络协议。 在默认情况下,网络层使用 UDP 通信协议。GPDB 自己会为 UDP 协议做数据包校验, 其可靠性与 TCP 协议相同,但其性能和扩展性远好于 TCP 协议。在使用 TCP 协议的情况 下,GPDB 的 Segment Instance 数量被限制在 1000 个。为了去除这个限制,UDP 协议被 作为默认的网络层协议。 冗余与失效援备 GPDB 提供了避免单点故障的部署选项。本节阐述 GPDB 的冗余组件。  Segment 镜像  Master 镜像  网络层冗余 Segment 镜像 在部署 GPDB 系统时,可以选择配置 Mirror Segment。Mirror Segment 使得数据库 查询在 Primary Instance 不可用时失效援备到备份的 Segment 上。为了配置 Mirror, GPDB 系统需要有足够多的 Host,从而可以保证冗余的 Segment Instance 总是在与 Primary Segment 不同的 Host 主机上。下图展示了在配置了 Mirror 的情况下数据如 何分布在不同的 Host 节点上。Mirror Instance 总是不同于 Primary Instance 的 Host 主机上。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 4 - Segment 失效援备与恢复 在 GPDB 系统的 Mirror 被配置情况下,当 Primary Instance 不可访问时,系统会自 动切换到其对应的 Mirror Instance 上,此时,Mirror Instance 取代 Primary Instance 的作用。只要剩余的可用 Segmeng Instance 能够保证数据完整性,在 Segment Instance 或者 Host 主机宕掉时,GPDB 系统仍可保证可用状态。 每当 Master 无法连接到 Primary Instance 时,其都会在 GPDB 系统的系统表中被标 记为已宕掉状态,并激活/唤起对应的 Mirror Instance 取代原有的 Primary Instance。 在采取响应的措施将其恢复到联机(Online)状态之前,宕掉的 Primary Instance 一直 被排除在系统操作之外。宕掉的 Primary Instance 可以在系统处于运行状态下被恢 复回来。恢复进程仅仅复制宕掉期间发生变化的数据部分。 在未启用 Mirror 的情况下,任何的 Segment Instance 宕掉都会导致系统自动停止服 务。在继续使用系统之前,必须恢复所有 Segment Instance。 Master 镜像 同样,可以部署一个备份/镜像到一个不同与 Master 节点的主机上。在 Primary Master 不可用时,Standby 就成为了热备 Master。Standby Master 与 Primary Master 之间保持事务日志的同步,其保证 Standby Master 与 Primary Master 之间的一致性。 在 Primary Master 失效时,复制进程会自动停止,同时,Standby Master 可以被激 活。在 Standby Master 上,冗余的日志被用来将状态恢复到最后成功提交(commit) 的状态。激活的 Standby Master 实际上会成为 GPDB 的 Primary Master,通过 Master Port(该端口需要设置和 Primary Master 的端口相同)接受客户端的链接访问。 由于 Master 不存储用户数据,仅仅是系统信息表需要在 Primary Master 和 Standby Master 之间同步。这些表很少发生变化,一旦发生变化,就会自动同步到 Standby Master 从而保证与 Primary Master 的一致性。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 5 - 网络层冗余 网络层关系到 Segmeng Instance 之间的通信,其依靠基础网络设施,高可用网络层 可以通过部署双重以太网络实现。不过如果在配置 Mirror 的情况下,通过不同网 段间的 Primary 与 Mirror 之间的对应关系也可以达到网络保障的效果。 并行数据装载 大数据量的数据仓库的其中一个挑战就是在一个给定的时间窗口内装载大量的数据。 GP 通过外部表(External Table)支持高速并行数据装载。外部表可以使用‘单条记录出错 隔离’模式,从而允许管理员在装载数据时将出错的数据记录剥离到一个单独的错误记 录表中。管理员还可以控制错误容忍阀值,从而可以达到控制装载数据质量的目的。 结合使用外部表和 GPDB 的并行文件服务(gpfdist),管理员可以实现最大化并行装载带 宽的目标。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 6 - 管理与监控 对 GPDB 系统的管理通过一些列的命令行工具来实现,它们都放置在$GPHOME/bin 目 录下。GPDB 提供的工具用以实现如下的管理任务:  批量安装 GPDB 软件  初始化 GPDB 系统  启动关闭 GPDB 系统  添加或移除 Host 主机  扩展 Segment Instance 以及在新节点间重新分布 Table  管控和恢复宕掉的 Segment Instance  管控和恢复宕掉的 Master Instance  备份和恢复数据库(并行)  并行装载数据  系统状态报告 GP 还提供了一个可选的监控管理工具,管理员可以选择与 GPDB 一起安装和启用。 GPCC(Greenplum Command Center)使用数据收集代理程序(Agent)在各个 Segment Host 收集数据库的指标。Agent 会定期(比如 15 秒)主动将 Segment Host 上收集的数据发送到 Master。用户可以直接查询 CC 数据库查看系统指标。GPCC 还有一个基于 WEB 的图形 化用户交互界面服务,其可以独立于 GPDB 安装。 第二章:分布式数据库概念 GP 是一个分布式数据库系统。这就意味着在物理上,数据是存储在多个数据库服务上 的(在 GP 中称为 Segment Instance)。这些独立的数据库服务通过网络通信(在 GP 中称为 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 7 - 网络层)。分布式数据库的一个基本特征是,用户和客户端程序如同访问一个单机数据 库(在 GP,这个入口数据库称之为 Master)。数据库分布在不同的机器上,但对于用户 来说,是感觉不出的。 数据是如何存储的 要理解 GPDB 是如何在不同的 Host 和 Segment Instance 之间存储数据,如下图所示的简 单逻辑数据库,主键(Primary Key)被使用黑体标记,外键(Foreign Key)关系通过连线标明。 用数据仓库的属于来说,这种称为星型模型。在该数据库模型下,sale 表通常被称为事 实表(Fact Table),其他表(Customer、Vendor、Product)被成为维表(Dimension Table)。 GPDB 系统中所有的表都是分布的,这意味着数据被拆分成无重叠的记录集合。每部分 存储在一个 Segment Instance 中。数据通过复杂的 HASH 算法分布到所有 Segment Instance。HASH KEY(一个或者多个)由管理员在定义 Table 的时候指定。 GPDB 从底层上来说,通过一系列相关的独立 Database Instance 实现——1 个 Master Instance 和数个 Segment Instance。Master Instance 不存储用户数据。Segment Instance 存储每张表无重叠的部分数据(记录集合/Collection Of Rows)。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 8 - 解读 GP 分布策略 在 GPDB 中创建(Create)或者修改(Alter)表时,有一个附加的 DISTRIBUTED 子句用以 定义表的分布策略(Distribution Policy)。分布策略决定了表中的数据记录如何打散到 GP 的 Segment Instance。GPDB 提供了 2 种分布策略:  HASH 分布 使用 HASH 分布时,1 个或数个 Table Column 被用作 Distribution Key(简称 DK)。DK 被 HASH 算法用来决定每行记录对应特定的 Segmen Instance。相同 Key 值的记录会 HASH 到相同的 Segment Instance。选择一个唯一键(unique key)作为 DK,比如主键 (Primary Key),可以确保尽可能的平坦分布数据。  随机(Random)分布 使用随机分布,数据记录被循环的分布到 Segmen Instance。相同值的记录可能会 落在不同的 Segment Instance。随机分布可以确保数据分布的平坦性,但为了确保 性能优势应该尽可能的使用 HASH 分布。 第三章:GPDB 特性摘要 本章概述 GPDB 的系统要求及相关特征。包含以下论题:  GP SQL 标准一致性  GP 与 PostgreSQL 兼容性 GP SQL 标准一致性 SQL 语言在 1986 年首次由美国国家标准协会(ANSI)标准化。随后的版本由 ANSI 发布并 由国际标准化机构(ISO)标准化:SQL1989,SQL1992,SQL2003,SQL2006 及目前标准的 SQL2008。官方标准化名称为 ISO/IEC 9075-14:2008。通常新版的标准会增加一些特性, 偶尔也会有特性被建议废弃或者剔除。 值得一提的是,目前还没有一款商业数据库系统完全符合 SQL 标准。GPDB 是几乎完全 符合 SQL1992 标准,符合大部分的 SQL1999 标准。数个来自 SQL2003 标准的特性被实 现(尤其是大部分的 SQL OLAP 特性)。 本章重点阐述 GPDB 与 SQL 标准间的关系。GP 对 SQL 标准一对一的特性支持列表可参 见附录 L,”SQL2008 标准可选特性支持”。 核心 SQL 一致性 由于 GPDB 采用的是无共享(Shared-Nothing)架构,GPDB 的查询优化器对于一些 SQL 结构目前还没有实现。下面这些 SQL 结构是不被支持的: 1. 一些在 EXISTS 或 NOT EXISTS 子句中有返回值的子查询,其无法被 GP 的执行优 化器重写为 JOIN。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 9 - 2. 表 JOIN 且有子查询时的 UNION ALL 语法。 3. 在 FROM 作用的子查询中有记录集返回的 FUNCTION。 4. 反向滚动游标(CURSOR),包括 FETCH PRIOR、FETCH FIRST、FETCH ABOLUTE、 和 FETCH RELATIVE。 5. 在使用 CREATE TABLE 语句时,UNION 或者 PRIMARY KEY 子句必须包含所有 DK 列(有 DK)。出于这个限制,仅有一个 UNION 或者 PRIMARY KEY 子句被使用在 CREATE TABLE 语句中。而对于 DISTRIBUTED RANDOMLY 的表来说,是不允许使 用 UNION 和 PRIMARY KEY 子句的。 6. CREATE UNIQUE INDEX 子句未包含全部 DK 列。CREATE UNIQUE INDEX 不可用在 DISTRIBUTED RANDOMLY 表。 7. VOLATILE 或者 STABLE 的 FUNCTION 不能在 Segment Instance 得到执行,因此只 能将字面值作为参数传递给这些函数。 8. 触发器(Trigger)不被支持,虽然其依赖于 VOLATILE FUNCTION。 9. 外键(Foreign Key)的参考约束在 GPDB 中不被生效。用户仍可以定义外键,并 且这些信息也会保存在系统信息表中。 10. 序列(Sequence)操作函数 CURRVAL 和 LASTVAL。 11. DELETE WHERE CURRENT OF 和 UPDATE WHERE CURRENT OF(指定游标删除和更 新操作)。 SQL1992 一致性 以下的 SQL1992 标准特性在 GPDB 中不被支持: 1. NATIONAL CHARACTER(NCHAR)和 NATIONAL CHARACTER VARYING (NVARCHAR)。 用户可以使用 NCHAR 和 NVARCHAR 类型,不过在 GPDB 中他们只是作为 CHAR 和 VARCHAR 的同义词。 2. CREATE ASSERTION 子句。 3. INTERVAL 在 GPDB 中的支持与 SQL 标准中不一致。 4. GET DIAGNOSTICS 子句。 5. GRANT INSERT 或 UPDATE 列权限。在GPDB 中只能对 TABLE 对象进行权限授予, 而不能对其 COLUMN 进行权限操作。 6. GLOBAL TEMPORARY TABLE 和 LOCAL TEMPORARY TABLE。GP 的 TEMPORARY TABLE 与 SQL 标准不一致,但与多数的商业数据库相同。GP 的 TEMPORARY TABLE 与 Teradata 的 VOLATILE TABLE 是不同的。 7. UNIQUE 谓语(操作)。 8. 针对参考约束校验的 MATCH PARTIAL(可能以后也不会被实现)。 SQL1999 一致性 以下的 SQL1999 标准特性在 GPDB 中不被支持: 1. 大数据对象:BLOB,CLOB,NCLOB。不过在 GP 中可以使用 BYTEA 和 TEXT 列来 存储分厂大的对象。 2. 模块化(SQL 端的模块化,类似 Oracle 的 PLSQL)。 3. 创建存储过程(PROCEDURE)。不过在 GPDB 中可以通过创建返回值为 void 的函 数(FUNCTION)来实现同样的效果,调用的时候通过这样的方式即可: Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 10 - SELECT myfunc(args); 4. PostgreSQL/GP 的函数(FUNCTION)定义语言(PL/PGSQL)是 Oracle PL/SQL 的子集。 更像是 SQL/PSM 定义语言。GPDB 仍然支持通过 Python,Perl,Java 和 R 语言 来定义函数。 5. BIT 和 BIT VARYING 数据类型(故意遗漏)。其在 SQL2003 中被弃用,且在 SQL2008 中被取代。 6. GP 支持 63 个字符长度的对象标识符。SQL 标准要求支持 128 个字符长度的对 象标识符。 7. Prepared Transaction(PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED)。这意味着 GP 不支持分布式事务(XA Transaction)(数据库事务与外 部事务分两阶段提交)。 8. CHAR 和 VARCHAR 数据列上的字符集选项(CHARACTER SET)。 9. 在 CHAR 或者 VARCHAR 数据列上的 CHARACTERS 或 OCTETS (BYTES)方式的长 度说明。比如:VARCHAR(15 CHARACTERS)或者 VARCHAR(15 OCTETS)或者 VARCHAR(15 BYTES)。 10. CURRENT_SCHEMA 函数。 11. CREATE DISTINCT TYPE 语句。在 GP 中可以使用 CREATE DOMAIN 达到相同的效 果。 12. 显式表(没明白原文为 explicit table)。 SQL2003 一致性 以下的 SQL2003 标准特性在 GPDB 中不被支持: 1. MERGE 子句(Oracle 的典型 UPDATE INSERT 操作)。 2. 身份列(IDENTITY columns)和默认生成子句 GENERATED ALWAYS/GENERATED BY DEFAULT。序列(SERIAL)和大序列(BIGSERIAL)类似通过默认身份列(DEFAULT AS IDENTITY)生成的 INT 和 BIGINT。 3. 多重结果上的数据类型修改手段(MULTISET modifiers on data types)。 4. ROW 数据类型。 5. GPDB 使用非标准语法操作序列(SEQUENCE)。例如:在 SQL 标准中使用 NEXT VALUE FOR seq,而在 GPDB 中使用 nextval(‘seq’)。 6. GENERATED ALWAYS AS 列。VIEW 可以作为变通方案。(不知干什么用的)。 7. 在 SELECT 语句中的样本子句(TABLESAMPLE)。Random()函数可以作为从 table 获取随机样本函数的变通方案。比如:select * from pg_class order by random() limit 10。 8. 在SELECT语句中的NULLS FIRST或者NULLS LAST子句(确保空值优先或者最后)。 9. 分区 JOIN(在 JOIN 时使用 PARTITION BY)。 10. 对列进行 SELECT 授权。GPDB 中仅仅可以精确到对 Table 进行授权(GRANT)。 可以考虑使用视图(VIEW)作为变通方案。 11. 对于 CREATE TABLE x (LIKE(y))语句,GPDB 不支持以下子句: [INCLUDING|EXCLUDING] [DEFAULTS|CONSTRAINTS|INDEXES]。 12. GP 的数据类型近乎 SQL 标准相一致。通常不会存在任何的使用问题。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 11 - SQL2008 一致性 以下的 SQL2008 标准特性在 GPDB 中不被支持: 1. BINARY 和 VARBINARY 数据类型。 2. 在使用 SELECT 语句时 FETCH FIRST 或 FETCH NEXT 子句,例如: SELECT id, name FROM tab1 ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; GP 使用 LIMIT 和 LIMIT OFFSET 子句代替这种分页功能。 3. 在视图(VIEW)或者子查询(SUBQUERY)中 ORDER BY 子句是被忽略的,除非有 LIMIT 子句存在。这在 GPDB 中是被故意忽略的,理由是:GP 的优化器不知道 何时避免排序是安全的,因为排序会可能导致无法预期的资源开销。作为一种 变通方案,可以使用很大的 LIMIT 来强制 ORDER BY。比如:SELECT * FROM mytable ORDER BY 1 LIMIT 9999999999。 4. 不支持行子查询(ROW SUBQUERY)。 5. TRUNCATE TABLE 操作不支持 CONTINUE IDENTITY 和 RESTART IDENTITY 子句。 GP 与 PostgreSQL 兼容性 GPDB 是基于 PostgreSQL8.2 开发的,增加了一些 8.3 版本的新特性。为了支撑 GPDB 系 统的分布式特征和典型的工作负载,一些SQL命令会被增加或者修改,且少量PostgreSQL 特性会不被支持。同时 GP 也增加了一些 PostgreSQL 中没有的特性,比如物理数据分布, 并行查询优化,外部表,资源队列,表分区等。完全的 SQL 语法参考可查阅”SQL 命令 参考”。 第四章:GPDB 查询处理 用户可以像使用其他的 DBMS 一样向 GPDB 提交查询。直接通过客户端程序(例如 psql) 连接到 GP 的 Master 主机并提交查询语句。 理解查询规划与分发 查询被 Master 接收、处理、优化、创建一个并行的或者定向的执行计划(根据查询语句 决定)。之后 Master 将查询计划分发到 Segment 节点去执行,每个 Segment 只负责处理 自己本地的那部分数据。 大部分的操作—比如扫表、关联、聚合、排序都是同时在 Segment 节点上并行执行的。 每个操作都独立于其他 Segment 执行。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 12 - 一些特定的语句可能只使用一个 Segment,比如单行的 INSERT、UPDATE、DELETE 或者 SELECT 操作,还有就是一些直接过滤 DK 列的查询。这些语句不会被分发到全部 Segment, 而是定向到包含该 DK 列的 Segment。 理解查询计划 执行计划是 GPDB 的一系列为了生成响应给定查询结果的一系列操作。执行计划的每一 步代表着 DB 操作,比如:扫表、关联、聚合、排序等。执行计划被从下向上执行。与 典型的 DB 操作不同的是,GPDB 有一个特有的操作:移动(motion)。移动操作(motion) 涉及到查询处理期间在 Segment 之间移动数据。不过并非所有的查询都需要移动数据。 比如针对系统日志表(在 Master 上)的查询是不会涉及通过网络层移动数据。 为了最大限度的实现并行化处理,GP 会将查询计划分割为多个步骤。步骤是执行计划 的一部分,其可以在独立的 Segment 上执行。当执行计划需要移动数据时,执行计划 会被分割开,两个操作分裂在数据移动步骤两侧,即:先执行一步操作,再执行数据移 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 13 - 动,再执行下一步操作。 例如,下面两个 table 的关联查询: SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE dateCol = '04-30-2008'; 下图表明了执行计划。每个 Segment 获取到查询计划的副本且并行执行。对于该执行 计划来说,有一个重分布操作(Redistribute motion),其是为了完成连接(join)而执行的数 据移动操作。执行计划被重分布操作划到两侧(slice 1 与 slice 2)。该查询计划还有另外 一种数据移动称为汇总动作(Gather motion)。汇总动作是 Segment 将计算结果反馈到 Master 从而可以反馈给客户端的一种操作。由于在移动动作发生时执行计划总是会被 分割,该执行计划还存在一个隐含的操作(slice 3)。不是所有的查询都有汇总操作,比 如:CREATE TABLE x AS SELECT...语句是不需要汇总操作的。 理解并行执行 GP 会 创 建 多 个 DB 进 程 来 处 理 查 询 。 在 Master 上 的 称 为 执 行 分 发 器 (Query Dispatcher/QD)。QD 负责创建、分发执行计划,汇总呈现最终结果。在 Segment 上,处 理进程被称为查询执行器(Query executor/QE)。QE 负责完成自身部分的处理工作以及与 其他处理进程之间传递中间结果。 执行计划的每个处理部分都至少涉及一个处理工作。执行进程只处理属于自己部分的工 作。在查询执行期间,每个 Segment 会并行的执行一系列的处理工作。 同一部分相关的处理工作称为簇(原文为 gangs)。在一部分处理完成后,数据将从当前 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 14 - 处理向下传递,直到执行计划完成。Segment 之间的通信涉及到 GPDB 的网络层组件。 下图显示查询处理如何在 Master 和 2 个 Segment 之间被逐步执行的。 第五章:角色权限管理 GPDB 通过角色(Role)的概念来管理数据库的访问权限。Role 的概念包含两个子概念用户 (User)和组(Group)。一个 Role 可以是一个 DB User 或者一个 Group 或者两者兼备。Role 可以拥有 DB 对象(比如 Table)并可以分配该对象的权限给其他 Role 从而实现对该对象的 控制。Role 还可以成为其他 Role 的成员,因此 Role 可以继承其父级 Role 的对象权限。 每个 GPDB 系统都包含一系列的 Role(User 或 Group)。这些 Role 与运行在 OS 上的 Role 是没有关系的。但出于便利考虑,可以选择使用与 OS Role 相关联的 GPDB Role,这样 对于一些默认使用 OS User 名称作为 DB User 的应用来说会比较方便。 在 GPDB 中 User 通过 Master 登录和认证。而对于 Segment 的访问是 Master 通过后台操 作实现,与当前登录的 User 信息无关。 Role 是定义在 GPDB 系统级别的,这就意味着,在其所在系统的全部 DB 实例中都是有 效的。 为了能够启动 GPDB 系统,在初始化系统时会自动包含一个 SuperRole,该 Role 与执行 该初始化操作的 OS User 相关。该 Role 与该 OS User 具有相同的 Name。习惯上这个 Role 使用 gpadmin。为了能够创建其他 Role,一开始需要使用该初始化 Role。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 15 - 角色与权限安全的最佳实现  保护系统 User gpadmin。GP 需要使用一个 UNIX 的 User ID 来安装和初始化 GPDB 系统。在 GP 文档中该系统 User ID 被称为 gpadmin。gpadmin 用户作为 GPDB 系统的默 认 Super User,同时是 GP 安装目录及相关数据文件的 Owner。默认管理员账户是 GPDB 的基础,如果没有该账户将无法运行,且没有办法限制 gpadmin 用户的访问。gpadmin 用户可以绕过 GPDB 的所有安全限制。任何人通过 gpadmin 登录到 GP 主机后,都可以 Read、Alter、Delete 任何数据,包括系统日志表的访问和 DB 操作。因此,保护好 gpadmin 用户账号是很重要的。超级用户(gpadmin)只应该用以执行特定的系统管理任务(比如升 级、扩展等)。DB User 不应该使用 gpadmin 账号,ETL 和生产系统也不应该使用 gpadmin 账号。  为每个登录的 User 分配不同的 Role。出于登录和审计的目的,每个被允许登录到 GPDB 的 User 都应该分配属于自己的 Role。对于应用程序(APP)或者 Web Service 来说, 应该考虑为每个 APP 或者 Service 建立独立的 Role。  使用 Group 来管理访问权限。  控制具备 SUPERUSER 属性的 User 数量。具有 SUPERUSER 属性的 Role 将可以像 gpadmin 那样绕过 GPDB 的所有安全限制,包括资源队列(Resource Queue/RQ)。应该紧 为系统管理员分配 SUPERUSER 权限。 创建用户 User Role User Role 意味着其可以登录 DB 并发起 DB 会话。因此,在持用 CREATE ROLE 来创建一 个 User 时,需要指定 LOGIN 权限。比如: =# CREATE ROLE jsmith WITH LOGIN; Role 可能还需要伴随着一系列的属性从而决定其可以执行哪些 DB 任务。可以在 CREATE ROLE 的时候指定这些属性,也可以在 CREATE 之后使用 ALTER ROLE 命令来完成。 ALTER ROLE 属性 属性 描述 SUPERUSER | NOSUPERUSER SUPERUSER 可以绕过所有所有权限限 制,SUPERUSER 使用有风险,请仅在 需要的时候使用。CREATE ROLE 时默 认属性为 NOSUPERUSER CREATEROLE | NOCREATEROLE 是否具备 CREATE DATABASE 权限。默 认为 NOCREATEROLE。 CREATEROLE | NOCREATEROLE 是否有 CREATE 和管理其他 ROLE 的权 限,默认为 NOCREATEROLE。 INHERIT | NOINHERIT 决定权限是否被其子成员继承。默认 属性为 INHERIT。 LOGIN | NOLOGIN 决定 ROLE 是否可以登录 DB。具备 LOGIN 属性的 ROLE 就是 USER。不具 备 LOGIN 属性的 ROLE 往往用来做权 限管理(GROUP)。默认值为 NOLOGIN。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 16 - CONNECTION LIMIT connlimit 对于可以 LOG IN 的 ROLE 来说,决定 其最多可同时有多少个连接。默认值 为-1(无限制)。 PASSWORD ‘password’ 设置 ROLE 的 PASSWORD。如果不打 算使用密码登录,可忽略该属性,如 果不指定密码,PASSWORD 会被设置 为 NULL 并且始终无法登录。空密码 可以明确定义 PASSWORD NULL。 ENCRYPTED | UNENCRYPTED 指定密码是否加密,默认行为受 password_encryption 参数决定(默认 为 MD5)。如果目前的密码已经使用 了加密存储,就忽略该属性。 VALID UNTIL ‘timestamp’ 设置在指定的日期后该 ROLE 的密码 失效。不设置的情况下为永远有效。 RESOURCE QUEUE queue_name 将 ROLE 分配到执行的资源队列(RQ)。 所有的语句都受到该 RQ 的约束。需 要注意的是该属性不会被继承,必须 为每个 USER 指定该属性。默认的是 pg_default。 DENY {deny_interval | deny_point} 定义允许登录的时间片段。 比如下面的例子: =# ALTER ROLE jsmith WITH PASSWORD 'passwd123'; =# ALTER ROLE admin VALID UNTIL 'infinity'; =# ALTER ROLE jsmith LOGIN; =# ALTER ROLE jsmith RESOURCE QUEUE adhoc; =# ALTER ROLE jsmith DENY DAY 'Sunday'; 除了上述的一些定义属性外,ROLE 还可以配置一些 Server 层面的属性值,比如设 置默认的搜索路径: =# ALTER ROLE admin SET search_path TO myschema, public; 创建组 Group Role 对于管理一组 User 来说,将它们绑定到一个 Group 是很方便的。通过这种方式,一组 User 可以通过一个 Group 来统一赋权限和收回权限。在 GPDB 中,通过 CREATE ROLE 的 方式来创建 GROUP,并通过 GRANT MEMBERSHIP 的方式为 USER 分组。 通过 CREATE ROLE 命令创建新的 GROUP ROLE: =# CREATE ROLE admin CREATEROLE CREATEDB; 一旦 GROUP 创建好了,就可以通过 GRANT 和 REVOKE 添加或者删除 Member(USER ROLE) 了: =# GRANT admin TO john, sally; =# REVOKE admin FROM bob; 为了合理的管理对象权限,需要将合适的权限赋予 GROUP ROLE。因为其所有的 USER ROLE 成员都会继承该 GROUP ROLE 的对象权限。比如: Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 17 - =# GRANT ALL ON TABLE mytable TO admin; =# GRANT ALL ON SCHEMA myschema TO admin; =# GRANT ALL ON DATABASE mydb TO admin; 不过,对于 ROLE 的 LOGIN、SUPERUSER、CREATEDB 和 CREATEROLE 属性是不会像普通 的权限一样被继承,需要被明确指定。USER 成员需要明确的使用 SET ROLE 来明确使用 其 GROUP 的这些属性。比如,admin ROLE 具备 CREATEDB 和 CREATEROLE 属性,sally 是 admin 的成员,其可以通过下面的语句来获取 admin ROLE 的属性: => SET ROLE admin; 管理对象权限 当一个对象(Table、View、Sequence、Database、Function、Language、Schema、Tablespace) 被创建时,其就会被分配到一个所有者(Owner)。通常 Owner 是执行了该创建语句的 User。 对于大多数(Object)的对象来说,默认只有其 Owner(或者 SUPERUSER)可以对其做任何操 作。要允许其他 User 使用该对象,需要使用 Grant。GPDB 对于每种对象支持的权限为: 对象类型 权限 Tables、Views、Sequences SELECT INSERT UPDATE DELETE RULE ALL External Tables SELECT RULE ALL Databases CONNECT CREATE TEMPORARY | TEMP ALL Functions EXECUTE Procedural Languages USAGE Schemas CREATE USAGE ALL 注意:每个对象的权限必须被独立的授权。比如:把一个 DB 的 ALL 授权不等于把 该 DB 的所有对象都授权了。这仅仅是授权了 DB 自身的全部权限(CONNECT、CREATE、 TEMPORARY)。 使用 GRANT SQL 命令给指定的 Role 授权一个对象权限。比如: =# GRANT INSERT ON mytable TO jsmith; 还可以功过 DROP OWNED 和 REASSIGN OWNED 命令来取消 Role 的 Owner 权限(只有该 对象的 Owner 或者 SUPERUSER 可以执行这样的操作)。例如: =# REASSIGN OWNED BY sally TO bob; =# DROP OWNED BY visitor; Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 18 - 模拟 Row 或者 Column 级别的权限控制 GPDB 本身不支持 Row 和 Column 级别的访问权限控制,而只是支持对象(Object) 级别的权限控制。 Row 和 Column 级别的访问控制可以通过 View 的方式来模拟。Row 级别的访问控 制可以通过添加一个 Column 的形势来存储敏感信息,使用基于该 Column 的 View 来控制访问的 Row,再将该 View 授权给相应的 User。Column 的访问控制也可以通 过选择一系列的 Column 作为 View 授权给响应的 User 来模拟。 数据加密 PostgreSQL 提供了一个加密/解密函数包叫做 pgcrypto,其同样可以在 GPDB 中安装使用。 默认情况下GPDB 是不安装 pgcrypto 包的,不过可以在 EMC Download Center 下载该包。 并使用 GP 的包管理工具(gppkg)在分布集群上安装 pgcrypto。 pgcrypto 允许 DB 管理员将 Table 特定的 Column 存储为加密格式。在为敏感数据增加了 进一步的加密处理之后,存储在 GPDB 中的数据是无法被没有密钥的用户访问的,并且 无法直接从磁盘访问加密后的数据文件。 值得注意的是,pgcrypto 函数运行在 DB Server 内部。数据的密码在 pgcrypto 之间和 Client App 之间可能会通过明文传输。为了安全期间,可以考虑在 Client 和 GP Master 之间使 用 SSL 安全连接。 密码加密 在 GPDB4.2.1 版本之前,密码默认使用 MD5 进行加密。由于诸多用户要求更高级别的 加密算法(Federal Information Processing Standard140-2),从 4.2.1 版本开始,GPDB 开 始使用 SHA-256 加密。 基于时间的登录认证 GPDB 允许管理员将 Role 的访问限制在一定的时间段内。使用 CREATE ROLE 或者 ALTER ROLE 命令来执行基于时间的限制。 访问限制可以控制到具体时间点,且约束的改变不需要删除或者重建 ROLE。为权限的 管理提高了灵活性。 时间约束仅仅对于设置的 Role 有效。如果一个 Role 包含在另一个受时间约束的 Role, 该时间约束是不会被继承的。 时间约束仅仅是在 LOGIN 的时候奏效。SET ROLE 和 SET SESSION AUTHORIZATION 命令对 于时间约束不受任何影响,也就是说,即便执行了这些语句也无法继承时间约束设置。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 19 - 需要的权限 要设置时间约束限制,SUPERUSER 或者 CREATEROLE 权限是必须的。另外没有任何 User 可以给 SUPERUSER 设置时间约束。 如何添加时间约束 有两种办法添加时间约束。在 CREATE ROLE 或者 ALTER ROLE 的时候使用 DENY 关键 字并跟随如下的命令来实现:  某天或者某个时间访问限制,比如:no access on Wednesdays。  一个有开始时间和结束时间的访问限制,比如:no access from Wednesday 10 p.m. through Thursday at 8 a.m. 还可以指定多个限制,比如:no access Wednesdays at any time and no access on Fridays between 3:00 p.m. and 5:00 p.m. 指明日期和时间 有两种方法指明哪一天。使用 DAY 关键字并紧跟英文的星期几或者 0~6 的数字, 如下表所示: 英文表述 数字表述 DAY ‘Sunday’ DAY 0 DAY ‘Monday’ DAY 1 DAY ‘Tuesday’ DAY 2 DAY ‘Wednesday’ DAY 3 DAY ‘Thursday’ DAY 4 DAY ‘Friday’ DAY 5 DAY ‘Saturday’ DAY 6 每日中的时间可以使用 12 小时或者 24 小时格式。在 TIME 关键字之后跟随单引号 引起来的时间格式。仅仅含有小时和分钟的时间即可,且使用冒号(:)作为分隔符。 如果使用 12 小时格式,需要指定 AM 或者 PM 来结尾以确定上下午。下面的例子 表明了几种时间格式: TIME ’14:00’ (24 小时格式的时间) TIME ’02:00 PM’ (12 消失格式的时间) TIME ’02:00’ (24 消失格式的时间) 其等价于 TIME ’02:00 AM’ 注意:时间约束是强制以服务器时间为基准的。时区信息被忽略。 指定时间间隔 要指定限制访问的时间间隔,需要两个[日期/时间]来确定,且通过 BETWEEN 和 AND 关键字连接。DAY 是必须的。 BETWEEN DAY ‘Monday’ AND DAY ‘Tuesday’ BETWEEN DAY ‘Monday’ TIME ’00:00’ AND DAY ‘Monday’ TIME ’01:00’ BETWEEN DAY ‘Monday’ TIME ’12:00 AM’ AND DAY ‘Tuesday’ TIME ’02:00 AM’ BETWEEN DAY ‘Monday’ TIME ’00:00’ AND DAY ‘Tuesday’ TIME ’02:00’ BETWEEN DAY 1 TIME ’00:00’ AND DAY 2 TIME ’02:00’ 最后 3 句是等价的。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 20 - 注意:日期间隔不能跨国 Saturday(周六)。 Incorrect:DENY BETWEEN DAY ‘Saturday’ AND DAY ‘Sunday’ 正确语法为: DENY DAY ‘Saturday’ DENY DAY ‘Sunday’ 例子: 下面的例子说明在 CREATE ROLE 和 ALTER ROLE 的时候使用时间约束。这里只是展 示了时间约束部分的命令。关于 CREATE ROLE 和 ALTER ROLE 的细节可参考相关附 录。 例 1-创建包含时间约束的 ROLE,限制周末访问。 CREATE ROLE generaluser DENY DAY ‘Saturday’ DENY DAY ‘Sunday’… 例 2-修改 ROLE 添加时间约束,每天晚上 2:00 到 4:00 限制访问。 ALTER ROLE generaluser DENY BETWEEN DAY ‘Monday’ TIME ’02:00’ AND DAY ‘Monday’ TIME ’04:00’ DENY BETWEEN DAY ‘Tuesday’ TIME ’02:00’ AND DAY ‘Tuesday’ TIME ’04:00’ DENY BETWEEN DAY ‘Wednesday’ TIME ’02:00’ AND DAY ‘Wednesday’ TIME ’04:00’ DENY BETWEEN DAY ‘Thursday’ TIME ’02:00’ AND DAY ‘Thursday’ TIME ’04:00’ DENY BETWEEN DAY ‘Friday’ TIME ’02:00’ AND DAY ‘Friday’ TIME ’04:00’ DENY BETWEEN DAY ‘Saturday’ TIME ’02:00’ AND DAY ‘Saturday’ TIME ’04:00’ DENY BETWEEN DAY ‘Sunday’ TIME ’02:00’ AND DAY ‘Sunday’ TIME ’04:00’ 例 3-修改 ROLE 添加时间约束,周三或者周五下午 3:00 到 5:00 限制访问。 ALTER ROLE generaluser DENY BETWEEN DAY ‘Wednesday’ DENY BETWEEN DAY ‘Friday’ TIME ’15:00’ AND DAY ‘Friday’ TIME ’17:00’ 删除时间约束 要删除时间约束,可使用 ALTER ROLE 命令。跟着 DROP DENY FOR,并 跟日期/时间。 DROP DENY FOR DAY ‘Sunday’ 任何与该条件有交集的约束都会被移除。比如存在的约束为 BETWEEN DAY ‘Monday’ AND DAY ‘Tuesday’。那么删除’Monday’限制时会移除整个限制。原则是有交集即移 除。例如: ALTER ROLE generaluser DROp DENY FOR DAY ‘Monday’… 第六章:配置客户端认证 在 GPDB 系统初始化之后,系统包含一个预定义的 SUPERUSER ROLE。该 USER 的 USER NAME 与初始化 GPDB 系统的 OS USER 同名。该 ROLE 被称为 gpadmin。默认情况下,系 统会被设置为只允许 gpadmin 从本地连接。为了让其他 ROLE 可以连接,或者允许从远 程 HOST 主机连接,必须配置 GPDB 来允许这些连接。该章节介绍如何配置客户端连接 和 GPDB 认证。  允许连接到 GPDB  限制并发连接 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 21 - 允许连接到 GPDB 客户端的访问认证是通过一个叫做 pg_hba.conf(也是标准的 PostgreSQL 的认证文件)的 配置文件来控制的。关于该文件的细节可以参考 PostgreSQL 的文档。 在 GPDB 中,Master 的 pg_hba.conf 文件控制着客户端连接到 GPDB 系统的认证。在 Segment 上也存在 pg_hba.conf 文件,通常该文件已经被正确配置为允许从 Master 访问。 不过就译者的经验来说,也出现过其配置错误的情况,该情况会导致 gpexpand 或 gp_dump 之类的操作出错并失败。通常来说,Segment 是不需要接受外部客户端连接的, 不太有必要修改其 pg_hba.conf 文件。 pg_hba.conf 是包含每行一条记录的平面文件。空行被忽略,任何在井号(#)后的符号串 都会被忽略。每行记录由一系列 Space 和 Tab 混合的分割的字段组成。如果需要在字段 中出现空白字符,需要将字段用引号引起来。记录都不可跨行。每个远程客户端访问权 限记录都像这种格式: host database role CIDR-address authentication-method 而每个 UNIX 嵌套连接的访问权限记录像这种格式: local database role authentication-method 这些字段的含义如下: 字段 描述 local 匹配 UNIX 嵌套连接。如果没有这种记录,UNIX 嵌套连接是不被允许的。 host 匹配 TCP/IP 方式的连接。除非该 Server 属于一个合适的 IP 段,否则其访问 是不被允许的。 hostssl 匹配 TCP/IP 方式的 SSL 加密连接。这个配置需要配合 SSL 参数的设置,该参 数在 GPDB 启动时生效。 hostnossl 匹配 TCP/IP 方式的非 SSL 加密连接。 database 设置该记录匹配的 DB Name。all 可以匹配全部 DB。多个 DB Name 可以使 用逗号(,)分割。或者使用@符号跟随文件名的方式指定,该文件包含需要匹 配的 DB Name。 role 匹配那个 ROLE。all 可以匹配全部的 ROLE。如果想把一个 GROUP 的所有成 员匹配上,可以在 ROLE Name 前使用加号(+)表示。多个 ROLE Name 可以使 用逗号(,)分割。或者使用@符号跟随文件名的方式指定,该文件包含需要匹 配的 ROLE Name。 CIDR-address 指定该记录匹配的客户端 IP 地址范围。其包含一个标准的逗号分割 IP 地址 和一个掩码长度值。IP 地址只能使用数字形势,不可以使用域名或者 Hostname。掩码长度表示 IP 地址高位与客户端 IP 匹配的长度。指定的掩码 长度右边的二进制 IP 地址位必须是 0。IP 地址与分隔符(/)和掩码长度之间 不可以有任何的空字符。比如 172.20.143.89/32。其只能匹配 172.20.143.89IP 地址。172.20.143.0/24 可以匹配 172.20.143 开始的任何 IP 地址。要匹配单 个 IP 地址 IPv4 使用 32 作为掩码长度,IPv6 使用 128 作为掩码长度。 IP-address IP-mask 通过标准子网掩码的格式作为掩码长度的可选方案。其被作为一个单独的 字段。255.0.0.0 等效于 IPv4 的 8 位掩码长度。255.255.255.255 等效于 IPv4 的 32 位掩码长度。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 22 - authentication-method 指定连接时使用的认证方法。比如 trust 为不需要密码,md5 为使用 md5 加密认证。更多细节可以查看 PostgreSQL8.4 的文档中认证方法的部分。 编辑 pg_hba.con 文件 下面的例子展示如何编辑 Master 上的 pg_hba.con 文件从而允许远程的客户端通过 加密认证的方式访问数据库。 编辑 pg_hba.con 文件 1. 使用文本编辑器(比如 VI)打开$MASTER_DATA_DIRECTORY/pg_hba.conf 文件。 2. 为每类需要允许的连接添加一行记录。记录是被顺序读取的,所有记录的顺序 是有象征意义的。通常前面的记录匹配更少的连接但要求较弱的认证,后面的 记录匹配更多的连接但要求更严格的认证。比如: # allow the gpadmin user local access to all databases # using ident authentication local all gpadmin ident sameuser host all gpadmin 127.0.0.1/32 ident host all gpadmin ::1/128 ident # allow the 'dba' role access to any database from any # host with IP address 192.168.x.x and use md5 encrypted # passwords to authenticate the user # Note that to use SHA-256 encryption, replace md5 with # password in the line below host all dba 192.168.0.0/32 md5 # allow all roles access to any database from any # host and use ldap to authenticate the user. Greenplum role # names must match the LDAP common name. host all all 192.168.0.0/32 ldap ldapserver=usldap1 ldapport=1389 ldapprefix="cn=" ldapsuffix=",ou=People,dc=company,dc=com" 3. 保存并关闭文件 4. 重新加载 pg_hba.conf 文件从而使得刚刚的修改生效: $ gpstop –u 限制并发连接 为了限制对 GPDB 系统的并发访问,可以通过配置 Server 参数 max_connections 来实现。 这是一个本地化参数,就是说,需要把 Master,Standby 以及所有的 Segment Instance 都修改该参数。通常建议 Segment 的值是 Master 的 5-10 倍,不过这个规律并非总是如 此,在 max_connections 比较大的时候通常没有这么高的倍数,2-3 倍也是允许的,但 无论如何 Segment 的值不能小于 Master。在设置 max_connections 时,其依赖的参数 max_prepared_transactions 参数也需要修改,该参数的值至少要和 Master 上的 max_connections 值一样大,另外 Segment 上的值需与 Master 相同。 例如: 在$MASTER_DATA_DIRECTORY/postgresql.conf(包括 Standby Master): max_connections=100 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 23 - max_prepared_transactions=100 在所有的 Segment Instance 上 SEGMENT_DATA_DIRECTORY/postgresql.conf: max_connections=500 max_prepared_transactions=100 修改最大连接数 1. 停掉 GPDB 系统: $ gpstop 2. 在 Master 上编辑$MASTER_DATA_DIRECTORY/postgresql.conf 文件并修改下面两 个参数: max_connections (允许连接的数量+superuser_reserved_connections) max_prepared_transactions (大于或等于max_connections) 3. 在每个 Segment Instance 上编辑 SEGMENT_DATA_DIRECTORY/postgresql.conf 文 件并修改下面两个参数: max_connections (建议5-10倍Master上的值) max_prepared_transactions (大于等于Master上的值) 4. 重启 GPDB 系统 $ gpstart 注意:增加该参数可能需要更多的共享内存。为了降低影响,可以考虑减少其他内 存相关的参数,比如 gp_cached_segworkers_threshold。 客户端/服务端间的加密连接 GPDB 原生支持客户端与 Master 服务端之间的 SSL 连接。SSL 连接可以有效的防止第三 方对包的窥探,防止中间层的攻击。在非安全连接环境中有必要使用 SSL,且在使用权 限认证时更为必要。使用 SSL 需要在客户端和 Master 端都安装有 OpenSSL。GP 在将设 置参数 ssl=on(在 Master 的 postgresql.conf 文件)后启动就开启了 SSL。在使用 SSL 模式启 动时,GP 会查找 Master 目录下的 server.key(服务器密钥)文件和 server.crt(服务器证书) 文件。这些文件比如被正确的安装,否则 GP 系统将无法启动。 重要提示:不要为 server.key 设置访问口令。GP 不会为密钥提示输入口令,这样会导致 出错并无法启动 DB 系统。 关于如何安装 OpenSSL,这里不做翻译解释,可参考源英文手册或相关文档。通常 GP 都是作为后台 OLAP 库部署在安全的网络环境,不太有必要开启 SSL。 第七章:访问数据库 本章解释可以使用哪些客户端工具连接 GPDB,并如何建立一个数据库会话:  建立数据库会话  支持的客户端应用  连接故障排除 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 24 - 建立数据库会话 用户可以使用任何 PostgreSQL 兼容的客户端程序连接到 GPDB,比如 psql。用户或者管 理员总是通过 Master 连接到 GPDB,Segment 不能使用客户端连接。 要连接到 GPDB 的 Master,需要知道下面这些连接参数并在客户端程序配置正确。 连接参数 描述 环境变量 Application name 连接到数据库的应用名称 $PGAPPNAME Database name 需要连接的数据库名称。对于新初始化的系统来说,首次 可以使用 template1。 $PGDATABASE Host name 要连接的 GPDB Master 的主机名称。默认为 localhost。 $PGHOST Port GPDB Master 上 Instance 的端口号。默认为 5432. $PGPORT User name 要连接的用户名。其没必要与 OS 的 User Name 相匹配。在 不知道 User Name 的情况下最好联系询问 GP 管理员。每个 GPDB 系统都有一个初始化时产生的 SUPERUSER。该 User Name 与初始化的 OS User Name 相同(通常为 gpadmin)。 $PGUSER 支持的客户端应用 可以使用这些客户端应用连接 GPDB:  在 GPDB 安装时提供的客户端应用。psql 提供了交互式的命令行访问 GPDB。  针对GPDB的 pgAdminIII作为 一种强化版本支持GP。从1.10.0版本开始,PostgreSQL 工具 pgAdminIII 开始支持 GP 特性。安装包可以从 pgAdmin 网站下载。  使用标准数据库应用接口,比如 ODBC、JDBC,用户可以开发出自己的客户端程序。 由于 GPDB 机遇 PostgreSQL 而来,可以直接使用 PostgreSQL 的驱动访问 GPDB。  诸多使用标准数据库应用接口,比如 ODBC、JDBC 的客户端程序,可以通过配置的 方式连接到 GPDB。 GPDB 的客户端应用程序 在 GPDB 安装时在 Master 主机的$GPHOME/bin 下有一系列的客户端应用程序。下 面是一些常用的客户端应用程序: 名称 用途 createdb 创建新的数据库 createlang 创建新的程序语言 createuser 创建新的数据库 ROLE dropdb 删除数据库 droplang 删除程序语言 psql PostgreSQL 交互式终端 reindexdb 将数据库重建索引 vacuumdb 收集数据库的磁盘空间并分析数据库 在使用这些客户端应用程序时,必须通过GP Master 实例。还必须知道目标DB Name, Host Name,Port 以及连接使用的 User Name。这些参数在命令行可以分别使用-d、 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 25 - -h、-p 和-U 提供。没有指定任何选项的参数会优先被解读为 DB Name。 那些有缺省值的参数可以不指定。缺省的 Host Name 是 localhost。缺省的 Port 是 5432。缺省的 User Name 是当前的 OS User Name,会被当作缺省的 DB Name。GPDB 的 User Name 与 OS User Name 未必相同。 如果缺省参数值是错误的,可以选择将正确的值保存在环境变量 PGDATABASE、 PGHOST、PGPORT、PGUSER 中。在~/.pgpass 中设置合适的值可以免除反复的密码 输入的麻烦。 使用 psql 连接 根据缺省值或者环境变量,下面的例子说明如何通过 psql 连接: $ psql -d gpdatabase -h master_host -p 5432 -U gpadmin $ psql gpdatabase $ psql 如果还没有用户数据库存在,可以连接系统数据库 template1。例如: $ psql template1 在成功连接到数据库之后,psql 会出现一个提示符,包含连接的 DB Name 和 一串字符(=>)(或者=#,紧当该用户是 SUPERUSER 时)。例如: gpdatabase=> 在提示符处,可以输入 SQL 命令执行了。SQL 命令必须已分号(;)结尾才能将 命令发给 Master。比如: => SELECT * FROM mytable; 要得到关于 psql 客户端应用程序的更多信息,可以查看相关附录章节。 针对 GPDB 的 pgAdminIII 如果更喜欢图形化界面(有谁不喜欢吗),可以使用针对 GPDB 的 pgAdminIII。该 GUI 客户端除了支持标准 PostgreSQL 外,还支持一些 GP 的转由特性。 针对 GPDB 的 pgAdminIII 支持下列的 GP 转由特性:  外部表(External tabls)  只读表(Append-only table),压缩只读表(Compressed append-only table)  图形化的解释器(EPLAIN ANALYZE)  GP Server 的参数配置 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 26 - 安装针对 GPDB 的 pgAdminIII 该安装包可以从 pgAdminIII 的官方网站(http://www.pgadmin.org)下载。安装包 中包含安装说明。其实和普通的 Windows 安装文件一样,就是诸多的 Next 和 选择安装位置等。 针对 GPDB 的 pgAdminIII 文档 普通的帮助信息可以在 Help>Help contents 菜单中查看。 关于 GP 方面的 SQL 帮助,可以在点击 Help>Greenplum Database Help 菜单 查看,在联网状态下,将会自动跳转到 Greenplum SQL 参考文档,只是在 Greenplum 被 EMC 整合后这个在线帮助已经打不开了。另外还可以安装 GP 客 户端工具包,其包含了 pgAdminIII 连接到的参考文档。 使用 pgAdminIII 执行管理操作 该节介绍诸多 GPDB 管理操作中的两个重要部分:编辑服务器配置,图形化查 看执行计划。 编辑服务器配置 pgAdminIII提供了两种修改Server的配置文件postgresql.conf的方式:本地, 通过File菜单,远程,通过Tools菜单。修改远程参数的方便之处在于不需 要将postgresql.conf文件再上传到Server端。 远程编辑远程服配置 1. 连接到需要修改的Server。如果连接了多个Server,要确保已经选中需 要修改的Server。 2. 选择Tools>Server Configuration>postgresql.conf菜单。配置信息将会 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 27 - 已列表的形势打开。 3. 双击需要修改的参数打开一个参数设置对话框。 4. 输入新的参数值或者[选择/取消]Enable。修改好之后点击OK按钮。 5. 如果修改的参数可以通过重新加载配置的方式生效,点击绿色的重载 按钮即可。有些参数修改是需要GP完全重新启动才生效的。 图形化查看执行计划 使用pgAdminIII工具,可以通过执行EXPLAIN命令查看查询计划。输出内 容包括GP的分布式查询处理如计划分割和Segment之间数据移动。还可以 查看图形化的执行计划。 查看图形化的执行计划 1. 在正确的数据库连接下,选择Tools>Query Tools。 2. 使用SQL编辑器输入查询语句,还可以通过图形化对象编辑器或者打 开一个SQL文件的方式。 3. 选择Query>Explain Option确认下面的选项:  Verbose-如果想查看图形化的执行计划,需要取消该选项  Analyze-如果你想在查看执行计划时就直接执行查询语句的话可 以选择该选项 4. 点击查询面板上端的执行计划按钮,或者选择Query>Explain。 查询计划在屏幕的底部展现。比如: DB 应用程序接口 若需要开发针对GPDB的应用程序,PostgreSQL提供的一些通用的API同样可以 应用在GPDB上。这些驱动包并没有与GPDB一起发布,而是一些独立的 PostgreSQL项目,需要单独下载和安装配置从而连接GPDB。下面这些驱动可 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 28 - 以获取: API PostgreSQL Driver 下载连接 ODBC pgodbc 在 GPDB 的 Connectivity 包中有。可以在 EMC 下载中心下载。没有 EMC 的 Powerlink 账号等于没用。 JDBC pgjdbc 在 GPDB 的 Connectivity 包中有。可以在 EMC 下载中心下载。没有 EMC 的 Powerlink 账号等于没用。 Perl DBI pgperl http://gborg.postgresql.org/project/pgperl Python DBI pygresql http://www.pygresql.org 使用通用API来访问GPDB的说明: 1. 下载相应的语言平台和对应的API。比如下载JDK和JDBC。 2. 编写相应的程序连接GPDB。需要注意SQL的语法支持问题。 下载合适的PostgreSQL驱动并配置到GPDB Master Instance的连接。GP提供了 一个客户端包,其包含了支持的GPDB驱动。可以在EMC下载中心下载。没有 EMC的Powerlink账号等于没用。 第三方客户端工具 很多第三方的ETL和BI工具使用标准的API如ODBC、JDBC,都可以配置连接 到GPDB。下面这些工具经过用户证实可以很好的协同GP一同工作:  Business Objects  Microstrategy  Informatica Power Center  Microsoft SQL Server Integration Services (SSIS) and Reporting Services (SSRS)  Ascential Datastage  SAS  Cognos GP专业服务可以协助用户配置他们选定的第三方工具协同GP工作。 连接故障排除 有很多导致客户端程序无法成功连接GPDB的原因。本节介绍一些常见的问题并说 明如何解决这些问题。 问题 解决办法 No pg_hba.conf entry for host or user 要允许远程客户端连接到 GPDB,必须正确的配置 GPDB Master Instance 的 pg_hba.conf 文件。参见”允许连接到 GPDB”。 Greenplum Database is not running 在 GPDB Master Instance 宕掉的情况下,用户是无法连接的。可以通过在 GP Master 上使用 gpstate 工具检查 GPDB 系统是否启动。 Network problems Interconnect timeouts 从远程连接 GP Master 时,网络问题可能会妨碍连接,比如 DNS 解析错误。要排 除这种问题可先 ping Master 主机,先确保需要连接的主机可以 ping 通 Master。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 29 - 另外需要分清 localhost 与实际的 Host Name。当然有时候还存在网络防火墙的问 题,这种情况下,可以 ping 通 Master,在 Master 主机通过 psql 可以连接数据库, 但从客户端连接 Master 无法连接,此时应该找相关的网络管理人员了。 Too many clients already 缺省情况下,GPDB 设置 Master 最大连接数是 250,Segment 为 750。超过这个限 制的请求会被拒绝。该限制是有 postgresql.conf 文件中的 max_connections 参数配 置的。若修改 Master 上的该参数,还必须修改 Segment 上该参数为合适的值。 第八章:管理工作负载与资源 本节阐述GPDB的工作负载管理的特征,解释资源队列(Resource Queue/RQ)的创建和 管理。本章包含以下内容:  GP工作负载管理概述  配置负载管理  创建资源队列  分配ROLE(User)到资源队列  修改资源队列  检查资源队列状态 GP 工作负载管理概述 工作负载管理的目的是控制同时活动的查询数量以避免造成系统资源耗尽,比如 Memory、CPU、磁盘I/O。在GPDB中已经具备基于ROLE体系的资源队列。资源队 列可以限制该队列中ROLE执行查询的数量。还可以指定共享CPU资源的使用优先 级。将ROLE分配到合适的资源队列,管理员将可以有效的控制用户的查询并避免 系统超负荷运行。 GPDB 中资源队列如何工作 在安装GPDB时资源调度是缺省打开的。所有的ROLE都必须分配到资源队列。 如果管理员创建ROLE时没有指定只愿队列,该ROLE将会被分配到缺省的资源 队列pg_default。 GP建议管理员结构性的为不同类型工作负载创建独立的资源队列。比如,可以 为高级用户、WEB用户、报表管理等创建不同的资源队列。可以根据对相关的 工作负载压力设置资源队列的限制。目前资源队列的限制包括:  活动语句数量。并行执行的最大语句数量。  活动语句内存使用量。所有提交的语句使用的总内存不能超过该限制。  活动语句优先级。该值设定了该资源队列相对于其他资源队列在CPU资源使用 上的优先级,这里说的优先是相对的。  活动语句的成本。该值由执行计划做的成本估算,该值以涉及的磁盘页(disk page)作为计量单位。 资源队列创建好之后,ROLE(User)即可分配到合适的资源队列。一个资源队列 可以被分配多个ROLE,但每个ROLE只能分配到唯一的资源队列。 资源队列如何限制工作 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 30 - 在运行时,用户提交一个查询,该查询会被针对其资源队列的限制进行评估。 如果评估认为该查询不会超过资源限制,该查询将被立即执行。如果评估认为 该查询超过了资源限制(比如最大活动语句数的查询在执行中),该查询需要等 到有足够的资源时才能得到执行。查询按照先进先出的方式排队。在查询优先 级启用的情况下,系统会定期的重新分配计算资源。可参见”优先级如何工作”。 SUPERUSER是不受资源队列限制的。超级用户的查询语句总是被立即执行, 不管其所在的资源队列如何限制。 内存限制如何工作 在资源队列上设置的内存限制使得每个Segment Instance上该资源队列能够使 用的内存综合不能超过设定的最大值。每个查询语句分配的内存大小是资源队 列的内存限制除以最大活动语句数量(GP建议与活动语句数限制结合使用,而 不是与成本限制结合使用)。比如,资源队列的内存限制为2000MB,活动语句 数限制为10,那么每条执行语句可以分配到200MB的内存。缺省的内存分配可 以针对每条语句通过设置statement_mem参数来覆盖(最大可以达到资源队列限 制的值)。一旦一条语句开始执行,其分配的内存一直到执行结束才会释放(即 便其实际使用的内存小于分配的内存)。 关于资源队列中内存限制的详细信息以及内存使用控制,可参考”创建内存限 制的资源队列”。 执行优先级如何工作 资源限制是针对活动语句来说的,内存和成本的限制属于是否许可类型,其决 定查询语句是非允许进入查询状态或者保持排队状态。在语句作为活动转台时, 其需要分享CPU资源,这部分的资源由资源队列的优先级控制。当一个更高优 先级的语句进入运行状态时,其要求获得更多的CPU资源,相应的需要减少其 他运行中语句的CPU资源。 语句的规模和复杂程度不会影响到CPU的分配。比如一个简单低成本的查询与 一个庞大复杂的查询同时执行却有着相同的优先级,他们将分配到相同的CPU 资源。当一个新的语句开始后,CPU资源分配的比重需要被重新计算,不过, 相同优先级的语句之间获得的CPU资源仍然是相同的。 例如,管理员想要创建3个资源队列:adhoc用于做持续查询的业务分析,reporting 用于定期的报表工作,executive用于高级用户的查询。管理员希望确保定期的 报表工作不要受到adhoc分析查询不可预测资源消耗的影响,希望高级用户提交 的查询能够获得更多的计算资源。因此,资源队列的优先级可以设置为这样: Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 31 -  adhoc – Low priority  reporting – High priority  executive – Maximum priority 运行时,CPU资源由正在运行的语句的优先级决定如何分配。如果语句1与语 句2来自reporting队列且同时运行,他们将获得相同的CPU资源。当一个来自 adhoc队列的语句开始运行,其需要较少的CPU资源。CPU资源的分配将做调整, 他们之间的比重受他们的优先级决定: 注意:该图显示的是粗略的百分比。在CPU的使用上,并不总是按照精确计算 来分配资源的。 当一个来自executive的语句开始执行,CPU资源将根据他们的优先级重新调整。 该语句相对于adhoc和reporting来说可能是很简单的,在其执行完之前,其仍获 取最大份额的CPU资源。 资源队列评估的语句类型 并不是所有的SQL语句都被资源队列评估和限制。缺省状态下只有SELECT、 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 32 - SELECT INTO、CREATE TABLE AS SELECT和DECLARE CURSOR语句被评 估限制。若将Server端参数resource_select_only设置为off,INSERT、UPDATE 和DELETE语句也将被评估和限制。 开启工作负载管理的步骤 在GPDB中开启工作负载管理涉及到如下几个高级任务: 1. 创建资源队列并设置合适的限制 2. 为User Role指定资源队列 3. 使用工作负载管理相关的系统视图监控和管理资源队列 配置工作负载管理 在安装GPDB时资源调度缺省是开启的,并强制所有ROLE遵从。缺省的资源队列是 pg_default,活动语句数量限制为20,成本(cost)无限制,内存(Memory)无限制,中 (medium)优先级。GP建议创建不同类型的资源队列。 配置工作负载管理 1. 以下为一般的资源队列配置参数  max_resource_queues – 设置最多可以有多少个资源队列  max_resource_portals_per_transaction – 设置每个事务最多可以打开几个游标 (Cursor)。值得注意的是每个游标需要占用资源队列的一个活动查询。  resource_select_only – 若设置为on,SELECT、SELECT INTO、CREATE TABLE AS SELECT和DECLARE CURSOR语句被评估限制。若设置为off, INSERT、UPDATE和DELETE语句也将被评估和限制。  resource_cleanup_gangs_on_wait – 在开始一个新的查询之前先清空所在资源 队列中其他空闲的工作进程。  stats_queue_level – 激活资源队列使用信息收集,这样就可以通过查询系统视 图pg_stat_resqueues查看。 2. 以下参数与内存使用有关:  gp_resqueue_memory_policy – 激活GP内存管理特性。设置为none的情况下内 存管理与4.1版本之前相同。设置为auto内存受statement_mem和资源队列内存 限制的控制。缺省为eager_free,译者认为该模式利于合理调整内存使用量。  statement_mem与max_statement_mem – 用于为每个活动语句分配内存(可以覆 盖资源队列的缺省值)。max_statement_mem由SUPERUSER设置,应考虑避免 普通用户的超负荷使用。在任何时候statement_mem都必须小于 max_statement_mem。  gp_vmem_protect_limit – 限制每个Segment Instance上所有语句可以使用的物 理内存总量的上限值。导致内存使用超过该上限的语句会被取消掉(Cancel)而 得不到执行。译者认为该参数要根据具体硬件情况进行合理的评估,从OS层 面来说,内存颗粒的容量用完之后会使用SWAP充当内存,对于普通磁盘来说, 不到万不得已,强烈建议不要使用SWAP。  gp_vmem_idle_resource_timeout 与 gp_vmem_protect_segworker_cache_limit – Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 33 - 用于释放Instance上空闲DB进程的内存。为了提高并发量管理员可以考虑调整 这些配置。详细说明可参考相关附录。 3. 以下参数与查询优先级有关。注意,这些参数都是本地(LOCAL)参数,必须修 改所有Instance的postgresql.conf文件:  gp_resqueue_priority – 缺省状态下查询优先级特性开启。  gp_resqueue_priority_sweeper_interval – 设置CPU为所有活动语句重新计算 CPU资源分配的时间。缺省值通常已经可以满足常规的DB操作。  gp_resqueue_priority_cpucores_per_segment – 设置每个Segment Instance使用的 CPU核数。缺省状态下Segment为4而Master为24,这对于EMC DCA是正确的。 该参数是LOCAL参数。该参数对于Master也是有影响的,需要配置到一个高 效的值。比如,在一个集群中,每个Host主机有8个CPU核,那可以按照如下 配置: Master Instance和Standby Instance gp_resqueue_priority_cpucores_per_segment = 8 Segment Instance gp_resqueue_priority_cpucores_per_segment = 2 重要提示:如果每个Segment Host主机上配置的Instance数量低于CPU核数,请确保 将该参数调整到一个合适的值。过低的值可能会导致CPU资源利用不足。 4. 要查看和修改这个工作负载管理参数,还可以使用gpconfig工具。译者认为, 往往都是使用这个工具,很少有人直接去改postgresql.conf文件的,不过如果由 于参数修改不当导致GPDB系统无法启动,目前这个工具是无能为力的。 5. 比如,要查看一个参数值: $ gpconfig --show gp_vmem_protect_limit 6. 比如,要修改一个参数的值,且Master的值与Segment不同: $ gpconfig -c gp_resqueue_priority_cpucores_per_segment -v 2 -m 8 7. 重启GPDB以确保修改的参数生效(这一节参数都是需要重启的): $ gpstop –r 创建资源队列 创建资源队列涉及到Name,成本、活动语句数量,执行优先级等。通过CREATE RESOURCE QUEUE命令来创建新的资源队列。 创建含活动语句数量的资源队列 资源队列通过设置ACTIVE_STATEMENTS控制活动语句的数量。例如,创建 一个名称为adhoc活动语句数量为3的资源队列: =# CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=3); 这意味着,分配到adhoc资源队列的所有ROLE,在同一时刻最多只能有3个语 句出于执行状态。如果该队列当前已经有3个语句正在执行,在该队列的ROLE 提交第4个语句时,其将出于等待状态,直到前面3个语句有一个执行完。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 34 - 创建含内存限制的资源队列 资源队列通过设置MEMORY_LIMIT控制属于该队列语句可以使用的内存总量。 总的内存不要超过每个Instance可以获得的物理内存总数。GP建议将 MEMORY_LIMIT控制在该Instance可以得的的物理内存总数的90%以下。比如, 一个Host主机有48GB的物理内存,有6个Instance,那么每个Instance可以获得 的物理内存为8GB。这样就可以简单的得到MEMORY_LIMIT=0.9*8GB=7.2GB。 如果存在多个资源队列,他们的MEMORY_LIMIT总和为7.2GB。 当与ACTIVE_STATEMENTS结合使用时,缺省每个语句获得的内存为: MEMORY_LIMIT / ACTIVE_STATEMENTS。当与MAX_COST结合使用时,缺 省的内存分配为:MEMORY_LIMIT * (query_cost / MAX_COST)。GP推荐与 ACTIVE_STATEMENTS结合使用而不是与MAX_COST结合使用。 比如,创建一个活动语句数量为10,从内存限制为2000MB的资源队列(每个语 句在执行时在每个Instance上获得200MB的内存): =# CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=10, MEMORY_LIMIT='2000MB'); 缺省的内存分配可以在每个语句通过设置statement_mem参数覆盖,但不可超过 MEMORY_LIMIT和max_statement_mem设定的值。比如,分配更多的内存: => SET statement_mem='2GB'; => SELECT * FROM my_big_table WHERE column='value' ORDER BY id; => RESET statement_mem; 通常来说,MEMORY_LIMIT的设置建议所有的资源队列的综合不要超过 Instance可以获得的物理内存总数。如果不同类型语句之间交错执行尚且可以, 但让需要留意,如果某个Instance超出了内存限制超,相关语句会被取消的。 创建含成本限制的资源队列 资源队列通过设置MAX_COST限制可以被执行的语句可消耗的成本(Cost)。 Cost以一个浮点数(如100.0或使用科学计数法如1e+2)来指定。 Cost是GP查询规划器(如使用EXPLAIN查看)来评估出来的总预估成本。因此管 理员在设置时需要对该系统执行的查询很熟悉才可以得到一个恰当的Cost阀值。 Cost意味着对磁盘的操作数量。1.0等于读取一个磁盘页(disk page)。 例如,创建一个Cost阀值为100000.0 (1e+5),名称为webuser的资源队列: =# CREATE RESOURCE QUEUE webuser WITH (MAX _COST=100000.0); 或者 =# CREATE RESOURCE QUEUE webuser WITH (MAX _COST=1e+5); 这意味着,分配到webuser资源队列的所有ROLE执行的全部语句Cost总和不能 超过100000.0的限制。比如,有200个Cost为500.0的语句正在执行,第201个Cost 为1000.0的语句提交后只能等到空闲的Cost足够时才能得到执行。 允许在系统空闲是执行语句 若一个资源队列配置了Cost阀值,管理员可以允许COST_OVERCOMMIT(这是 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 35 - 缺省设置)。在系统没有其他语句执行时,超过资源队列Cost阀值的语句可以被 执行。而当有其他语句在执行时,Cost阀值仍被强制执行。 如果COST_OVERCOMMIT被设置为false,超过Cost阀值的语句将永远被拒绝。 允许小查询绕过队列限制 可能存在一些工作负载很小的查询,管理员希望其不炸用资源队列的活动语句 数量而允许其得到执行。比如,一些检索系统表的语句不涉及大的资源消耗甚 至不需要与Segment进行交互。管理员可以设置MIN_COST指明什么样的开销 作为小查询。那些低于MIN_COST的语句将立即得到执行。MIN_COST不仅可 以同最大Cost一起使用,还可以和活动语句数量一起使用。例如: =# CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=10, MIN_COST=100.0); 设置优先级级别 为了控制CPU资源的使用,管理员可以设置合适的优先级。在并发争用CPU资 源时,高优先级资源队列中的语句将可以获得比的低优先级资源队列中语句更 多的CPU资源。 优先级可以在CREATE RESOURCE QUEUE和ALTER RESOURCE QUEUE的 时候通过WITH来设置。例如,为adhoc和reporting队列指定优先级,管理员可 以使用下面的命令: =# ALTER RESOURCE QUEUE adhoc WITH (PRIORITY=LOW); =# ALTER RESOURCE QUEUE reporting WITH (PRIORITY=HIGH); 创建最高优先级的队列executive,管理员可以使用下面的命令: =# CREATE RESOURCE QUEUE executive WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX); 在优查询优先级特性开启时,资源队列的优先级缺省为MEDIUM。 重要提示:要使得资源队列的优先级设置在执行语句中强制生效,必须确保优 先级特性的相关参数已经设置好。参见”配置工作负载管理”。 分配 ROLE(User)到资源队列 一旦资源队列被创建好了,就需要把ROLE(User)分配到合适的资源队列。如果ROLE 没有被显式的分配到一个资源队列,其将被分配到缺省的资源队列pg_default。缺省 的资源队列含20个活动语句数量和MEDIUM的优先级。 使用ALTER ROLE或者CREATE ROLE命令来分配ROLE到资源队列。比如: =# ALTER ROLE name RESOURCE QUEUE queue_name; =# CREATE ROLE name WITH LOGIN RESOURCE QUEUE queue_name; 每个ROLE同一时间只能被分配到一个资源队列,可以使用ALTER ROLE命令修改 ROLE的资源队列。 资源队列的分配必须通过逐个User的方式进行。如果有一个层级较高的ROLE(比如 GROUP ROLE),将给ROLE分配到一个资源队列并不会将其包含的User分配到该资 源队列。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 36 - SUPERUSER总是不受资源队列限制的。SUPERUSER的查询总是可以立即得到执行, 而不管资源队列的限制如何设置。 从资源队列中移除 ROLE 所有ROLE都需要分配到资源队列。如果没有被显式分配到指定的资源队列,该 ROLE将会进入缺省资源队列pg_default。如果想将ROLE从现有资源队列中移除并 放到缺省队列中,可将其资源队列分配到none。比如: =# ALTER ROLE role_name RESOURCE QUEUE none; 修改资源队列 在创建资源队列后,可以使用ALTER RESOURCE QUEUE命令来改变或者重置队列 的限制。还可以使用DROP RESOURCE QUEUE命令删除资源队列。 变更资源队列 使用ALTER RESOURCE QUEUE命令来改变资源队列的限制。一个资源队列必 须包含ACTIVE_STATEMENTS或者MAX_COST(或者都包含)。变更资源队列, 执行新的值。例如: =# ALTER RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=5); =# ALTER RESOURCE QUEUE exec WITH (MAX_COST=100000.0); 要将活动语句数量或者内存限制重置为无限制,可以使用-1值。要重置Cost门 槛为无限制,可以设置为-1值。比如: =# ALTER RESOURCE QUEUE adhoc WITH (MAX_COST=-1.0, MEMORY_LIMIT='2GB'); 可以使用ALTER RESOURCE QUEUE命令改变查询优先级。比如,设置一个资 源队列的优先级为最低级别: ALTER RESOURCE QUEUE webuser WITH (PRIORITY=MIN); 删除资源队列 使用DROP RESOURCE QUEUE命令删除资源队列。要删除一个资源队列,该 资源队列不能与任何ROLE相关,或者队列中有语句正等待执行。删除一个资 源队列: =# DROP RESOURCE QUEUE name; 检查资源队列状态 检查资源队列状态涉及下列内容:  查看排队语句和资源队列状态  查看资源队列统计信息  查看分配到资源队列的ROLE  查看资源队列中等待的语句 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 37 -  清除资源队列中等待的语句  查看活动语句的优先级  重置活动语句的优先级 查看排队语句和资源队列状态 管理员可以通过查看视图gp_toolkit.gp_resqueue_status来查看资源队列的状态。 该视图展示系统中每个资源队列有多少语句在等待执行,多少语句正在执行。 查看资源队列在系统中的创建、限制、当前的状态: =# SELECT * FROM gp_toolkit.gp_resqueue_status; 查看资源队列统计信息 如果要追踪资源队列的统计信息和性能,需要为资源队列开启统计信息收集配 置。这可以通过配置Master上postgresql.conf文件的这个参数来实现: stats_queue_level = on 一旦该配置开启,就可以使用系统视图pg_stat_resqueues来查看资源队列使用的 统计信息。注意,开启该配置会带来轻微的资源开销,每个经资源队列执行的 语句都会被追踪。开启统计信息收集对于初期的资源队列诊断是有帮助的,而 后续的运行应该关闭该参数。 更多关于资源队列统计信息收集的问题可以参考PostgreSQL的相关文档。 查看分配到资源队列的 ROLE 要查看ROLE与资源队列之间的关联关系,使用系统日志表pg_roles和 gp_toolkit.gp_resqueue_status来获得: =# SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid; 或者可以创建一个视图来简化以后的使用。例如: =# CREATE VIEW role2queue AS SELECT rolname, rsqname FROM pg_roles, pg_resqueue WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid; 这样就可以直接查询试图了: =# SELECT * FROM role2queue; 查看资源队列中等待的语句 当语句在资源队列中执行时,其会被记录在pg_locks系统日志表中。这里可以 查看到所有的活动语句和等待语句。为了检查处于等待状态的语句(即便没有语 句在等待),可以使用gp_toolkit.gp_locks_on_resqueue视图。例如: =# SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true'; 若该查询没有结果返回,意味着此时没有语句在资源队列中等待执行。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 38 - 清除资源队列中等待的语句 有时候可能需要清楚资源队列中出于等待状态的语句。比如,想要清楚在资源 队列中等待还没有得到执行的语句。还有可能想要终止一个已经开始而需要很 长时间才能完成的语句,或者该语句处于空闲的事务状态而希望其把资源让给 其他需要的ROLE。要达到这些目的,首先需要知道那些语句需要清除,确定 该进程的ID,然后使用pg_cancel_backend函数来终止该进程。 比如,查看当前处于活动状态或者等待状态的语句: =# SELECT rolname, rsqname, pid, granted, current_query, datname FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity WHERE pg_roles.rolresqueue=pg_locks.objid AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid AND pg_stat_activity.procpid=pg_locks.pid; 若没有结果返回,意味着当前没有语句处于资源队列中。比如有两个语句在资 源队列中可能是这种样子的: rolname | rsqname | pid | granted | current_query | datname ----------------------------------------------------------------------- Sammy | webuser | 31861 | t | in transaction | namesdb daria | webuser | 31905 | f | SELECT * FROM topten; | namesdb 根据输出结果确定需要清除语句的进程ID(pid)。通过下面的方式清楚语句: =# pg_cancel_backend(31905) 注意:尽量不要使用OS的KILL命令。译者认为,不是完全不可以用,如果不 小心把系统搞崩溃了有能力拯救的话,其实怎么杀都无所谓。 查看活动语句的优先级 在gp_toolkit模式中有个视图gp_resq_priority_statement,其包含了所有正在执行 的语句的优先级,会话ID等信息。该视图只能通过gp_toolkit模式访问。 重置活动语句的优先级 SUPERUSER可以在语句运行期间通过内置函数gp_adjust_priority(session_id, statement_count, priority)调整其优先级。通过该函数SUPERUSER可以提升或者 降低任何语句的优先级。例如: =# SELECT gp_adjust_priority(752, 24905, 'HIGH') 该函数需要获取语句的SESSION ID和Statement Count两个参数,SUPERUSER 可以通过gp_toolkit模式的视图gp_resq_priority_statement获得,session_id和 statement_count两个参数分别对应 rqpsession和rqpcommand。该函数只对指定 的语句有效,同一个资源队列随后的语句仍然使用其正常设定的优先级。 第九章:定义数据库对象 本章覆盖GPDB的数据定义语言(DDL)以及如何创建和管理数据库对象。  创建与管理数据库 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 39 -  创建与管理表空间  创建与管理模式  创建与管理表  分区大型表  创建与使用序列  在GPDB中使用索引  创建与管理视图 创建与管理数据库 一个GPDB系统可以有过个数据库(Database)。这与一些DBMS不同(比如Oracle),它 们的Instance就是Database。在GP系统中,虽然可以创建多个DB,但是客户端程序 一次只能连接一个DB –不可以跨越DB执行查询语句。 关于数据库模版 每个新的数据库都是基于一个模版创建的。缺省的数据库模版为template1。在 初始化GPDB系统初期可以连接到该库。在没有明确指定模版的情况下创建新 的数据库将缺省使用该DB作为模版。除非你希望之后创建的DB包含你所创建 的对象,请不要在该DB中创建任何对象。 除了template1之外,每个新建的GP系统还包含另外两个模版template0和 postgres,这两个DB是系统内部使用的,最好不要删除或者修改。template0模 版库可以用来创建仅仅包含标准对象的完全干净的数据库。如果想避免从 template1中拷贝任何的对象,可以考虑使用该模版。 创建数据库 通过CREATE DATABASE命令来创建一个新的数据库。例如: => CREATE DATABASE new_dbname; 要创建一个数据库,必须具备创建数据库的权限或者是SUPERUSER身份。若 没有正确的权限是无法创建数据库的。需要联系GP管理员授予必要的权限或者 帮助创建一个数据库。 还有有一个客户端程序createdb可以用来创建数据库。例如,通过命令行终端执 行下面的命令将会在指定的Host上创建名为mydatabase的数据库: $ createdb -h masterhost -p 5432 mydatabase 克隆一个数据库 缺省状态下,创建数据库是通过克隆数据库模版template1的方式完成的。然而 任何的DB都可以作为模版来创建新一个的数据库,因此可以通过指定DB的方 式克隆或者拷贝出一个新的数据库,新的DB包含模版的所有对象和数据。例如: => CREATE DATABASE new_dbname TEMPLATE old_dbname; Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 40 - 查看数据库列表 在psql客户端程序中,直接使用\l指令查看GPDB中包含模版在内的所有DB的列 表。使用其他客户端程序时,可以通过查询pg_database系统日志表(必须是 SUPERUSER用户)来得到。比如: => SELECT datname from pg_database; 变更数据库 使用ALTER DATABASE命令来改变DB的属性,例如Owner、Name以及缺省配 置等。必须是该DB的Owner或者SUPERUSER才可以执行这样的操作。下面的 例子演示修改默认的搜索路径: => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog; 删除数据库 使用DROP DATABASE命令来删除DB。该操作将从系统信息表中删除该DB的 信息记录,并删除该DB包含的全部磁盘数据。必须是该DB的Owner或者 SUPERUSER才可以执行删除DB操作,其他用户无法删除该DB,有其他用户 连接时也无法删除。可以先连接到template1(或者其他DB),然后在删除需要删 除的DB。例如: => \c template1 => DROP DATABASE mydatabase; 这里同样有一个客户端程序叫做dropdb用以删除DB。例如,通过命令行中断执 行下面的命令将会在指定的Host上删除名为mydatabase的数据库: $ dropdb -h masterhost -p 5432 mydatabase 警告:删除数据库操作是无法回滚的。慎用该操作! 创建与管理表空间 表空间(tablespace)允许DB管理员使用多个文件系统来存储数据库对象,从而可以决 定如何更好的利用他们的物理储存器。表空间的使用是有好处的,比如在访问频度 不同的数据库对象上使用不同性能的磁盘。例如,将经常使用的表放在高性能的文 件系统上(比如SSD固态盘),而将其他表放在普通的硬盘上。 一个表空间是需要一个文件系统来存储其数据库文件的。在GPDB中,Master和每 个Segment(primary和mirror)都需要独立的存储位置(或者说目录)。所有这些文件系 统组件构成了GP系统中所谓的文件空间(filespace)。文件空间定义之后,其可以被 多个表空间使用。简言之,在GP系统中,文件空间建立在一系列的文件系统之上, 表空间建立在文件空间之上。 创建文件空间 要创建文件空间,首先需要在所有相关的GP Host主机上准备好逻辑文件系统。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 41 - 文件系统位置对于Master和所有的Primary和Mirror来说都是必须的。在准备好 了文件系统之后,使用gpfilespace工具来定义文件空间。只有SUPERUSER才能 进行该操作。 注意:GP并不直接知晓文件系统的界限,其只是把文件存向指定的位置。因此, 在一个逻辑磁盘位置定义多个文件空间是没有意义的,因为根本无法控制文件 在逻辑文件系统上储存的具体位置。 使用gpfilespace创建文件系统 1. 使用gpadmin用户登录的GPDB系统的Master主机。 $ su - gpadmin 2. 创建一个文件空间的配置文件: $ gpfilespace -o gpfilespace_config 3. 将会提示输入一个文件空间的名称,Primary Segment的文件系统位置, Mirror Segment的文件系统位置,Master的文件系统位置。例如,若每个 Segment Host配置了2个Primary和2个Mirror,将会提示出入5个文件系统位 置(包括Master)。就像这样: Enter a name for this filespace> fastdisk primary location 1> /gpfs1/seg1 primary location 2> /gpfs1/seg2 mirror location 1> /gpfs2/mir1 mirror location 2> /gpfs2/mir2 master location> /gpfs1/master 4. 该工具命令将会输出一个配置文件。请再次检查该文件确认其按照期望的 那样反映出了想要使用的文件系统位置。 5. 再次执行该工具命令,基于之前生成的配置文件创建文件空间: $ gpfilespace -c gpfilespace_config 转移临时文件或事务文件的位置 可以选择将临时文件或事务文件到一个特殊的文件空间从而改善DB的查询性 能、备份性能、连续存储数据的性能。 临时文件和事务文件默认都是存储在每个Instance(包括Master、Standby、 Primary和Mirror)目录下。只有SUPERUSER可以移动该位置。只有gpfilespace 工具可以写该文件。 关于临时文件和事务文件 除非另有指明,临时文件和事务文件都是和用户数据放在一起的。缺省的临时 文件位置为///pgsql_tmp,使用 gpfilespace –movetempfiles命令将改变该文件的位置。 关于临时文件和事务文件,需要注意以下几点信息:  虽然可以使用同一个文件空间存储不同类型文件,但只能为临时文件或者 事务文件指定一个文件空间。  如果文件空间被临时文件使用,该文件空间将不能被删除。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 42 -  文件空间必须提前被创建好才能使用。 使用gpfilespace移动临时文件 1. 确保文件空间存在,且与存储其他用户数据的文件空间不同。 2. 将GPDB系统停掉,保持离线状态。 注意:任何活动的连接都会导致gpfilespace –movetempfiles操作的失败。 3. 把GPDB启动为限制模式,确保其他用户无法连接,执行下面的命令: gpfilespace --movetempfilespace filespace_name 注意:临时文件位置在Segment Instance中配合共享内存使用,在创建、打开、 删除临时文件时用到。如果查询用到了临时文件,表明当前的可用内存已经无 法满足该查询对内存的需求。很多时候我们宁愿使用临时文件也不选择使用 SWAP作为内存扩展方案。除非SWAP的性能比临时文件目录所在文件系统的 性能还要高。 使用gpfilespace移动事务文件 1. 确保文件空间存在,且与存储其他用户数据的文件空间不同。 2. 将GPDB系统停掉,保持离线状态。 注意:任何活动的连接都会导致gpfilespace –movetransfiles操作的失败。 3. 把GPDB启动为限制模式,确保其他用户无法连接,执行下面的命令: gpfilespace --movetransfilespace filespace_name 注意:事务文件位置在Segment Instance中配合共享内存使用,在创建、打开、 删除事务文件时用到。 创建表空间 一旦文件空间创建好了,就可以使用该文件空间定义表空间了。要定义一个表 空间,使用CREATE TABLESPACE命令,比如: =# CREATE TABLESPACE fastspace FILESPACE fastdisk; 表空间必须由SUPERUSER才可以创建,不过在创建好之后可以允许普通的DB User来使用该表空间。可以将CREATE权限授予相应的用户。例如: =# GRANT CREATE ON TABLESPACE fastspace TO admin; 使用表空间存储 DB 对象 表、索引、甚至整个DB都可以指定在特定的表空间。若要如此,拥有给定表空 间CREATE权限的Role必须通过表空间的名称作为相关命令的参数来实现,下 面是创建一个space1表空间上的表: CREATE TABLE foo(i int) TABLESPACE space1; 或者使用缺省表空间参数default_tablespace来设定: SET default_tablespace = space1; CREATE TABLE foo(i int); 在将default_tablespace设置为一个非空字符串后,其相当于给CREATE TABLE 和CREATE INDEX命令添加一个TABLESPACE的子句,但却不必显式注明。 如果一个表空间与DB关联,那么其将存储所有该DB的系统日志、临时文件等。 此外,其也是在该DB上创建表、索引等缺省的表空间(除非通过TABLESPACE Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 43 - 或者default_tablespace参数指定)。如果DB在创建的时候没有与特定的表空间相 关联,那么该DB与其使用的模版DB使用相同的表空间。 一旦表空间被创建,将可以被任何DB使用并提供足够的用户访问权限。 查看现有的表空间和文件空间 每个GPDB系统都有两个缺省的表空间:pg_global(用以存储系统日志信息)和 pg_default(用以存储template1和template0模版DB的缺省表空间)。这些表空间使 用系统缺省的文件空间pg_system(系统初始化时使用的数据目录data directory)。 要获取文件空间的信息,可以查看系统日志表pg_filespace和pg_filespace_entry。 可通过与pg_tablespace关联查看表空间的完整定义。例如: =# SELECT spcname as tblspc, fsname as filespc, fsedbid as seg_dbid, fselocation as datadir FROM pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfse WHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY tblspc, seg_dbid; 删除表空间和文件空间 在表空间相关的所有对象被删除之前,该表空间是不能被删除的。同样的,在 相关的表空间被删除之前,文件空间是不能被删除的。 要删除表空间,可通过DROP TABLESPACE命令完成。表空间只能被其Owner 和SUPERUSER删除。 要删除文件空间,可通过DROP FILESPACE命令完成。只有SUPERUSER可以 删除文件空间。 注意:如果文件空间被临时文件或者事务文件使用,该文件空间将不能被删除 创建与管理模式 模式(Schema)是在DB内阻止对象的一种逻辑结构。模式可以允许用户在一个DB内 不同的模式之间使用相同Name的对象(比如Table)。 缺省”Public”模式 每个新创建的DB都有一个缺省的模式public。如果没有创建其他的模式,在创 建DB对象时将缺省使用public模式。缺省情况下所有的ROLE(User)都有public 模式下的CREATE和USAGE权限。而在创建其他模式时,需要将该模式授权给 相关的ROLE(User)。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 44 - 创建模式 使用CREATE SCHEMA命令来创建一个新的模式。例如: => CREATE SCHEMA myschema; 要访问某个模式中的对象,可以通过模式名加圆点加对象名来指明对象所属的 模式。比如: schema.table 还可以在创建模式的时候将Owner设置为其他ROLE(User)。语法如下: => CREATE SCHEMA schemaname AUTHORIZATION username; 模式搜索路径 要知道在DB的那个模式下搜索需要的对象,可以通过明确指定模式名的方式来 实现。例如: => SELECT * FROM myschema.mytable; 若不想通过指定模式名称的方式来实现,可以通过设置search_path参数来完成。 该参数告诉DB其应该在哪些可用的模式中搜索对象。在不指明模式名称的情况 下,搜索路径(search_path)列表中的第一个模式将成为缺省模式,比如创建对 象等。 设置模式搜索路径 search_path用于设置模式的搜索顺序。该参数可以通过ALTER DATABAST命 令修改DB的模式搜索路径。例如: => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog; 还可以通过ALTER ROLE命令修改特定ROLE(User)的模式搜索路径。例如: => ALTER ROLE sally SET search_path TO myschema, public, pg_catalog; 设置了模式搜索路径之后,在未明确指明模式名称的情况下访问DB对象,将会 按照search_path列表的顺序依次在相应的Schema中查找对应的Object,直到找 到为止,若在不同的Schema中存在相同Name的Object,DB优先匹配search_path 中靠前的Schema下的Object。 查看当前的模式 有时不确定当前所在的模式或者搜索路径。要查看这些信息,可以通过使用 current_schema()函数或者SHOW命令来查看。例如: => SELECT current_schema(); => SHOW search_path; 删除模式 使用DROP SCHEMA命令来删除模式。例如: => DROP SCHEMA myschema; 缺省状态下,只有空的模式才可以被删除。若想要直接删除模式及相关的所有 Object(Table、Index、Function等)。使用如下命令: => DROP SCHEMA myschema CASCADE; Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 45 - 系统模式 下面的这些系统级别的模式在所有的DB中都存在:  pg_catalog模式存储着系统日志表(System Catalog Table),内置类型(Type)、 函数(Function)和操作符(Operator)。该模式无论是否在search_path中指明, 都存在search_path中。  information_schema模式由一个标准化视图构成,其包含DB中对象的信息。 该视图用于以标准化的方法从系统日志表中查看系统信息。  pg_toast模式是一个储存大对象的地方(那些超过页面尺寸(page size)的记 录)。该模式仅供GPDB系统内部使用,通常不建议管理员或者任何用户访 问。  pg_bitmapindex视图是一个储存bitmap index对象的地方(值列表等)。该模式 仅供GPDB系统内部使用,通常不建议管理员或者任何用户访问。  pg_aoseg视图是一个储存append-only表的地方。该模式仅供GPDB系统内部 使用,通常不建议管理员或者任何用户访问。  gp_toolkit是一个管理用的模式,可以查看和检索系统日志文件和其他的系 统信息。gp_toolkit视图包含一些外部表、视图、函数,可以通过SQL的方 式访问它们。gp_toolkit视图对于多有DB User都是可以访问的。更多信息 查看” gp_toolkit管理视图”。 创建与管理表 GPDB中的Table除了数据是分布在不同Segment主机这点外,和其他关系型数据库 的Table是很相像的。在创建Table时,需要一个额外的SQL语法来指明Table的分布 策略。 创建表 CREATE TABLE命令用于创建一张新的Table和定义其结构。在创建Table时, 通常需要定义如下几个方面:  都有哪些列(Column)以及对应的数据类型。  Table或者Column的约束(Constraint),其限定了Table或者Column可以储存 什么样的数据。  Table的分布策略,其决定了Table的Data如何被分割存储在GPDB的各个 Segment上。  Table在Disk上的存储方式。比如压缩、按列存储等选项。  大表的分区策略(Partition Table)。 选择Column的数据类型 Column的Data Type决定了其可以储存什么类型的数据值。通常都希望用最小的 空间储存数据。但还应该考虑到Data Type对数据的约束。比如,持用Character 类型储存字符串,Date或者Timestamp储存日期,Numeric储存数字等。 对于Character类型来说,CHAR、VARCHAR和TEXT之间不存在性能差异,当 然是在不考虑填补空白导致的储存尺寸增加的影响情况下。然而在其他的DB Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 46 - 系统中,可能CHAR会表现出最好的性能,但在GPDB中是不存在这种性能优 势的。在多数情况下,应该选择使用TEXT或者VARCHAR而不是CHAR。 对于Numeric类型来说,应该尽量选择更小的数据类型来适应数据。比如,选 择BIGINT类型来存储SMALLINT类型范围内的数值,会造成空间的大量浪费。 对于计划用来做Table Join的Column来说,应该考虑选择相同的数据类型。如果 做Join的Column具有相同的数据类型(比如主键PrimaryKey与外键ForeignKey), 其工作效率会更高。如果两者的数据类型不同,DB还需要将其中一个类型做转 换从而可以做关联比较,这种开销是不必要的浪费。 设置Table和Column的约束 数据类型用来限制在Table中可以存储的数据的性质。但对于很多应用来说,数 据类型提供的限制粒度太大。SQL标准允许在Table和Column上定义约束。约束 将可以在Table的数据上使用更多的限制。如果User试图在Table上储存违反约 束的数据将会发生错误。在GPDB中使用约束是有一些限制的,最为限制的是 外键(ForeignKey)、主键(PrimaryKey)和唯一约束(Unique Constraint)。其他约束 的支持与PostgreSQL的相同。 检查约束 检查约束是最常见的约束类型。其通过指定数据必须满足一个布尔表达式来约 束。比如,要求产品的价格必须为正数,可以这样: => CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) ); 非空约束 非空约束简单的理解就是不可以存在空(NULL)值。非空约束是一种Column类 型的约束。例如: => CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price numeric ); 唯一约束 唯一约束可以确保包含某些Column的数据在整个Table中是唯一的。在GPDB中 使用唯一约束存在强制条件,Table必须是HASH分布的(而不是DISTRIBUTED RANDOMLY),并且唯一约束的Column集合必须完整包含所有的DK Column。 => CREATE TABLE products (product_no integer UNIQUE, name text, price numeric) DISTRIBUTED BY (product_no); 主键约束 主键约束就是唯一约束与非空约束的结合体。在GPDB中使用主键约束存在强 制条件,Table必须是HASH分布的(而不是DISTRIBUTED RANDOMLY),并且 主键约束的Column集合必须完整包含所有的DK Column。如果一个Table包含主 键,那么主键包含的所有Column会缺省成为DK。例如: => CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric) DISTRIBUTED BY (product_no); 外键约束 在目前版本的GPDB中外键约束是没有被支持的。可以定义外键约束,但参照 完整性是无效的,就是说DB不会理会其参照完整性。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 47 - 外键约束要求一个Table中某些Column的值必须在另外一个Table中出现。这样 可以维持两个相关表之间的数据完整性。在当前版本的GPDB中,在分布式的 Table之间的数据完整性检查是无效的。 选择表的分布策略 GPDB的所有Table都是分布式存储的。在CREATE TABLE和ALTER TABLE的 时候有个DISTRIBUTED BY(HASH分布)或DISTRIBUTED RANDOMLY(随机 分布)子句用以决定Table的Row数据如何分布。 在选择表的分布策略时需要重点考虑以下几点(依次更重要):  平坦的数据分布 – 为了尽可能达到最好的性能,所有的Segment Instance 应该尽量储存等量的数据。若数据的分布不平衡或倾斜,那些储存了较多 数据的Instance在处理自己那部分数据时将需要耗费更多的工作量。为了实 现数据的平坦分布,可以考虑选择具有唯一性的DK,如主键。往往很多 Table是没有唯一键的,译者认为,只需尽量选择数据分布规律且取值范围 远远大于Instance数量的Column作为DK即可。  本地操作与分布式操作 – 在处理查询时,很多处理如关联、排序、聚合 等若能够在Instance本地完成,其效率将远高于跨越系统级别(需在Segment Instance之间交叉传输数据)的操作。当不同的Table使用相同的DK时,在 DK上的关联或者排序操作将会以最高效的方式把绝大部分工作在Segment Instance本地完成。本地操作大约比分布式操作快5倍。若Table使用随机分 布策略,将大大限制本地操作的可能性,虽然这种方式可以确保数据分布 的平坦性。  平坦的查询处理 – 在一个查询正被处理时,我们希望所有的Segment都能 够处理等量的工作负载,从而尽可能达到最好的性能。有时候查询场景与 数据分布策略很不吻合,这时很可能导致工作负载的倾斜。例如,有一张 销售交易Table。该Table的DK为公司名称,那么数据分布的HASH算法将 机遇公司名称的值来计算,假如有一个查询已某个特定的公司名称作为查 询条件,该查询任务将仅在一个Segments Instance上执行。若查询时不是指 定特定的单位名称,这仍然是个可行的分布策略。译者认为,这个例子只 是用以说明查询倾斜是如何发生的,在真实的应用中有时为了达到某种效 果还可能会特意这样选择,比如提高并发访问能力。 声明分布键 在创建Table时有一个额外的子句用以指明分布策略。如果在创建Table时没有 指明DISTRIBUTED BY或者DISTRIBUTED RANDOMLY子句,GPDB将会依 次考虑使用主键(假如该Table有的话)或者第一个字段作为HASH分布的DK。几 何类型或者自定义类型的Column是不适合作为GP的DK的。如果一个Table没有 一个合适类型的Column作为DK,该表将使用随机分布策略。就译者的经验来 说,还有办法是的Table不分布,即像System表那样只存储在Master上,要达到 这样的效果,在CREATE TABLE的时候使用空的COLUMN配置且不指定分布 策略。这样创建出来的是一张没有意义的空表,之后在通过ALTER TABLE命 令添加COLUMN即可。有些特殊场合可以考虑这种选择,除非你已经对GP的 工作机制深度了解,一般情况下不建议尝试。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 48 - 为了确保数据的平坦分布,可能需要选择一个具有唯一性的Column作为DK, 如果达不到平坦的效果,也可以选择DISTRIBUTED RANDOMLY策略,但这 只应该作为最后的选择。例如: => CREATE TABLE products (name varchar(40), prod_id integer, supplier_id integer) DISTRIBUTED BY (prod_id); => CREATE TABLE random_stuff (things text, doodads text, etc text) DISTRIBUTED RANDOMLY; 选择表的存储模式 GPDB提供几种灵活的存储处理模式(或者混合模式)。在创建一张新的TABLE 时,有几个选项来决定数据如何储存在磁盘上。该节解释这几种选项,以及出 于工作负载的考虑如何实现最佳的储存模式。  选择堆存储(Heap)或只追加(Append-Only/AO)存储  选择行存储(Row-Orientation)或列存储(Column-Orientation)  使用压缩(只可以是AO表)  检查只追加(AO)表的压缩和分布情况 选择堆存储或者只追加存储 缺省情况下GPDB使用与PostgreSQL相同的存储模式堆存储。堆存储模式在 OLTP类型工作负载的DB中很常用,数据在初始装载后经常变化。UPDATE和 DELETE操作需要对ROW级别做版本控制从而确保DB事务处理的可靠性。堆 表更适合一些小表,比如维表,这种表可能会在初始化装载后经常更新数据。 GPDB还提供了一种称之为只追加存储模式的TABLE。AO表更适合数据仓库中 非规范化事实表,这些表通常都是系统中最大的表。事实表通常是批量装载数 据且只进行只读式检索操作。AO表在数据装载后是不支持更新的。将事实表 数据存入AO表时不会保留更新相关ROW级别的信息(大约每ROW需要20字节)。 AO表达到了更精简和优化的页面存储结构。AO表不允许指定DELETE和 UPDATE操作。该存储模式强化了批量数据装载的性能。不推荐一行一行的使 用INSERT语句来装载数据。 创建堆表 行存堆表是缺省的存储模式,创建堆表时不需要额外的CREATE TABLE语法。 例如: => CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a); 创建只追加表 在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。如果没有指明, 该表将会是缺省的行存堆表。例如,要创建一张没有压缩的AO表: => CREATE TABLE bar (a int, b text) WITH (appendonly=true) 选择行存或列存 GPDB提供存储导向的选择:行存或列存(或者混合)。本节提供一些关于正确选 择行存或列存的常规指导。不过具体还需根据查询工作负载来确切评估。 从最通用的目的和混合从左负载来考虑,行存可以提供灵活与性能的最佳结合。 不过在一些特定的情况下,列存可以提供更好的I/O和存储性能。在考虑使用行 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 49 - 存还是列存时需要考虑以下几点:  表数据的更新。如果一张表在装载完之后一定有更新操作,那么就选择行 存表。因为列存表必须是AO表。AO表不可以更新。  经常做INSERT操作。如果经常有数据被INSERT,考虑选择行存表。列存 表对于写操作不是最优的,因为没条数据都需要被写到磁盘的多个位置(列 存表的每列存储于不同的磁盘文件,而行存表是存储在同一个磁盘文件)。  查询涉及的COLUMN数量。若通常在SELECT或者WHERE中涉及TABLE 的全部或大部COLUMN,考虑选择行存表。行存适合在WHERE或HAVING 中对单列做聚合操作: SELECT SUM(salary)... SELECT AVG(salary)... WHERE salary > 10000 或者在WHERE体哦啊见中使用单个ROW条件且返回相对少量的ROW: SELECT salary, dept ... WHERE state='CA'  TABLE的COLUMN数量。行存储对于使用COLUMN数量很多或者ROW 的数据尺寸相对较小的TABLE来说更高效。列存表在只访问宽表的很少 COLUMN的查询中可以表现出更好的性能。  压缩。虽然行存与列存的数据类型完全相同,但在列存表上的一些压缩优 势无法应用在行存表上。例如,许多压缩算法都利用相邻相似的数据进行 压缩。然而,越深的压缩,其随机访问越困难,因为在读取数据时是需要 解压缩的。 创建列存表 在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。如果没有指明, 该表将会是缺省的行存堆表。使用列存的TABLE必须是AO表。比如,创建一 张列存储的TABLE: => CREATE TABLE bar (a int, b text) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a); 使用压缩(只可以是AO表) 在GPDB中,AO表有两种库内压缩可选,一种是表级的压缩,另外一种是 COLUMN级别的压缩,前者应用到整个TABLE,后者应用到指定的COLUMN。 在选择COLUMN级别压缩时,可以为不同的COLUMN选择不同的压缩算法。 下表是可用的压缩算法: 表导向 可用压缩类型 支持压缩算法 行 表级别 ZLIB 和 QUICKLZ 列 列级别 和 表级别 RLE_TYPE、ZLIB 和 QUICKLZ 使用库内压缩要求Segment系统具备强劲的CPU来压缩和解压缩数据。不要压 缩文件系统使用压缩AO表。如果Segment数据目录是压缩文件体统,不要压缩 使用AO表。 在选择AO表的压缩方式和级别是,需要考虑以下几点因素:  CPU性能  压缩比或占用磁盘尺寸  压缩速度  解压速度或扫表效率 虽然说在考虑压缩表时最小的占用磁盘尺寸是主要目的,但影响压缩和扫表的 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 50 - CPU性能也是需要重点考虑的。每个系统都有一个最优的压缩设置,应在保证 不会显著提高压缩时间和降低扫表效率的前提下最有效的压缩减少数据尺寸。 QUICKLZ压缩通常适用于CPU能力一般的情况,其压缩速度比ZLIB快,但压 缩率不如ZLIB。相反的,ZLIB提供更高的压缩率,但压缩速度较低。在压缩 级别为1时,QUICKLZ与ZLIB可能获得差不多的压缩率(但压缩速度ZLIB可能 差一些)。但在6级以上的ZLIB在压缩率方面的优势显著高于QUICKLZ(但压缩 速度也因此显著的低于QUICKLZ)。 压缩AO表的性能相关的因素包括硬件、查询调优等方面。通常建议在特定的 环境下选择储存模式时最好根据相应的比较测试的结果来确定。 创建压缩表 在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。使用压缩模式 的TABLE必须是AO表。例如,要创建一张5级ZLIB压缩的AO表: => CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); 注意:QUICKLZ压缩模式只有一种压缩级别,没有级别选项可以选择。而ZLIB 压缩模式有1 – 9个压缩级别可选。 检查AO表的压缩与分布情况 GP提供了内置的函数用以检查AO表的压缩率和分布情况。这两个函数可以使 用对象ID或者TABLE的NAME作为参数。表名可能需要带模式名限定。 函数 返回类型 解释 get_ao_distribution(name) get_ao_distribution(oid) Set of (dbid, tuplecount) rows 展示 AO 表的分布情况,每 ROW 对应 Segment Instance 的 dbid 与储存的数据行数。 get_ao_compression_ratio(name) get_ao_compression_ratio(oid) float8 计算出 AO 表的压缩率。如果 该信息未得到,将返回-1 值。 压缩率得到的是一个常见的比值类型。比如,3.19的返回值或者3.19:1,意味着 该TABLE未压缩状态下的储存尺寸是压缩下的储存尺寸的3倍多。 分布信息展示的是每个Segment Instance存储该TABLE的ROW数量。例如,在 一个有着4个Segment Instance的系统,其dbid范围为0 – 3,干函数返回类似下面 的结果集: =# SELECT get_ao_distribution('lineitem_comp'); get_ao_distribution --------------------- (0,7500721) (1,7501365) (2,7499978) (3,7497731) (4 rows) 支持运行长度编码 GPDB已支持COLUMN级别的运行长度编码(Run-length Encoding /RLE)压缩算 法。RLE是一种将连续重复的数据作为一种计数方式存储的压缩算法。RLE对 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 51 - 于重复元素是很有效的。比如,在一个表中有两个COLUMN,一个日期 COLUMN和一个描述COLUMN,其中包含200000个date1和400000个data2,RLE 压缩处理这种数据为类似data1 200000 data2 400000这样的效果。对于那些没有 很多重复值的数据RLE是不适合的,而且还可能会显著的增加存储文件的尺寸。 RLE压缩有4中级别。级别越高,压缩效率越搞,但压缩速度也会越低。 使用了RLE压缩的行存表对4.2.1之前的版本是不兼容的。若需要将这些表备份 并在之前的版本上恢复,可在恢复操作前,先将这些表ALTER为无压缩或者旧 版本兼容的压缩模式(ZLIB或QUICKLZ),再执恢复操作,因为RLE模式的表定 义在4.2.1之前的版本不兼容。 使用列级压缩 在CREATE TABLE、ALTER TABLE和CREATE TYPE命令中包含对COLUMN 设置压缩类型、压缩级别和块尺寸(Block Size)的选项。这些参数统称为存储参 数。存储参数可用于行导向和列导向的AO表。下面列举这3种存储参数及每种 参数的可选值。 名称 解释 可选值 COMPRESSTYPE 使用的压缩类型 ZLIB(更高压缩) QUICKLZ(更快压缩) RLE_TYPE(运行长度编码) none(无压缩、缺省值) COMPRESSLEVEL 压缩级别 ZLIB 为 1-9 级可选 1 级压缩较快但压缩率较低,9 级压缩较慢但压缩率较高 QUICKLZ 仅 1 个级别可选(缺省不需指定) RLE_TYPE 为 1-4 级可选 1 级压缩较快但压缩率较低,4 级压缩较慢但压缩率较高 BLOCKSIZE 表的存储块大小 8192 – 209715(8K – 2M)该值必须是 8192 的倍数 使用存储参数的格式如下: * ENCODING ( storage_directive *,…+ ) + 这里ENCODING关键字是必须的,存储参数包含3个部分:参数名称、等于号、 参数值。要指定多个存储参数,用逗号分(,)割即可。存储参数可以应用在单独 的COLUMN上,还可以作为所有COLUMN的默认设置,如下面的CREATE TABLE语句所示: 一般用法: column_name data_type ENCODING ( storage_directive *, … + ), … COLUMN column_name ENCODING ( storage_directive *, … + ), … DEFAULT COLUMN ENCODING ( storage_directive *, … + ) 例如: C1 char ENCODING (compresstype=quicklz, blocksize=65536) COLUMN C1 ENCODING (compresstype=quicklz, blocksize=65536) DEFAULT COLUMN ENCODING (compresstype=quicklz) 缺省压缩属性 在未指定压缩方式、压缩级别和块尺寸的情况下,缺省不使用压缩存储,而块 大小使用系统配置参数block_size指定的值。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 52 - 压缩设置的优先级 COLUMN的压缩设置通过TABLE向分区再到子分区传递。在越低级别的设置 具有越高的优先级。  子分区层面的COLUMN压缩设置将覆盖分区、COLUMN和TABLE层面的 设置。  分区层面的COLUMN压缩设置将覆盖COLUMN和TABLE层面的设置。  COLUMN层面的压缩设置将覆盖整个TABLE层面的设置。 注意:包含存储参数或者COLUMN层面的存储参数的表不可以被继承 (INHERIT)。若使用LIKE子句来创建TABLE,表层面的存储参数以及COLUMN 层面的存储参数都会被忽略。也就是说,如果你要为一张分区表新增一个分区 的话,需要为该分区指定你所期望的压缩设置,而不要指望其可以从父表继承。 列压缩设置的最佳定位 最佳的方法是根据不同的数据设置特定的列压缩。下面的例5展示了2级分区表 在子分区上使用RLE_TYPE压缩。 存储参数的例子 下面的例子展示了在使用CREATE TABLE语句时使用存储参数。 例1 该例子中,COLUMN c1使用ZLIB压缩并使用系统定义的块尺寸。COLUMN c2 使用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3不使用压缩且使用系统 定义的块尺寸。 CREATE TABLE T1 (c1 int ENCODING (compresstype=zlib), c2 char ENCODING (compresstype=quicklz, blocksize=65536), c3 char) WITH (appendonly=true, orientation=column); 例2 该例子中,COLUMN c1使用ZLIB压缩并使用系统定义的块尺寸。COLUMN c2 使用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3使用RLE_TYPE压缩并 使用系统定义的块尺寸。 CREATE TABLE T2 (c1 int ENCODING (compresstype=zlib), c2 char ENCODING (compresstype=quicklz, blocksize=65536), c3 char, COLUMN c3 ENCODING (RLE_TYPE) ) WITH (appendonly=true, orientation=column) 例3 该例子中,COLUMN c1使用ZLIB压缩并使用系统定义的块尺寸。COLUMN c2 使用QUICKLZ压缩并使用块尺寸为65536。COLUMN c3使用RLE_TYPE压缩并 使用系统定义的块尺寸。值得注意的是在子分区的定义中,COLUMN c3使用 了ZLIB(不是RLE_TYPE)压缩,由于分区中的COLUMN存储设置的优先级比 TABLE层面的COLUMN存储设置的优先级高,实际上c3使用的是ZLIB压缩而 非RLE_TYPE压缩。 CREATE TABLE T3 (c1 int ENCODING (compresstype=zlib), c2 char ENCODING (compresstype=quicklz, blocksize=65536), c3 char,COLUMN c3 ENCODING (compresstype=RLE_TYPE)) WITH (appendonly=true, orientation=column) PARTITION BY RANGE (c3) ( Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 53 - START ('1900-01-01'::DATE) END ('2100-12-31'::DATE), COLUMN c3 ENCODING (zlib) ); 例4 该例子中,创建TABLE时,COLUMN c1直接指定了存储设置。而COLUMN c2 没有指定存储设置,因此其将从DEFAULT COLUMN ENCODING子句继承压 缩方式(QUICKLZ)和块尺寸(65536)。 COLUMN c3指定了压缩方式为RLE_TYPE,而块尺寸(65536)从DEFAULT COLUMN ENCODING子句继承而来。 COLUMN c4没有压缩。因为缺省列存储设置指定了压缩模式,COLUMN c4需 要显式的指定压缩模式为none来覆盖默认的压缩设置。而块尺寸没有显式的覆 盖设置,因此,其块尺寸为65536. CREATE TABLE T4 (c1 int ENCODING (compresstype=zlib), c2 char, c3 char, c4 smallint ENCODING (compresstype=none), DEFAULT COLUMN ENCODING (compresstype=quicklz, blocksize=65536), COLUMN c3 ENCODING (compresstype=RLE_TYPE) ) WITH (appendonly=true, orientation=column); 例5 该例子中,创建一个2层分区表。如果COLUMN j的值为1或者2,该ROW将进 入RLE_TYPE压缩的分区,如果COLUMN j的值为其他,该ROW将进入ZLIB 压缩的分区。所有其他的COLUMN使用QUICKLZ压缩。 CREATE TABLE T5 ( i int, j int, k date, DEFAULT COLUMN ENCODING (blocksize=1048576) ) WITH (appendonly = true, orientation=column) PARTITION BY RANGE(k) SUBPARTITION BY LIST(j) SUBPARTITION TEMPLATE (PARTITION one_two VALUES(1, 2) COLUMN j ENCODING (compresstype=RLE_TYPE), PARTITION rest VALUES(3, 4, 5, 6, 7, 8, 9, …) COLUMN j ENCODING (compresstype=zlib, compresslevel=9), DEFAULT COLUMN ENCODING (compresstype=quicklz) ) ( START (date '2011-01-01') END (date '2011-12-31') EVERY (interval '1 day') ); 若在已有的TABLE上设置COLUMN的压缩设置,使用ALTER TABLE命令。 通过TYPE命令的方式设置压缩配置 一个TYPE可以包含3个压缩参数。关于添加这些参数到TYPE的语法和限制, Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 54 - 参考相关的CREATE TYPE命令。下面的命令使用精简的方式创建压缩表。 CREATE TABLE t2 (c1 comptype) WITH (APPENDONLY=true, ORIENTATION=column); 这里的comptype的定义为: CREATE TYPE comptype ( internallength = 4, input = comptype_in, output = comptype_out, alignment = int4, default = 123, passedbyvalue, compresstype="quicklz", blocksize=65536, compresslevel=1 ); 注意:译者不建议使用这种不显眼的方式,虽然在定义TABLE时看起来精简了 不少,但对于他人来说,阅读和理解可能都存在障碍。另外替代原生TYPE的定 义未必适应所有情况。建议慎用。 选择块尺寸 在一个TABLE中,每个块尺寸意味着相应数量byte的存储。块尺寸必须在8192 到2097152之间,并且必须是8192的倍数。缺省值为32768。需要注意的是,指 定大的块大小会消耗大量的内存资源。块尺寸决定着存储层的尺寸,在GP中, 每个块作为一部分数据来维护,因此多分区表和列存储表都会消耗更多的内存。 变更表 ALTER TABLE命令用以改变现有表的定义。通过ALTER TABLE命令可以改变TABLE 的各种属性,如:列定义、分布策略、存储模式和分区结构(可参见”维护分区 表”章节)等。例如,将TABLE的一个COLUMN添加一个非空限制: => ALTER TABLE address ALTER COLUMN street SET NOT NULL; 改变表的分布 ALTER TABLE命令提供了改变分布策略的选项。在修改TABLE的分布策略时,表 中的数据要在磁盘上做重分布,该操作可能需要密集的资源消耗。还有一个按 照现有策略重新分布数据的选项。 修改分布策略 ALTER TABLE命令可用于改变表的分布策略。对于分区表来说,修改分布策略会 递归的应用于所有的子分区。该操作不会改变表的OWNER以及其他TABLE属性。 例如,下面的命令在所有Segment之间按照customer_id作为DK重分布sales表: ALTER TABLE sales SET DISTRIBUTED BY (customer_id); 在修改TABLE的HASH分布时,表数据会自动重新分布。然而,将分布策略改为 随机分布时不会重新分布数据。例如: ALTER TABLE sales SET DISTRIBUTED RANDOMLY; 重分布表数据 对于随机分布策略或者不改变分布策略的表,要重分布TABLE的数据,使用 REORGANIZW=TRE。这在处理数据倾斜问题时可能是很必要的,在添加新的 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 55 - Segment节点资源时也是必要的。比如: ALTER TABLE sales SET WITH (REORGANIZE=TRUE); 该命令会在Segment之间按照现有的分布策略(包括随机分布策略)重新平衡表 中数据。 修改表的存储模式 在TABLE被创建之后,修改表的存储模式是不可能的。存储模式只能在CREATE TABLE时被指定。如果要修改现有表的存储模式,必须使用正确的存储选项重 建该表,重新加载数据到新的表,删除旧的表,修改新表为旧的表明。另外还 必须重新授权表的权限。例如: CREATE TABLE sales2 (LIKE sales) WITH (appendonly=true, compresstype=quicklz, compresslevel=1, orientation=column); INSERT INTO sales2 SELECT * FROM sales; DROP TABLE sales; ALTER TABLE sales2 RENAME TO sales; GRANT ALL PRIVILEGES ON sales TO admin; GRANT SELECT ON sales TO guest; 在现有表上添加压缩列 可以使用ALTER TABLE命令来添加一个压缩列。关于压缩列的设置,参见”使用 列级压缩”章节。下面的例子演示了在现有的T1表上增加zlib压缩列: ALTER TABLE T1 ADD COLUMN c4 int DEFAULT 0 ENCODING (COMPRESSTYPE=zlib); 继承压缩设置 在增加一个子分区(非一级分区)时,新的分区将继承子分区的压缩设置。下面 的例子演示了创建一个带子分区设置的表,然后增加一个分区: CREATE TABLE ccddl (i int, j int, k int, l int) WITH (APPENDONLY = TRUE, ORIENTATION=COLUMN) PARTITION BY range(j) SUBPARTITION BY list (k) SUBPARTITION template( SUBPARTITION sp1 values(1, 2, 3, 4, 5), COLUMN i ENCODING(COMPRESSTYPE=ZLIB), COLUMN j ENCODING(COMPRESSTYPE=QUICKLZ), COLUMN k ENCODING(COMPRESSTYPE=ZLIB), COLUMN l ENCODING(COMPRESSTYPE=ZLIB)) (PARTITION p1 START(1) END(10), PARTITION p2 START(10) END(20)); ALTER TABLE ccddl ADD PARTITION p3 START(20) END(30); 运行ALTER TABLE命令创建了TABLE ccddl的分区ccddl_1_prt_p3和 ccddl_1_prt_p3_2_prt_sp1。分区ccddl_1_prt_p3与子分区sp1继承了不同的压缩 设置。译者提醒:这里的子分区(sp1)其实是沿用了SUBPARTITION TEMPLATE的 设置,而不是所谓的继承,在GP中,分区的存储设置不会自动从父级分区继承 下来,需要手动执行,SUBPARTITION TEMPLATE与普通的继承不是一回事,望 读者细细揣摩理解。你可以认为这是一个bug,当然也可以认为这是一种灵活 的设计思路。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 56 - 删除表 可通过DROP TABLE命令从DB中删除表。例如: DROP TABLE mytable; 要想在不删除表定义的情况下清空表中的记录,使用DELETE或TRUNCATE命令。 例如: DELETE FROM mytable; TRUNCATE mytable; DROP TABLE会删掉所有与该表相关的索引、规则、触发器、限制等。然而要一 起删除与该表相关的视图VIEW,必须使用CASCADE。CASCADE会删除所有依赖 该TABLE的VIEW。例如: DROP TABLE mytable CASCADE; 分区大表 表分区用以支持解决特别大的表的问题,比如事实表,解决办法就是将表分成很多 小且更容易管理的部分。分区表在执行给定的查询语句时扫描相关的部分数据而不 是全表的数据从而提高查询性能。分区表对于数据库的管理也有帮助,比如在数据 仓库中滚动旧的数据。 理解 GPDB 的表分区 TABLE在CREATE TABLE时使用PARTITION BY(以及可选的SUBPARTITION BY)子句来 做分区。在GPDB中对一张表做分区,实际上是创建了一张顶层(父级)表和多个 低层(子级)表。在内部,GPDB在顶级表与低级表之间创建了继承关系(类似于 PostgreSQL中的继承/INHERIT功能)。 根据分区创建时定义的准则,每个分区在创建时都带有一个不同的检查(CHECK) 约束,其限制了该表可以包含的数据。该检查约束还用于查询规划器在执行特 定的查询语句时决定扫描那些分区。 分区层级关系被储存在GP系统日志表中,因此插入到顶级父表的数据将被传到 相应的分区表中。任何对分区结构的修改或者TABLE结构的修改都需要通过父 表使用PARTITION子句结合ALTER TABLE命令来完成。 GPDB支持范围(根据数值型的范围分割数据,比如日期或价格)分区和列表(根据 值列表分区,比如区域或生产线)分区,或者两种类型的结合。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 57 - 分区表和其他非分区表一样都是在GPDB的Segment之间分布的。表在GPDB的 Segment之间物理性的分布可以确保并行查询处理。表分区是一种大表逻辑切 分和数据仓库任务的工具。分区本身不会改变Segment间数据物理上的分布规 律。 决定表的分区策略 并不是任何TABLE都适合做分区的。如果下列问题的全部或者大部分的答案是 yes,这样的表可以通过分区策略来提高查询性能。如果大部分的答案是no,分 区不是好的方案:  表是否足够大?大的事实表适合做表分区。若在一张表中有百万级甚至数 亿条记录,从逻辑上把表分成较小的分区将可以改善性能。而对于只有数 千条或者更少记录的表,对分区预先进行的管理开销将远大于可以获得的 性能改善。  对目前的性能不满意?作为一种调优方案,应该在查询性能低于预期时再 考虑表分区。  查询条件是否能匹配分区条件?检查查询语句的WHERE条件是否与考虑 分区的COLUMN一致。例如,如果大部分的查询使用日期条件,那么按照 月或者周的日期分区涉及也许很有用,而如果查询条件更多的是使用地区 条件,可以考虑使用地区将表做列表形势的分区。  数据仓库是否需要滚动历史数据?历史数据的滚动需求也是分区设计的 考虑因素。比如,数据仓库中仅需要保留过去两个月的数据。如果数据按 照月进行分区,将可以很容易的删除掉两个月之前的数据,而最近的数据 存入最近月份的分区即可。  按照某个规则数据是否可以被均匀的分拆?应该选择尽量把数据均匀分 布的规则。若每个分区储存的数据量相当,那么查询性能的改善将与分区 的数量相关。例如,把一张表分为10个分区,命中单个分区条件的查询扫 表性能将比为分分区的情况下高10倍。译者认为,我们不应该简单的说查 询性能是10倍,因为多数的查询不是count(*)这样简单的计数,若是其他耗 时的运算,除了扫表过滤数据这部分可以提升外,后续的处理部分是不会 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 58 - 有性能提升的。 创建分区表 TABLE只能在执行CREATE TABLE命令时被分区。 表做分区的第一步是选择分区类型(范围分区、列表分区等)和分区字段。决定 分区的层数。例如,先按照日期范围划分一级月分区,再将月分区按照区域做 二级列表分区。本节通过示例演示如何创多种分区表。  定义日期范围分区表  定义数字范围分区表  定义列表分区表  定义多级分表  将现有表分区 定义日期范围分区表 日期范围分区表使用单个date或者timestamp字段作为分区键。如果需要,还可 以使用同样的字段做子分区(比如按照月分区后再按照日做子分区)。使用日期 分区时优先考虑直接使用最细粒度的分区。比如,设置365个日分区,而不是 先设置年分区再设置月分区再设置日分区。多级分区会降低执行计划的时间, 但水平的分区设计可以提高执行的速度。 可以通过使用START值、END值和EVERY子句定义分区增量让GPDB自动产生分区。 缺省情况下,START值总是被包含而END值总是被排除。例如: CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 day') ); 不过也可以为每个分区单独指定名称。比如: CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE , PARTITION Feb08 START (date '2008-02-01') INCLUSIVE , PARTITION Mar08 START (date '2008-03-01') INCLUSIVE , PARTITION Apr08 START (date '2008-04-01') INCLUSIVE , PARTITION May08 START (date '2008-05-01') INCLUSIVE , PARTITION Jun08 START (date '2008-06-01') INCLUSIVE , PARTITION Jul08 START (date '2008-07-01') INCLUSIVE , PARTITION Aug08 START (date '2008-08-01') INCLUSIVE , PARTITION Sep08 START (date '2008-09-01') INCLUSIVE , PARTITION Oct08 START (date '2008-10-01') INCLUSIVE , PARTITION Nov08 START (date '2008-11-01') INCLUSIVE , PARTITION Dec08 START (date '2008-12-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE ); Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 59 - 注意:由于分区的范围限制是连续的,不需要为每个分区指定END值,而只需 要为最后一个分区指定即可。但如果分区的范围不是连续的,可以考虑指定END 值。 定义数字范围分区表 数字范围分区表使用单个数字列作为分区键。例如: CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2001) END (2008) EVERY (1), DEFAULT PARTITION extra ); 关于默认分区的更多信息,参见”添加默认分区”相关章节。 定义列表分区表 列表分区表可以使用任何数据类型的列作为分区键,分区规则使用等值比较。 列表分区可以使用多个COLUMN(组合起来)作为分区键,而范围分区只允许使用 单独COLUMN作为分区键。对于列表分区,必须为每个分区指定相应的值。例 如: CREATE TABLE rank (id int, rank int, year int, gender char(1), count int ) DISTRIBUTED BY (id) PARTITION BY LIST (gender) ( PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other ); 关于默认分区的更多信息,参见”添加默认分区”相关章节。 定义多级分区表 当需要子分区时,可以使用多级分区的涉及。使用subpartition template来确保 每个分区具有相同的子分区结构,尤其是对那些后增加的分区来说。例如,创 建一个两层的分区表: CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), DEFAULT SUBPARTITION other_regions) START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates ); 下面是一个3级分区表的例子,这里表sales被分区为年、月、区域。SUBPARTITION TEMPLATE子句确保每个年分区有相同的子分区结构。另外,每个级别的分区都 有一个默认分区: CREATE TABLE sales (id int, year int, month int, day int, region text) DISTRIBUTED BY (id) PARTITION BY RANGE (year) SUBPARTITION BY RANGE (month) Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 60 - SUBPARTITION TEMPLATE ( START (1) END (13) EVERY (1), DEFAULT SUBPARTITION other_months ) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions ) ( START (2002) END (2010) EVERY (1), DEFAULT PARTITION outlying_years ); 将现有表分区 对已经创建的表是不能分区的。只能在CREATE TABLE的时候做分区。要想对现 有的表做分区,只能重新创建一个分区表、重新装载数据到新的分区表中、删 掉旧表然后把新的分区表改为旧表的名称。还必须重新对TABLE做授权。例如: CREATE TABLE sales2 (LIKE sales) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); INSERT INTO sales2 SELECT * FROM sales; DROP TABLE sales; ALTER TABLE sales2 RENAME TO sales; GRANT ALL PRIVILEGES ON sales TO admin; GRANT SELECT ON sales TO guest; 分区表的限制 主键或者唯一约束必须包含表上的所有分区键。而唯一索引可以不包含分区键, 但是,其只对一个分区强制有效,而不是对整个分区表有效。 装载分区表 一旦创建了分区表,顶级表总是空的。数据值储存在最低层的表中。在多级分 区表中,仅仅在层级最低的子分区中有数据。 如果某行记录无法匹配到子分区,该数据将会被拒绝并致使装载失败。若不希 望在任何时候都出现记录在装载时被拒绝,可以选择定义默认分区。这样所有 不能匹配分区CHECK约束的数据将装载到默认分区。可参考相关的”添加默认分 区”章节。 在运行期间,执行规划器会扫描整个TABLE的层级结构并使用CHECK约束适配查 询条件来决定哪些子表需要被扫描。默认分区(只要该层级中存在)总是会被扫 描的。如果默认分区中包含数据,其会拖慢整体的扫表时间。 在使用COPY或者INSERT向父级表装载数据时,这些数据会默认自动路由到正确 的分区。因此,可以普通的未分区表一样装载分区表。 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 61 - 如果有必要,还可以直接把数据装载到子表中。还可以先创建一种中间表、装 载数据、然后与分区表进行分区交换。这种分区交换的性酴直接的COPY和 INSERT。参考相关的”交换分区”章节。 验证分区策略 表分区的目的是减少给定查询的数据扫描数量。若一张表基于相应的查询条件 做分区,可以使用EXPLAIN查看执行计划来验证执行执行计划是否有选择的扫描 相关的数据而不是全表扫描。 例如,假设有一张表sales已经根据日期按月做范围分区,同时按区域做了子分 区,参见” 定义多级分区表”章节的例子。对于下面的查询: EXPLAIN SELECT * FROM sales WHERE date='01-07-08' AND region='usa'; 对于这个执行计划应该只显示对扫描下列表的扫描:  默认分区返回0-1条数据  2008年1月分区(sales_1_prt_1)返回0-1条数据  USA地区子分区(sales_1_2_prt_usa)返回一些记录 下面是相关部分查询计划的示例: -> Seq Scan on sales_1_prt_1 sales (cost=0.00..0.00 rows=0 width=0) Filter: "date"=01-07-08::date AND region='USA'::text -> Seq Scan on sales_1_2_prt_usa sales (cost=0.00..9.87 rows=20 width=40) 要确保查询计划没有扫描不必要的分区或者子分区。并且顶层表的扫表返回0-1 条数据。 分区选择性扫描的限制 如果执行计划显示分区表没有被选择性的扫描,可能和以下的限制有关:  执行计划仅可以对稳定的比较操作符执行选择性扫描,如: = < <= > >= <>  执行计划不识别非稳定函数来执行选择性扫描。比如,WHERE子句中使 用如date > CURRENT_DATE会使得执行计划选择性的扫描分区表,而 time > TIMEOFDAY不会。 查看分区设计 要查看分区表的设计情况,通过pg_partitions视图查看。比如,查看sals表的分 区情况: SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank FROM pg_partitions WHERE tablename='sales'; 另外还有如下的视图查看分区表的信息:  pg_partition_templates - 用以创建SUBPARTITION的SUBPARTITION template  pg_partition_columns – 用于分区的分区键 Greenplum4.2.2 管理员指南 翻译:陈淼 miaochen@mail.ustc.edu.cn - 62 - 维护分区表 必须使用ALTER TABLE命令从顶级表来维护分区。最常见的场景是根据日期 范围的设计来维护数据时,删除旧分区并添加一个新的分区。还有一种可能就 是把旧的分区交换为压缩AO表以节省空间。若在父表中存在默认分区,添加 分区的操作只能是从默认分区拆分出一个新的分区。  添加新分区  重命名分区  添加默认分区  删除分区  清空分区  交换分区  拆分分区  修改子分区模版(Subpartition Template) 重要提示:在定义个更改分区时,使用分区名称而不是分区TABLE的名称。虽 然可以使用SQL命令直接针对分区表进行查询和装载操作,但只能通过ALTER TABLE…PARTITION子句来修改分区结构。 由于分区不要求有名称,若分区没有名称,下面的表达式仍可以指定一个分区: PARTITION FOR (value) or PARTITION FOR(RANK(number)) 添加新分区 可以使用ALTER TABLE命令在已有的分区表上添加新分区。如果原有的分区 表包含了subpartition template设计,新增的分区将根据该模版创建子分区。例 如: ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE; 如果在创建TABLE时没有subpartition template,在新增分区时需要定义子分区: ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe') ); 如果要在现有分区上添加子分区,可以指定分区执行ALTER。例如: ALTER TABLE sales ALTER PARTITION FOR (RANK(12)) ADD PARTITION africa VALUES ('africa'); 注意:若有默认分区的分区表中添加新的分区,只能从默认分区拆分出一个新 的分区。参见”拆分分区”相关章节。 重命名分区
还剩61页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 11 人已下载

下载pdf

pdf贡献者

88909803

贡献于2013-01-16

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf