《Greenplum5.0 最佳实践》模式设计（三）-CSDN博客

模式设计最佳实践

Greenplum 是基于大规模并行处理（MPP）和shared-nothing架构的分析型数据库。其不同于高度规范化的事务型SMP数据库。使用非规范化数据库模式，例如具有大事实表和小维度的星型或者雪花模式，处理MPP分析型任务时，Greenplum数据库表现优异。

数据类型（Data Types）

使用类型一致

在做关联操作的两个表，其两个关联字段的数据类型尽量保持一致。如果数据类型不一致，Greenplum 数据库必然后动态的转化一个字段的数据类型，这样就可以实现不同类型间的字段比较操作。对于这种情况，可能需要增加数据类型的大小，以便关联操作更高效。

选择合适的数据类型减少空间浪费
通过选择最有效的数据类型存储数据，来提高整个数据库集群的容量和查询的响应高效。
例如：使用TEXT 或者 VARCHAR 代替CHAR。在查询响应上，这三种数据类型间没有任何差距，但是在存储空间的使用上，TEXT和VARCHAR 就显的更经济，使用最小的数据类型来容纳数据，例如使用INT或者 SMALLINT 代替 BIGINT 将会节省更多的存储空间

存储模型

在创建表时，可以选择存储模型，这是非常重要的，我们可以使用append-optimized (AO) storage, 该模型支持行存和列存, 堆与AO的行列选择对大维度的事实表非常重要，但是对于小尺寸的表不填重要。有如下规则：
最佳实践如下：

1.对于直插入模型，在加载数据前删除每天的分区表

对于大型事实分区表，评估并优化不同分区的存储选项。一种存储模型可能满足不了整个分区表的不同应用场景。例如，一些分区表可以面向行存储，一些分区表可以面向列存储。
2.当使用面向列的存储模式时，每个列都是Greenplum数据库段中的单独的物理文件。对于具有大量列的表，考虑对于经常访问的（热）数据使用列式存储，不经常访问的（冷）数据采用行存储。
3.在分区级别或者在数据存储级别上设置存储类型。
4.在集群中，压缩大表可以提高I/O性能和节省磁盘空间。

堆存储和 AO存储模型

堆存储是默认的存储模型，是PostgreSQL 数据库中默认的存储方式。使用堆存储对于表和分区表将更适合经常UPDATE和DELETE或者单行INSERT操作。使用堆存储同样适合对于表和分区表执行并行的UPDATE和DELETE，INSERT操作.

使用Append-optimized 存储模型适合存储不经常UPDATE的表。避免执行单条的INSERT UPDATE 或者DELETE操作对于append-optimized 表。
Append-Optimized 表适合加载一次，多次查询，很少更新，批量插入操作。
注：之所以不适合单次插入，因为AO表的磁盘分配原则，每次插入默认申请一个block，将新插入的数据存入Block中。而不是利用已有的空闲block。

行存或者列存

行存储 是最传统的数据存储方式用来存储数据元组。在一行中所有的列属性字段连续的存储在磁盘上，因此一次单一的I/O开销，读取的是1整行记录。

列存储 是一列所有的值存储在一起放，且该列占有一个独立的物理文件。如果这个表是分区表，将会为每一个分区的每一列创建单独的存储文件。使用列存储时，当一个查询只访问很少的列时，与行对照表相比，I/O的成本大大降低，任何查询不涉及的列，都不会从磁盘中检索。

行存储方式推荐用于交易类型的任务中，这些任务要求频繁的insert 和 update 操作。使用行存储方式是对于每次查询都涉及到很多字段。如果这些主要的列在SELECT中或者在WHERE子句中，那么使用行存储方式。使用行存储方式适合通用的或者复合的混合型负载，它提供了最好的灵活性和性能最佳的组合。

列式存储方式是提供的对读取的优化，而不是提供的对写入的优化；列属性的值对于行来说，写入了不同的磁盘位置。使用列存储方式存储大数据量的表，当对少数列访问时，可以提供更好的性能。
由于列式存储是相同的属性值的列的值存在一起的单独物理文件，相比于行存方式将混合的属性值存在一个物理文建。占用的磁盘空间更少。所以相比于行存储其能够更好的减少I/O开销。在对表数据压缩上，由于列式的属性类型相同，将会有更好的压缩比。

