Greenplum 数据库最佳实践(12)

警告：如果表严重臃肿，建议运行 VACUUM 前运行 ANALYZE。因为 ANALYZE使用块级别抽样，如果一个表中无效/有效行的块的比例很高，则ANALYZE会设置系统表 pg_class 的reltuples 字段为不精确的值或者0，这会导致查询优化器不能生成最优查询。VACUUM命令设置的数值更精确，如果在 ANALYZE 之后运行可以修正不精确的行数估计。 如果一个表膨胀严重，运行VACUUM命令是不够的。对于小表，可以通过 VACUUM FULL 回收溢出 FSM 的空间，降低表的大小。然而VACUUM FULL 操作需要 ACCESS EXCLUSIVE 锁，可能需要非常久或者不可预测的时间才能完成。注意，消除大表膨胀的每种方法都是资源密集型操作，只有在极端情况下才使用。

第一种方法是创建大表拷贝，删掉原表，然后重命名拷贝。这种方法使用 CREATE TABLE AS SELECT 创建新表，例如：

gpadmin=# CREATE TABLE mytable_tmp AS SELECT * FROM mytable;gpadmin=# DROP TABLE mytable;gpadmin=# ALTER TABLE mytabe_tmp RENAME TO mytable; 第二种消除臃肿的方法是重分布。步骤为：

1. 记录表的分布键2. 修改表的分布策略为随机分布 ALTER TABLE mytable SET WITH (REORGANIZE=false) DISTRIBUTED randomly; 此命令修改表的分布策略，但是不会移动数据。这个命令会瞬间完成。3. 改回原来的分布策略 ALTER TABLE mytable SET WITH (REORGANIZE=true) DISTRIBUTED BY (); 此命令会重新分布数据。因为和原来的分布策略相同，所以仅会在同一个段数据库上重写数据，并去掉过期行。 消除系统表臃肿

Greenplum数据库系统表也是堆表，因而也会随着时间推移而变得臃肿。随着数据库对象的创建、修改和删除，系统表中会留下过期行。使用 gpload 加载数据会造成臃肿，因为它会创建并删除外部表。（建议使用gpfdist加载数据）。

系统表膨胀会拉长表扫描所需的时间，例如生成解释计划时。系统表需要频繁扫描，如果它们变得臃肿，那么系统整体性能会下降。 建议每晚(至少每周)对系统表运行VACUUM。 下面的脚本对系统表运行VACUUM ANALYZE。

#!/bin/bashDBNAME=\\VCOMMAND' || ' pg_catalog.' || relname || ';'FROM pg_class a, pg_namespace b

where a.relnamespace=b.oid andb.nspname='pg_catalog' and a.relkind='r'\psql -a $DBNAME

如果系统表变得异常臃肿，则必须执行一次集中地系统表维护操作。对系统表不能使用CREATE TABLE AS SELECT和上面介绍的重分布方法消除臃肿，而只能在计划的停机期间，运行VACUUM FULL。在这个期间，停止系统上的所有系统表操作，VACUUM FULL会对系统表使用排它锁。定期运行VACUUM可以防止这一代价高昂的操作。 消除索引表臃肿

VACUUM 命令仅恢复数据表的空间，要恢复索引表的空间，使用 REINDEX 重建索引。 使用REINDEX table_name重建一个表的所有索引；使用REINDEX index_name重建某个索引。 消除AO表臃肿

AO 表的处理方式和堆表完全不同。尽管AO表可以更新和删除，然而AO表不是为此而优化的，建议对AO表避免使用更新和删除。如果AO表用于一次加载/多次读取的业务，那么AO表的 VACUUM 操作几乎会立即返回。

如果确实需要对AO表执行UPDATE或者DELETE，则过期行会记录在辅助的位图表中，而不是像堆表那样使用空闲空间映射表。使用VACUUM回收空间。对含有过期行的AO表运行VACUUM会通过重写来精简整张表。如果过期行的百分比低于配置参数

