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 |