166
《greenplum 最佳实践》 模式设计 （三）
模式设计 最佳实践
Greenplum 数据库是一个分析型，shared-nothing 数据库。其不同于高度归一化的事务型SMP数据库。
Greenplum 数据库平台更适用于非规范化模式设计的MPP处理方式，星型或者雪花模式，大的集中式事实表连接到多个小维度的表。
数据类型 （Data Types）
使用类型一致
在做连接操作的两个表，其两个连接字段的数据类型一致。如果数据类型不一致，Greenplum 数据库必然后动态的转化一个字段的数据类型，这样就可以实现不同类型间的字段比较操作。
这种情况下，可能需要增加数据类型大小以便于连接到其他常见的对象。
选择合适的数据类型减少空间浪费
通过选择最有效的数据类型存储数据，来提高整个数据库集群的容量和查询的响应高效。
例如：使用TEXT 或者 VARCHAR 代替CHAR。在查询响应上，这三种数据类型间没有任何差距，但是在存储空间的使用上，TEXT和VARCHAR 就显的更经济
使用最小的数据类型来容纳数据，例如使用INT或者 SMALLINT 代替 BIGINT 将会节省更多的存储空间
存储模型
在创建表时，可以选择存储模型，这是非常重要的，我们可以使用append-optimized (AO) storage, 该模型支持行存和列存, 堆与AO的行列选择对大维度的事实表非常重要，但是对于小尺寸的表不填重要。有如下规则：
最佳实践如下：
对于直插入模型，在加载数据前删除每天的分区表
对于大型分区事实表，评估和使用不同分区的最佳存储选项。整个分区表的一个存储选项并不总是正确的。例如，一些分区表可以面向行存储，一些分区表可以面向列存储
当使用面向列的存储时， 每个列都是Greenplum数据库段中的单独文件。对于具有大量列的表，考虑对于经常访问的（热）数据使用列式存储，不经常访问的（冷）数据采用行存储。
每一个分区表层都应该设置存储选项
在集群中，压缩大表可以提高I/O性能和节省磁盘空间在集群中。
堆存储 和 追加优化存储模型
堆存储是默认的存储模型，是Postgresql 数据库中默认的存储方式。使用堆存储对于表和分区表将更适合经常更新和删除或者单行插入操作。使用堆存储对于表和分区表将会并行的更新和删除，插入操作
使用Append-optimized 存储模型适合存储不经常更新的表。避免执行单条的INSERT UPDATE 或者 delete操作对于append-optimized 表。
Append-Optimized 表适合加载一次，多次查询，很少更新， 批量插入操作。
行存或者列存
行存储是最传统的数据存储方式用来存储数据元组。在一行中所有的列属性字段连续的存储在磁盘上，因此一次单一的I/O开销，读取的是1行记录。
列存储是一列所有的值存储在一起放在磁盘上。 每一列都存放在一个分开的文件中。 如果这个表是分区表， 将会为每一个分区和每一列创建单独的存储文件。使用列存储时，
当一个查询只访问很少的列时，与行对照表相比，I/O的成本大大降低，任何查询不涉及的列，都不会从磁盘中检索。
行存储方式呗推荐用于事务类型的工作中和迭代事务中这些任务要求频繁的insert 和 update 操作。使用行存储方式是对于每次查询都涉及到很多字段。如果这些主要的列在SELECT中或者在WHERE子句中，那么使用行存储方式。使用行存储方式适合通用的或者复合的存储方式，它提供了最好的灵活性和性能最佳的组合。
列式存储方式是提供的对读取的优化，而不是提供的对写入的优化； 列属性的值对于行来说，写入了不同的磁盘位置。列存储方式的大数据量的表，当对少数列访问时，可以提供更好的性能访问方式。
由于列式存储是相同的属性值的列的值存在一起，相比于行存，混合的属性值存在一个文件，相比，占用的磁盘空间更少。所以相比于行存储其能够更好的减少I/O开销。在对表数据压缩上，由于列式的属性类型相同，将会有更好的压缩率，相比于行存储。
使用列式存储方式适合分析型数据仓库，当选择很少的列数据集或者做聚集计算时，对于表使用列式存储方式，需要定期修改某一个单列的值时，更合适。如果对于使用列存的表执行全表扫描读取时，将会花费更多的时间。
最重要的是，要铭记，每一个单独的列都是Greenplum中，段数据里面的单独文件。
压缩操作
Greenplum 对去Append-optimized 表或者Append-optimized分区表提供了很多的压缩操作. 使用压缩可以使得一次I/O开销下读取更多的数据。最好的做法是对列式压缩设置在分区表中。
当对分区表压缩时，需要明确知道的就是，分区表并不会继承父表的压缩类型和压缩等级，需要我们明确指定分区表的压缩类型和压缩等级。
RLE （Run-length encoding） 压缩提供了最好的压缩级别。高的压缩级别将会节省更多的磁盘存储空间，但是要求额外的时间和CPU周期来进行数据的解压和压缩操作。
排序的数据，与各种压缩选项想结合都会获得非常好的压缩率。
不应该将数据压缩方法用于存储在压文件的系统上的数据
在使用压缩前，应测试一些你的数据到底适合何种压缩等级，和排序方式。例如，你的数据可能适合Zlib方式下，4或5的压缩等级。RLE压缩格式更适合数据中具有重复值的数据。
分布键
分布键的选择决定了最终在Greenplum中，数据的分布。在一个MPP shared nothing 换将下，整个查询的响应时间是受最慢的那个段数据库返回时间决定的。如果数据倾斜，段数据库将会花费更多的时间区域完成任务。所以每一个段数据库必须有近似相近的数据量，和执行相同的查询进程。低性能和内存不足的情况下，正是因为一些段执行了更多的任务或者处理了更多的数据。
考虑如下的最佳实践
需要明确指出分布键的列或者使用岁建分布键，不要使用默认分布键，默认分布键通常是表结构中的第一列。
理想情况下，单一的分布键就可以使得表数据均匀的分布到所有的段数据库中
不要使用在WHERE子句中，经常出现的字段
4.不要使用下列类型作为分布键的列 dates 或者 timestamps
选择作为分布键的列最后包含唯一值或者具有较高的基数
如果对于一个表来说，使用单一的分布键并不能实现数据的负载均衡，那么推荐使用多属性列作为分布键，最多为两列。因为更多的列并不会产生更均匀的分布，而且会因为分布键的增多，导致整个过程需要更多的hash 三列时间
如果两个属性列作为分布键，依据不能保证获得负载均衡，那么推荐使用随机分布键。多列分布键在多说情况下，需要移动操作来连接表，所以相比随机分布，多列分布并不具备优势。
Greenplum 的随机分布并不是使用的 round-robin 算法， 所以它不能保证相等数的记录分配在同一个段数据库上，随机分布，通常保证数据分布不均在10%以内。
优化分布键对于大表数据来说非常重要，在执行JOIN操作时，想连接的行记录最好在同一个段数据上。如果数据并没有分布在相同的连接字段上，这些行记录必须从一个动态重分布到其他段上。在广播移动的情况下，
每一个段数据库都会重新三列其行记录到其他的数据中 根据hash key.
本地链接操作 Local (co-located) Joins
使用一个合理的分布键，将会会使得在执行链接操作时，会有很好的性能提升。当链接的行在同一个数据段上时，可以在段实例上，完成大埔人处理任务。这叫做本地连接或者同位置连接操作。本地连接可以保证数据移动
最少，减少网络开销，减少段之间的通信。
为了尽可能的实现本地连接操作，分布键最好都是使用相同的字段对于要做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%的倾斜的表，我们应该重新评估该表的分发策略。 （如何更改呢？？？）
计算倾斜
当不成比例的数据流入到一个段数据库或者一小部分段数据库时，处理这些倾斜数据，是Greenplum数据库性能和稳定性问题背后的罪魁祸首。它经常发生在JOIN、SORT、AGGREGATION和其他多种OLAP操作。
发生表级别的数据倾斜是因为分布键选取不当引起的，可以通过重新选择分布键来解决该问题。
如果单一段数据库挂掉，并不是一个主机上全部的段数据库，这将会引起计算倾斜的问题。为了定义到底是那个进程倾斜，我们需要手动调试。
如果有倾斜发生，但是发生倾斜的段并没有引起溢出文件，这将不会引起性能问题。如果我们确定有倾斜发生，那么找到查询对应的倾斜。使用如下命令
（修改名称使用gpssh）
首先找到发生倾斜的数据库的OID
SELECT OID， datname FROM pg_database;
输出如下：
运行gpssh命令去检查文件的尺寸在当前系统中，所有主机所包含的文件
gpssh -f ./host_all -e \
"du -b /home/gpadmin/data/data[1-2]/primary/gpseg&/base//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 "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
命令执行后的输出结果为
从上述结果可以看出，在主机 上面gpseg45是一起异常的。因为他的排序文件明显大于其他的输入文件
使用ssh命令登录到相应的子节点上，并且盖节点的用户转换成为root用户。使用lsof命令去查找那个进程执行了排序的PID操作
lsof /data2/primary/gpseg /base/19979/pgsql_tmp/pgsql_tmp_slice........
PID 是文件名称的一部分。 5. 使用ps命令去计算PID来定义数据库和连接信息
ps -eaf | grep PID_number
在主节点 master上， 检查pg_log 的日志文件来查看用户当前执行的命令操作 (sbaskin),
连接信息 (con699238 和 cmd32) . 具有三个值的日志文件中的行应该包含查询的行，但是偶尔也可能会略有不同
例如，这个ps输出肯恩显示的是cmd32,但是在日志文件中，它是34.如果这个查询一直在运行，那么对于用户和连接来说，这就是一个异常查询
在几乎所有情况下，处理偏差的不就措施都是重写查询。创建临时表可以消除倾斜。可以随机分配临时表，以强制两阶段聚合操作。
分区操作
良好的分区策略通过仅仅读取满足查询所需的分区来减少数据扫描的数据量。
每一部分都是一个独立的物理文件或者瓦块的集合对于每一个段。就像读取一个很快的列存储的表需要的时间要多余行存储的的堆表，读取全部的分区表在被划分的表需要的
时间多余同样未分区的表。
如下最佳实践的建议
1.只对大表做分区表，不对小表做分区表
2.只对大表做分区表当分区，基于插叙条件可以实现分区删除（分区修剪），通过对表的谓词选择
3.如果可能的话使用范围分区表，不要使用链分区表
4.查询优化器canker选择扫描表的分区表进到查询包含直接和简答的约束例如使用恒定的操作符， =，<, <=, >= 和 <>.
选择扫描识别STABLE 和 IMMUTABLE 功能。但是不会识别 VOLATILE 功能在一个查询里。 例如 WHERE 子句中
date > current_DATE
导致查询计划去选择部分表。但是WHERE子句是
time > TIMEOFDAY
就不会去选择分区表。它非常重要对于查询去选择部分表通过使用 EXPLAIN
不要使用默认分区表。默认分区经常被扫描，但是，非常重要，在很多环境中中他们经常引起环境国漫而导致性能变差
7.不要使用分布键和分区键是同一个字段
8.不要使用多级分区。尽管支持多级子分区， 但是以旧不值得推荐因为通常自分区包含更小或者没有数据。
随着子分区的增长并不会提升性能。维护多个分区和子分区将会超过任何性能优势。对于性能，可扩展性和可管理性，将分区扫描性能和整体分区数量进行平衡
不要使用太多的分区对于列式存储方式
要考虑工作负载以及为所有并发查询打开和扫描的平均分区数
分区数和列式存储文件
硬件上文件数量的约束对于Greenplum来说，是来自于操作系统上的文件限制。这非常重要，然而考虑到集群中，当前文件数量，每一个段所含有的文件数量，在主机上所含有的全部文件数量。
对于MPP shared nothing 环境， 任何节点操作都独立于其他节点。每一个节点都会管理器自身的磁盘， CPU， 内存。 因此对于磁盘和CPU将不再是Greenplum数据库的限制。但是内存依旧是整个查询
性能的瓶颈所在。
这个优化的文件数在每一个端上依旧是变化的，这个变化依靠着单一主机上段数据库的数量， 集群的大小， SQL的访问， 并发， 负载 和 倾斜。有6-8个段数据库在每一个主机上，但是大的主机应该有更少的
段数据库在每个主机上。当使用分区表和列式存储是，平衡文件数量非常重要。但是去考虑每一个段数据库和全部数量的一个节点上，同样重要
例如
DCA V2 64GB 内存 每个节点
16个节点
每个主机上有段数据库的量： 8
3.每个段上平均含有的文件数： 10000
主机含有的总文件数为 8 * 1000 = 80000， 那么整个集群含有的文件数为 8 * 16 * 1000 = 1280 000.
可以预见，这个文件数量随着分区和列存增长快速。
总的建议为，限制主机上，文件的总述不超过10000；正如上面的例子所示，
每个段的最佳文件数和每个节点的文件总述取决于节点（主要是内存）的硬件配置，集群的大小 SQL 的访问 并发 工作负载 和 数据倾斜。
索引
在Greenplum中 索引显得并不是那么重要，许多分析系型查询语句都要全表扫描操作，因此，索引更适合去定位单独的行或者很少数据量的数据。
在Greenplum 数据库中，顺序扫描是一种非常有效的扫描方式，每一个段都包含着相等部分的数据，所有的段并行扫描。
如果索引不能增加性能上的提升，那么删除它。确保你每个增加的索引都是有效的。
对于高选择率的查询语句，索引将会提高查询的性能。对于列式存储的表创建一个索引在单一的字段上对于训练目的的高基数的列需要更高的选择率
不要对经常更新的列创建索引。对于经常更新的列创建索引，将会引起写入慢的问题，因为更新数据的同时，还要更新索引。
可以先删除索引，更新完数据后，在重新创建索引。
创建表达式索引时，这个表达式需要在查询语句中经常用到。
谓词索引的创建可以用于从大表中选择少量行的部分索引。
避免重复索引，具有相同前缀的索引是多余的。
索引可以提高AO压缩表的性能。对于压缩数据，一个额索引访问方法，意味着访问的是重要的不压缩的页。数据库访问的最小磁盘单元是page
创建B-Tree索引，。索引选择率高对于拥有众多值不相同的数据列来说。例如如果一个表有1000条记录和每一列有800个不同的值，那么其选择率就是0.8，这就认为是好的
通常的原则，在加载数据之前删除索引。没有索引加载数据会明显快于有数据加载索引。在加载数据完后，重新构建索引。
Bitmap 索引适合于经常查询很少更新的字段。位图索引的性能最好的范围是索引字段的基数在100到10000之间。不要使用位图索引对有唯一性约束的字段，特别高或者特别低的基数数据
不要用位图索引给事务加载
如过分区表需要索引，这个索引列必须有不同的值相比于部分列。对分区表创建索引的好处是，索引字段必须与分区字段不同。在分区表中，B-Tree索引的性能会随着索引文件大小的增长，指数下降。
创建索引在分区表上，创建一个非常小的B-Tree索引其性能要好于没有分区表的表。
列式存储和字节对齐
为了更好的优化列式存储的在一个表上，可以使用字节对齐。
遵循如下顺序原则
分区和分布字段
2.固定数字类型
2.可变数值类型
对数值类型排序，从最大到最小。所以， 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
最后更新：2017-10-26 00:33:29