gp_appendonly_compaction_threshold, 则不会执行任何操作，默认值是10（10%）。每个段数据库(Segment)上都会检查该值，所以有可能某些Segment上执行空间回收操作，而另一些Segment不执行任何操作。可以通过设置gp_appendonly_compaction参数为no禁止AO表空间回收。

? 监控Greenplum数据库日志文件

了解系统日志文件的位置和内容，并定期的监控这些文件。

下标列出了Greenplum数据库各种日志文件的位置。文件路径中，date 是格式为 YYYYMMDD 的日期，instance 是当前实例的名字，n 是Segment号。

首先使用 gplogfilter -t(--trouble)从Master日志中搜索 ERROR:, FATAL:, PANIC: 开头的消息。WARNING 开头的消息也可能提供有用的信息。

若需搜索段数据库（Segment）日志，从主服务器（Master）上使用gpssh连接到Segment上，然后使用gplogfilter工具搜索消息。可以通过Segment_id 识别是哪个 Segment的日志。

配置参数log_rotation_age控制什么时候会创建新的日志文件。默认情况下，每天创建一个新的日志文件。

第六章加载数据

Greenplum数据库支持多种数据加载方式，每种都有其适用的场景。

? INSERT 语句直接插入字段数据

带有字段值的INSERT语句插入单行数据。数据通过主服务器（Master）分发到某个段数据库（Segment）。这是最慢的方法，不适合加载大量数据。

? COPY语句

PostgreSQL 的 COPY 语句从外部文件拷贝数据到数据库表中。它可以插入多行数据，比INSERT语句效率高，但是所有行数据仍然会通过Master。所有数据由一个命令完成拷贝，不能并行化。

COPY命令的数据输入可以是文件或者标准输入。例如： COPY table FROM '/data/mydata.csv' WITH CSV HEADER;

COPY适合加载少量数据，例如数千行的维度表数据或者一次性数据加载。 使用脚本加载少于10k行数据时可以使用 COPY。

COPY是单个命令，所以不需要禁用自动提交（autocommit）。 可以并发运行多个 COPY 命令以提高效率。

? 外部表

Greenplum数据库外部表可以访问数据库之外的数据。可以使用SELECT访问外部数据，常用语抽取、加载、转换（ELT）模式中。ELT 是 ETL 模式的一个变种，可以充分利用Greenplum数据库的快速并行数据加载能力。

使用ETL时，数据从数据源抽取，在数据库之外使用诸如Informatica或者Datastage的外部工具进行转换，然后加载到数据库中。

使用ELT时，Greenplum外部表提供对外部数据的直接访问，包括文件（例如文本文件、CSV或者XML文件）、Web服务器、Hadoop文件系统、可执行操作系统程序或者下节介绍的gpfdist文件服务器。外部表支持选择、排序和关联等SQL操作，所以数据可以同时进行加载和转换；或者加载到某个表后在执行转换操作，并最终插入到目标表中。 外部表定义使用 CREATE EXTERNAL TABLE 语句，该语句的LOCATION 子句定义了数据源，FORMAT 子句定义了数据的格式以便系统可以解析该数据。外部文件数据使用 file:// 协议，且必须位于Segment主机上可以被Greenplum超级用户访问的位置。可以有多个数据文件，LOCATION子句中文件的个数就是并发读取外部数据的Segments的个数。

? 外部表和gpfdist

加载大量事实表的最快方法是使用外部表与gpfdist。gpfdist 是一个基于HTTP协议的、可以为Greenplum数据库的段数据库（Segments）并行提供数据的文件服务器。单个gpfdist实例速率可达 200MB/秒，多个gpfdist进程可以并行运行，每个提供部分加载数据。当使用 INSERT INTO SELECT *FROM 语句加载数据时，这个INSERT语句由Master解析，并分发到各个主Segments上。每个Segment连接到gpfdist服务器，并行获取数据、解析数据、验证数据、计算分布键的哈希值，并基于哈希值分布到存储该数据的目标Segment上。默认情况下，每个gpfdist实例 …… 此处隐藏：3096字，全部文档内容请下载后查看。喜欢就下载吧 ……