使用列式存储方式适合分析型数据仓库，当 SELECT 很少的列数据集或者做聚合计算时，推荐使用列式存储方式。需要定期修改某一个字段的值时，t推荐使用列式存储。如果对于使用列存的表执行全表扫描读取时，将会花费更多的时间。
最重要的是，要铭记，使用列式存储时每一个单独的列都是Greenplum中，段数据里面的单独的物理文件。

压缩操作

Greenplum数据库对去AO表或者AO分区表提供了多种压缩操作。使用压缩可以使得一次I/O开销下读取更多的数据。建议为表设置压缩类型。当新添加分区表时，需要明确知道的就是，新添加的分区表并不会继承父表的压缩类型和压缩等级，需要我们明确指定分区表的压缩类型和压缩等级。
RLE （Run-length encoding）压缩提供了最好的压缩级别。高的压缩级别将会节省更多的磁盘存储空间，但是要求额外的时间和CPU周期来进行数据的解压和压缩操作。
排序插入数据，与各种压缩选项想结合都会获得非常好的压缩率。（数据聚合存储）。不应该将数据压缩方法用于存储在压文件的系统上的数据。
在使用压缩前，应测试一些你的数据到底适合何种压缩等级，和排序方式。例如，你的数据可能适合Zlib方式下，4或5的压缩等级。RLE压缩格式更适合数据中具有重复值的数据。

分布键

分布键的选择决定了最终在Greenplum中，数据的分布。在MPP shared nothing 环境下，整个查询的响应时间是受最慢的那个段数据库返回的时间决定的。如果数据倾斜，回引起某个段数据库将会花费更多的时间区域完成任务。所以每一个段数据库必须有近似相近的数据量，和执行相同的查询进程。低性能和内存不足的情况下，正是因为一些段执行了更多的任务或者处理了更多的数据。

考虑如下的最佳实践：

1.需要明确指出分布键的列或者使用随机分布键，不要使用默认分布键，默认分布键通常是表结构中的第一列。理想情况下，单一的分布键就可以使得表数据均匀的分布到所有的段数据库中。
2.不要使用在WHERE子句中经常出现的字段作为分布键
3.不要使用下列类型作为分布键的列 dates 或者 timestamps
4.选择作为分布键的列要么包含唯一值或者具有较高的基数
5.如果对于一个表来说，使用单一的分布键并不能实现数据的负载均衡，那么推荐使用多属性列作为分布键，最好为两列。因为更多的列并不会产生更均匀的分布，而且会因为分布键的增多，导致整个过程需要更多的hash散列时间（计算hash值的操作）
6.如果两个属性列作为分布键，依就不能保证数据均匀分布，那么推荐使用随机分布键。多列分布键情况下，在执行表间关联操作时，节点间需要更多的移动操（motion），所以相比随机分布，多列分布并不具备优势。

Greenplum 的随机分布并不是使用的 round-robin （轮询）算法，所以它不能保证相等数的记录均匀分配在段数据库上。使用随机分布，通常保证数据分布差异在10%以内。

优化 分布键 对于大表数据来说非常重要，在执行JOIN操作时，想连接的行记录最好在同一个段数据上。如果数据并没有分布在相同的连接字段上，这些行记录必须从一个动态重分布到其他段上。在广播移动操作（Motion）比重分布效果更好。
重分布：每一个段数据库都会重新散列其行记录到其他的数据中根据hash key.

本地链接操作 Local (co-located) Joins

使用一个合理的分布键，将会使得在执行关联（JOIN）操作时，会有很好的性能提升。当关联的记录在同一个段数据段时。可以在段实例上，内部完成关联操作。这叫做本地连接或者同位置连接操作。本地连接可以保证数据移动最少，减少网络开销，减少段之间的通信。
为了尽可能的实现本地连接操作，分布键最好都是使用相同的字段对于要做JOIN操作的表。本地操作要求，两个表的分布键一致，并且表的顺序也尽可能的一致，在连接表时，使用分配子句中的所有列。
分布字段必须要有相同的数据类型，因为，尽管表现形式相同，如果其类型不同，这些值很有可能会被分配在不同的段上。

数据倾斜

