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 |