从oracle往greenplum迁移，查询性能不满足要求的定位以及调优过程

一、前言

在一次对比oracle和greenplum查询性能过程中，由于greenplum查询性能不理想，因此进行定位分析，提升greenplum的查询性能

 

二、环境信息

初始情况下，搭建一个小的集群，进行性能测试

磁盘	SAS
交换机	千兆
集群大小	4segment
数据量	3亿
数据文件大小	68G
表类型	Heap 行表
字段类型	所有列为varchar
列宽	41列
索引	无
查询语句	select count(*) from xxx where gjdqdm = 'CHN' and crrqsj >= '20100101000000' and crrqsj <= '20180101000000' and crkadm = '055'

 

 

 

 

 

 

 

 

PS:由于要求greenplum中的表数据类型和源表类型一直，且索引一致。所以所有字段都为varchar类型且无索引，因此这方面没有优化空间。

 

三、优化过程

3.1 结果对比

SQL	ORACLE耗时	greenplum耗时
select count(*) from xxx where gjdqdm = 'CHN' and crrqsj >= '20100101000000' and crrqsj <= '20180101000000' and crkadm = '055'	24S	14.1S

 

 

14.1S是不能接受的速度，因此需要查找原因，以期找出性能瓶颈，提供优化方案

 

3.2 分析过程

3.2.1 查看执行计划

从①处可以看出，所有的耗时都在③的操作，seq scan上。

这里①处的意思是（摘自官网）：

The numbers that are quoted by EXPLAIN are (left to right):

    Estimated start-up cost (time expended before the output scan can start, e.g., time to do the sorting in a sort node)

    Estimated total cost (if all rows are retrieved, though they might not be; e.g., a query with a LIMIT clause will stop short of paying the total cost of the Limit plan node's input node)

    Estimated number of rows output by this plan node (again, only if executed to completion)

    Estimated average width (in bytes) of rows output by this plan node

 ③处的意思是：顺序扫描磁盘

 

从②处可以看出，所有的segment都参与了查询

 

从④处可以看出，所有的列设置为varchar都进行了类型转换，转成了text，且没有走索引（也无索引能用）

 

从⑤出可以看出，实际使用的内存远小于分配的内容，所以这里可以判断出问题不在内存

 

3.2.2 查看数据分布情况

 

这里可以看到数据分布是非常均匀的，所以不存在其中一台计算节点耗时特别长的情况

 

3.2.3 查看CPU情况

既然内存没有问题，那就可以尝试看CPU和磁盘的使用情况了

在其中计算节点使用top命令查看：

这里是其中一台计算节点的截图，这里说明仅仅对于这一条SQL而言，已经消耗了CPU100%的资源，但是整机还有相当富余的CPU资源可用

 

3.2.4 查看磁盘情况

使用sar命令查看计算节点情况

PS：这里仅展示一套机器（实际情况中每一台计算节点都是相同的情况）

这里发现iowait一列是基本都为0，但是idle也为0，此处验证了磁盘io没有问题，问题出在CPU上

 

3.3 问题解决

前面说到这个greenplum集群建立的时候只在每台结算节点分配了一个segment，所以每台机器上只有一个CPU是忙碌状态的，而其他的CPU处于空闲状态

充分的利用CPU资源，就可以显著提高查询的性能。

因此，对这套集群的segment进行扩容，将原来的4个segment扩容为54个，并且重新建表后将所有varchar类型换成text，将参与查询的日期列设置为分区键，分布键不变，仍为id列

 

3.4 最终结果

oracle	原集群	扩容后的集群
24S	14.1S	1.5S

 

 

 四、参考资料

https://www.postgresql.org/docs/9.2/static/using-explain.html