数据倾斜 是经常引起查询响应慢和内存溢出的根源。倾斜的数据将会影响数据扫描（读取）的性能，但是这将影响到当前实例的其他查询操作，JOIN和group by操作。验证数据的分布非常重要，在数据初始化加载到Greenplum后，数据最终均匀分布在整个集群。
接下来的查询语句显示了每一个段数据库所含有的记录数量，记录数的最大值和最小值


SELECT 'Example Table' AS  "Table Name", max(c) AS "Max Seg Rows", min(c) AS "Min Seg Rows",

(max(c) - min(c))*100.0/max(c) AS "Percentage Difference Between Max & Min"

FROM (SELECT count ( * ) c, gp_segment_id FROM facts GROUP BY 2) AS a;

gp_toolkit 模式下有两个视图用于帮助我们检查数据倾斜
视图 gp_toolkit.gp_skew_coefficients 视图将会显示数据倾斜的情况通过计算每个段数据库的存储数据的变异系数（CV）。视图中字段 skccoeff 显示的是变异系数他的计算方式是 标准差除以平均值 计算得到的。
该字段代表着数据的平均值和可变性，该值越低越好，越高的值表示更大的数据偏移。

视图 gp_toolkit.gp_skew_idle_fractions 通过计算在表扫描期间空闲的系统百分比，来计算数据的分布倾斜。该方法作为计算倾斜的指示器。
这个 siffraction 字段用来存储系统在扫描表时所耗费系统资源的百分比。这个指标可以表示是计算倾斜或者数据倾斜
例如：0.1 表示的是10%的倾斜， 0.5 代表的是50%的倾斜等等。对于超过10%的倾斜的表，我们应该重新评估该表的分发策略。（如何更改呢？？？）

`SELECT * FROM gp_toolkit.gp_skew_coefficients;`

`SELECT * FROM gp_toolkit.gp_skew_idle_fractions;`

计算倾斜

数据是均匀分布的，但是计算过程中，结果集是非均匀分布的。该如何优化和避免呢？？

当不均匀的数据流流入到一个段数据库或者一小部分段数据库时，处理这些倾斜数据，是引起Greenplum数据库性能和稳定性问题背后的罪魁祸首。它经常发生在JOIN、SORT、AGGREGATION和其他多种OLAP操作。计算倾斜发生在查询任务执行时，并不如数据倾斜那么容易检测处理。都知道，
发生表级别的数据倾斜是因为分布键选取不当引起的，可以通过重新选择分布键来解决该问题。但是如何处理计算倾斜呢？
如果单一段数据库挂掉（并不是一个节点上全部的段数据库实例挂掉），这将会引起计算倾斜的发生。为了定义到底是那个数据库实例（段数据库）出现计算倾斜，我们需要手动调试。
如果有倾斜发生，但是发生倾斜的段并没有产生溢出文件（内存溢出），这将不会引起性能问题。如果我们确定有倾斜发生，那么找到查询对应的倾斜。使用如下命令

1.首先找到发生倾斜的数据库的OID


 SELECT OID， datname FROM pg_database;

输出如下：

oiddatname1template112092template012093postgres16384aladin

运行 gpssh 命令去检查文件的尺寸在当前系统中，所有主机所包含的文件


$ gpssh -f ./host_all -e  
"du -b /home/gpadmin/data/data[1-2]/primary/gpseg&/base/16384/pgsql_tmp/*"  | \
grep -v "du -b " | sort | awk -F " " ' {arr[$1] = arr[$1] + $2; tot = tot +$2};END\
{for (i in arr) print "Segment node" i , arr[i], "bytes (" arr[i]/(1024**3) " GB)" ; \
print "Total", tot, "bytes (" tot/(1024**3)" GB)"}' -

样例输出


$ gpssh -f all_hosts -e " du -b /home/gpadmin/data/data/gpseg&/base/16384/pgsql_tmp/* " |  grep -v " du -b " | sort | awk -F " " ' { arr[$1] = arr[$1] + $2 ; tot = tot + $2 }; END {for (i in arr) print "Segment node" i , arr[i], "bytes (" arr[i]/(10243) " GB)";  print "Total", tot, "bytes (" tot/(10243) " GB)" }'

Segment node[node0] 0 bytes (0 GB) Segment node[node1] 0 bytes (0 GB) Segment node[node2] 0 bytes (0 GB) Segment node[node3] 0 bytes (0 GB) Segment node[node4] 0 bytes (0 GB) Total 0 bytes (0 GB)

