KYLIN&GP性能测试报告

KYLIN&GP性能测试报告

版本信息

系统/软件名	版本
centos	CentOS Linux release 7.3.1611
hadoop	hadoop-2.7.4
hive	apache-hive-2.1.1-bin
hbase	hbase-1.4.0
zookeeper	zookeeper-3.3.6
sqoop	sqoop-1.4.6.bin__hadoop-2.0.4-alpha
kylin	apache-kylin-2.3.1-bin
GP	greenplum-db-4.3.8.2-build-1-RHEL5-x86_64

 

集群环境

节点	内存	磁盘	服务
bigdata-3-22	15G	50G	Namenode，secondarynamenode, datanode，QuorumPeerMain，metastore，hmaster，HRegionServer，JobHistoryServer，ResourceManager，nodemanager，kylin，greenplum
bigdata-3-23	15G	50G	Datanode，HRegionServer，QuorumPeerMain，nodemanager，greenplum
bigdata-3-24	15G	50G	Datanode，HRegionServer，QuorumPeerMain，nodemanager，greenplum

 

GP环境

IP	秘钥	数据库	表名	存储方式	数据量
192.168.xxx.xxx	xxgxx/xxx	testDB	dw_test_data	行式/无压缩	45W
10.5.xxx.xxx	xxx/xxx	testdb	dw_test_data	行式/无压缩	100W

 

Kylin/GP  sql查询语句和平均执行时间

数据库	SQL	平均执行时间（S）	数据量（万条）	结果数（条）
KYLIN	select  rq, gngw, jjdq, wp, jjrlx, jjrxb, jjrnld, sum(jdl), sum(wtdl), sum(tqjdl), sum(tqwtdl), sum(syjdl), sum(sywtdl)   from dw_test_data group by rq, gngw, jjdq, wp, jjrlx, jjrxb, jjrnld order by rq ,jjrnld	0.25	139	39939
	同上	1.9	100	120528
GP	select  da.month, sum(dw.jdl) , sum(dw.wtdl) , sum(dw.tqjdl) , sum(dw.tqwtdl) , sum(dw.syjdl), sum(dw.sywtdl) from dw_test_data dw left join dw_test_dim_date da on dw.rq=da.day left join dw_test_dim_region re on dw.jjdq=re.city GROUP BY da.month	3.9	100	8
	select  dw.rq, dw.gngw, dw.jjdq, dw.wp, dw.jjrlx, dw.jjrxb, dw.jjrnld, dw.sjrlx, dw.sjrlb, dw.sjrnlb, dw.kdjc, sum(dw.jdl) , sum(dw.wtdl) , sum(dw.tqjdl) , sum(dw.tqwtdl) , sum(dw.syjdl), sum(dw.sywtdl) from dw_test_data dw left join dw_test_dim_date da on dw.rq=da.day left join dw_test_dim_region re on dw.jjdq=re.city GROUP BY dw.rq, dw.gngw, dw.jjdq, dw.wp, dw.jjrlx, dw.jjrxb, dw.jjrnld, dw.sjrlx, dw.sjrlb, dw.sjrnlb, dw.kdjc ORDER BY dw.rq ,dw.jjrnld	190	100	682992
	select  dw.rq, dw.gngw, dw.jjdq, dw.wp, dw.jjrlx, dw.jjrxb, dw.jjrnld, dw.sjrlx, dw.sjrlb, dw.sjrnlb, dw.kdjc, sum(dw.jdl) , sum(dw.wtdl) , sum(dw.tqjdl) , sum(dw.tqwtdl) , sum(dw.syjdl), sum(dw.sywtdl) from dw_test_data dw left join dw_test_dim_date da on dw.rq=da.day left join dw_test_dim_region re on dw.jjdq=re.city GROUP BY dw.rq, dw.gngw, dw.jjdq, dw.wp, dw.jjrlx, dw.jjrxb, dw.jjrnld, dw.sjrlx, dw.sjrlb, dw.sjrnlb, dw.kdjc ORDER BY dw.rq ,dw.jjrnld limit 200	2	100	200