gp基础篇--优化

痛定思痛。 2022-05-14 01:19 267阅读 0赞

author：skate  
time：2012/11/26

**gp基础篇--优化**

gp系统的优化需要从全局考虑  
1.硬件层  
2.资源的分配，并发资源竞争  
3.统计信息的准确性  
4.数据分布  
5.数据库的设计  
6.sql的优化

1.硬件层  
gp是一个集群系统，任何节点都会影响gp性能，如：磁盘是否健康、主机是否健康、网络是否健康等；os是否为gp环境定制调优；还有为了保证gp系统性能不衰减，要保证磁盘容量最大使用70%以前；在每次dml操作、load数据后都要vacuum。

2.资源的分配，并发资源竞争  
 2.1 通过资源队列限制gp系统里active queryes的数量，分配给指定query的资源多少，使gp系统最佳状态运行  
 2.2 清楚gp系统的运行负载，把后台管理放(如，data load，vacuum，backup等 )在系统负载低时运行。  
 2.3 调整应用，避免同一时间不同的应用更新相同的数据，从为避免lock冲突  
   
3.数据库的统计信息收集  
  3.1 在gp系统data load、restore from backup、add index、dml等之后都要vacuum analyze，一方面是如果批处理操作失败，会浪费很多空间，这个操作可以回收这些空间，另一方面，GP也是基于cost的优化器，只有收集相对准确的优化统计，才能保证执行计划的最优，这个操作会进行优化统计的收集。优化统计信息的收集粒度由参数default\_statistics\_target，该参数设置越高，优化器得到最优执行计划的可能越大，但是收集操作的代价也就越大，耗时越长。

另外，每次装载完数据还应该检查是否存在数据存放不均匀的情况，比如某些segment存放的记录数远远超过其它segment，会导致多存放记录的segment成为整个系统运算过程中的瓶颈。  
     我们可以通过一些简单的方法去检查是否存在数据分布的不平衡。比如系统级，我们可以直接用df -h 或du -h检查磁盘或者目录数据是否均匀。在segment一级，可以通过  
select gp\_segment\_id,count(\*) from fact\_table group by gp\_segment\_id的方式检查每张表的数据是否均匀存放。  
   
4.数据的分布式  
数据分布均匀是保证GP高效并行处理能力的基础。因此定义表时，如果选用HASH分布策略，保证数据分布均匀是获取高性能的关键所在。选择的依据遵从三大原则，第一个就是首先保证前面提到的所有节点数据存放是均匀的。第二，如果经常进行大表连接，那么尽量把连接键定义成数据分布键（如果多个列作为数据分布键，他们应该都出现在连接中，否则还是会造成无效广播），这样尽量减少无效的interconnect。第三，尽量保证where条件产生的结果集的存储也尽量是均匀的。

5.数据库模型的设计  
 5.1 gp索引  
 gp要保守使用索引，在使用索引时要注意以下几点：  
  5.1.1 在data load之前不要创建索引  
  5.1.2 避免在频繁更新的列上创建索引  
  5.1.3 避免表上有重复的索引  
  5.1.4 从超大结果集合中返回非常小的结果集（不超过5%），建议使用BTREE索引；表记录的存储顺序最好与索引一致， 可以进一步减少IO（好的index cluster），where条件中的列用or的方式进行join，可以考虑使用索引；如果记录很多，键值大量重复时，比较适合使用bitmap索引，gp可以在一个简单的查询中使用多个bitmap索引  
  5.1.5 在创建索引之后，要做analyze   
   
 5.2 分区表  
 对于大表，为了数据更均匀的分配，需要使用分区表尽可能把一个任务的所有数据都平均分配在所有segment上，以达到最大的性能  
   
 5.3 行存和列存  
GREENPLUM同时支持行存和列存方式，那么到底该怎么选择？ 如果记录需要update/delete，那么只能选择非压缩的行存方式。对于查询，如果选择的列的数量经常超过30个以上的列，那么也应该选择行存方式。如果选择列的数量非常有限，并且希望通过较高的压缩比换取海量数据查询时的较好的IO性能，那么就应该选择列存模式。但是需要注意的是，如果是列存分区表，每个分区的每个列都会有一个对应的物理文件。比如有100个分区，100个列，那么就会产生10000个文件，默认情况下，这些文件都会放在一个目录中。这可能会导致两方面的问题，一个是访问数据时有可能超越linux上允许同时打开文件数量的上限。另一个是在同一个目录内，存放过多的文件，会导致DDL命令的效率很差。解决这两个问题，一个是尽量在SQL中使用事实表的分区键作为条件，或者尽量指定需要访问的事实表分区，以减少文件的访问。另外，应该创建不同的表空间（相当于不同的系统目录），把分区放入不同的表空间。

5.4 表的设计  
尽量把数据分布键放在最前面，如果是分区表，那么接下来是分区键，并且在此基础上建议按照数据类型宽度从大到小的顺序排列比如先8 byte的列，再4字节，再2字节等；还有就是，连接表的关联列要使用相同的列类型  
   
6.sql的优化  
可以通过查看sql的执行计划来发现sql慢在哪？比如是书写不合理的问题，还是优化统计不准确的问题。查看sql执行计划有  
explain和explain analyze，他们是有区别的。explain是基于统计，给出执行计划，他不会真正执行SQL，因此没有执行统计。  
explain analyze会真正执行SQL因此会提供执行过程中真实资源消耗统计，但是对在线系统可能影响会较大

尽量避免对大表进行DISTINCT操作，因为GP中DISTINCT要进行排序操作。可以考虑用group by对distinct操作进行改写。  
同样对于UNION操作应该用UNION ALL加GROUP BY的方式进行改写

\---end---