如果出现显著和持续的计算倾斜，下一个任务是去定位这个违规的查询任务。上一步的命令操作是所有节点信息的统计。这次，找到实际的段目录。你可以从主机或者通过登录到上一步中标记的所有节点执行操作。一下是从主节点运行的实例。
下面这个实例是专门针对排序文件。不是所有的溢出文件或倾斜文件的情况都是由排序文件引起的。所以，我们需要执行这样的自定义命令：


$ gpssh -f all_hosts -e "ls -l /data[1-2]/primary/gpseg*/base/pgsql_tmp/*"  | grep -i sort | awk '{sub (/base.*tmp\//, ".../", $10);
print $1, $6, $10}' | sort -k2 -n

执行样例命令执行后的输出结果为


$ gpssh -f all_hosts -e "ls -l /home/gpadmin/data/data/gpseg2/base/16384/pgsql_tmp/* " | grep -i sort | awk '{sub (/base.*tmp\//, ".../", $10); \
print $1, $6, $10} ' | sort -k2 -n

从上述结果可以看出，在主机上面gpseg*是异常的。因为他的排序文件明显大于其他的输入文件

使用 ssh 命令登录到相应的子节点上，并且将该节点的用户转换成为root用户。使用lsof命令去查找那个进程执行了排序的PID操作


 $ ssh node? 
 $ su -
 # lsof  /home/gpadmin/data/data/gpseg2/base/16384/pgsql_tmp/........

PID 是文件名称的一部分。使用ps命令去计算PID来定义数据库和连接信息


# ps -eaf | grep PID_number

在主节点 master上，检查 pg_log 的日志文件来查看用户当前执行的命令操作 ( sbaskin ),
连接信息 (con699238 和 cmd32) . 具有三个值的日志文件中的行应该包含查询的行，但是偶尔也可能会略有不同
例如，这个ps输出肯恩显示的是cmd32,但是在日志文件中，它是34.如果这个查询一直在运行，那么对于用户和连接来说，这就是一个异常查询

在几乎所有情况下，处理计算倾斜最好的措施都是重写查询语句。创建临时表可以消除倾斜。可以随机分配临时表，以强制两阶段聚合操作。

分区操作

良好的分区策略通过仅读取满足查询所需的分区来减少数据扫描的数据量。
每一部分都是一个独立的物理文件或者瓦块的集合对于每一个段数据库。就像全表扫描整个列存储的表需要的时间要多余全表扫描行存储的的堆表，读取全部的分区表的时间同样多余未分区的表。
如下最佳实践的建议

1.只对大表做分区表，不要对小表做分区表操作

2.只对大表做分区表当分区，基于查询条件可以实现分区删除（分区修剪），通过对表的谓词选择
3.如果可能的话使用范围(RANGE)分区表，少使用链（LIST）分区表
4.仅当SQL查询包含使用不变操作符（例如=，<, <=, >=, <>）的简单直接的约束时，查询优化器才会执行分区裁剪。
5.选择扫描可以识别STABLE 和 IMMUTABLE 函数。但是不会识别 VOLATILE。
例如 WHERE 子句中
date > current_DATE
导致查询计划去选择部分表（分区裁剪）。但是WHERE子句是
time > TIMEOFDAY
将不会引起 分区裁剪

1.通过检查查询的EXPLAIN 执行计划雁阵是否执行了分区裁剪将会非常重要
2.不要使用默认分区表。默认分区经常被扫描，但是，非常重要的是因为在很多环境中中他们经常引起环境国漫而导致性能变差
3.不要使用分布键和分区键是同一个字段
4.避免使用多级分区。尽管支持多级子分区，但是仍旧不推荐。因为通常更小的分区包含更小或者没有数据。随着子分区的增长并不会提升性能。维护多个分区和子分区将会浪费掉更多性能优势。对于性能，可扩展性和可管理性，将分区扫描性能和整体分区数量进行平衡
5.不要使用太多的分区对于列式存储方式
6.要考虑工作负载以及为所有并发查询打开和扫描的平均分区均值

硬件上，文件数量的约束对于Greenplum来说，是来自于操作系统上的文件限制。这是非常重要，然而考虑到集群中，当前文件数量，每一个段所含有的文件数量，在主机上所含有的全部文件数量。
对于MPP shared nothing 环境，任何节点操作都独立于其他节点。每一个节点都会管理器自身的磁盘， CPU，内存。因此对于磁盘和CPU将不再是Greenplum数据库的限制。但是内存依旧是整个查询性能的瓶颈所在。
这个优化的文件数在每一个端上依旧是变化的，这个变化依靠着单一主机上段数据库的数量，集群的大小， SQL的访问，并发，负载和倾斜。如有6-8个段数据库在每一个节点上，但是大的节点应该有更少的段数据库。当使用分区表和列式存储时，平衡文件数量非常重要。但是去考虑每一个段数据库和全部文件数量在一个节点上，同样重要

例如
DCA V2 64GB 内存每个节点

节点数量： 16个节点
每个主机上有段数据库的量： 8
每个段上平均含有的文件数： 10000
主机含有的总文件数为 8 1000 = 80000，那么整个集群含有的文件数为 8 16 * 10000 = 1280 000.
可以预见，这个集群的文件数量随着分区和列存增长而快速增长。

总的建议为，限制在单一节点上，文件的总数量不超过10000；正如上面的例子所示，每个段的最佳文件数和每个节点的文件总述取决于节点（主要是内存）的硬件配置，集群的大小， SQL 的访问，并发，工作负载，和数据倾斜。

索引

在Greenplum数据库集群中索引显得并不是那么重要，许多分析系型查询语句(OLAP)都要全表扫描操作。因此，索引扫描更适合去定位单独的行或者很少数据量的数据。在Greenplum 数据库中，顺序扫描是一种非常有效的扫描方式，每一个段都包含着相等部分的数据，所有的段并行扫描。

-. 如果索引不能增加性能上的提升，那么删除它。确保你每个增加的索引都是有效的。
-. 对于高选择率的查询语句，索引将会提高查询的性能。对于列式存储的表创建一个索引在单一的字段上对于训练目的的高基数的列需要更高的选择率
不要对经常更新的列创建索引。对于经常更新的列创建索引，将会引起写入慢的问题，因为更新数据的同时，还要更新索引。可以先删除索引，更新完数据后，在重新创建索引。
-. 创建表达式索引时，这个表达式需要在查询语句中经常用到。
-. 谓词索引的创建可以用于从大表中选择少量行的部分索引。
-. 避免重复索引，具有相同前缀的索引是多余的。
-. 索引可以提高AO压缩表的性能。对于压缩数据，一个额索引访问方法，意味着访问的是重要的不压缩的页。数据库访问的最小磁盘单元是page
-.创建B-Tree索引。索引选择率高对于拥有众多不相同值的数据列来说。例如如果一个表有1000条记录和每一列有800个不同的值，那么其选择率就是0.8，这就认为是好的。

通常的原则，在加载数据之前删除索引。没有索引加载数据会明显快于有数据加载索引。在加载数据完后，重新构建索引。

Bitmap 索引适合于经常查询很少更新的字段。位图索引的性能最好的范围是索引字段的基数在100到10000之间。不要使用位图索引对有唯一性约束的字段，特别高或者特别低的基数数据
不要用位图索引给事务加载

如过分区表需要索引，这个索引列必须有不同的值相比于部分列。对分区表创建索引的好处是，索引字段必须与分区字段不同。在分区表中，B-Tree索引的性能会随着索引文件大小的增长，指数下降。
创建索引在分区表上，创建一个非常小的B-Tree索引其性能要好于没有分区表的表。

列式存储和字节对齐

在一个表上为了更好的优化列式存储，可以使用字节对齐。
遵循如下顺序原则建表

1.分区和分布字段
2.固定数字类型
3.可变数值类型
对数值类型排序，从最大到最小。所以， BIGINT TIMESTAMP 要排在 INT DATE 的前面，排在可变长度 TEXT， VARCHAR，或者 NUMERIC(X,Y).

例如有如下序列的表结构
INT, BIGINT, TIMESTAMP, BIGINT, TIMESTAMP, INT (distributed key) , Date (partition key), BIGINT, SMALLINT
重新排列后的表结构为
INT(distributed key), date(partition key), BIGINT, BIGINT, TIMESTAMP, BIGINT, TIMESTAMP, INT, SMALLINT