GreenPlum vs MonetDB case-白红宇

强烈建议你试试无所不能的chatGPT，快点击我

GreenPlum vs MonetDB case

阅读量：6151 次

发布时间：2019-06-21

本文共 4040 字，大约阅读时间需要 13 分钟。

GreenPlum测试环境: 16台主机, 64个segment.

主要对比的是前几天MonetDB vs PostgreSQL vs PostgreSQL+cstore_fdw的场景.

MonetDB的测试环境和测试数据, 测试结果参考

GreenPlum采用列存储, 随机分布, 开启压缩, 压缩级别9.

以下是GreenPlum的测试结果 :

copy方式导入时间：Time: 366566.754 ms , -- 比MonetDB略慢.

(insert into t2 select * from t1)方式导入时间：20922 ms , -- 比monetdb快10秒.

MonetDB :

sql>delete from tt;

50000000 affected row (5.290ms)

sql>insert into tt select * from t1;

50000000 affected row (30.9s)

采用copy方式导入表存储消耗(列存储, 压缩级别9)：10265MB --, 比MonetDB小 1.xG

测试SQL:

1. select count(distinct c1) from t1;

Time: 18442.063 ms , 比MonetDB慢10倍

2. select count(distinct c1) from t2;

Time: 18165.555 ms

, 比MonetDB慢10倍

3. select count(*) from (select c1,c11,c21,c31,c41,c51,c60 from t1 group by c1,c11,c21,c31,c41,c51,c60) as t;

Time: 4302.028 ms

, 比MonetDB慢2秒

4. select count(*) from (select t1.c1 from t1 join t2 on t1.c2=t2.c3) as t;

Time: 3559.810 ms , 比MonetDB快24.5秒

5. select count(*) from (select t1.c1 from t1 join t2 on t1.c2=t2.c3 join t3 on t1.c3=t3.c5) as t;

Time: 46933.672 ms , 比MonetDB快27秒

6. select count(*) from (select t1.c1 from t1 join t2 on t1.c2=t2.c3 join t3 on t1.c3=t3.c5 join t4 on t3.c5=t4.c6) as t;

Time: 78507.539 ms

, 比MonetDB快25秒

7. select count(*) from (select t1.c1 from t1 join t2 on t1.c2=t2.c3 join t3 on t2.c3=t3.c4 join t4 on t3.c4=t4.c5 join t5 on t4.c5=t5.c6) as t;

Time: 125883.171 ms

, 速度一致

8. select count(*) from ( select t1.c1 from t1 join t2 on t1.c2=t2.c3 join t3 on t2.c3=t3.c4 join t4 on t3.c4=t4.c5 join t5 on t4.c5=t5.c6 where t1.c60 < 100000 ) as t;

Time: 5640.577 ms , 比MonetDB快7秒

9. select count(*) from (

select t1.c1 from t1

join t2 on t1.c2=t2.c3

join t3 on t2.c3=t3.c4

join t4 on t3.c4=t4.c5

join t5 on t4.c5=t5.c6

join t6 on t5.c5=t6.c6

join t7 on t6.c5=t7.c6

join t8 on t7.c5=t8.c6

join t9 on t8.c5=t9.c6

join t10 on t9.c5=t10.c6

join t11 on t10.c5=t11.c6

join t12 on t11.c5=t12.c6

join t13 on t12.c5=t13.c6

join t14 on t13.c5=t14.c6

join t15 on t14.c5=t15.c6

join t16 on t15.c5=t16.c6

join t17 on t16.c5=t17.c6

) as t;

Time: 658719.790 ms

, 比MonetDB慢348秒

10. select count(*) from (

select t1.c1 from t1

join t2 on t1.c2=t2.c3

join t3 on t2.c3=t3.c4

join t4 on t3.c4=t4.c5

join t5 on t4.c5=t5.c6

join t6 on t5.c5=t6.c6

join t7 on t6.c5=t7.c6

join t8 on t7.c5=t8.c6

join t9 on t8.c5=t9.c6

join t10 on t9.c5=t10.c6

join t11 on t10.c5=t11.c6

join t12 on t11.c5=t12.c6

join t13 on t12.c5=t13.c6

join t14 on t13.c5=t14.c6

join t15 on t14.c5=t15.c6

join t16 on t15.c5=t16.c6

join t17 on t16.c5=t17.c6

where t1.c60 < 100000

) as t;

Time: 25521.288 ms

, 比MonetDB快11秒

11. select count(*) from (

select t1.c1 from t1

join t2 on t1.c1=t2.c3

join t3 on t2.c3=t3.c4

join t4 on t3.c4=t4.c5

join t5 on t4.c5=t5.c6

join t6 on t5.c5=t6.c6

join t7 on t6.c5=t7.c6

join t8 on t7.c5=t8.c6

join t9 on t8.c5=t9.c6

join t10 on t9.c5=t10.c6

join t11 on t10.c5=t11.c6

join t12 on t11.c5=t12.c6

join t13 on t12.c5=t13.c6

join t14 on t13.c5=t14.c6

join t15 on t14.c5=t15.c6

join t16 on t15.c5=t16.c6

join t17 on t16.c5=t17.c6

) as t;

Time: 692865.584 ms

, 比MonetDB慢430秒

12. select count(*) from (

select t1.c1 from t1

join t2 on t1.c2=t2.c3

join t3 on t2.c3=t3.c4

join t4 on t3.c4=t4.c5

join t5 on t4.c5=t5.c6

join t6 on t5.c6=t6.c7

join t7 on t6.c7=t7.c8

join t8 on t7.c8=t8.c9

join t9 on t8.c9=t9.c10

join t10 on t9.c10=t10.c11

join t11 on t10.c11=t11.c12

join t12 on t11.c12=t12.c13

join t13 on t12.c13=t13.c14

join t14 on t13.c14=t14.c15

join t15 on t14.c15=t15.c16

join t16 on t15.c16=t16.c17

join t17 on t16.c17=t17.c18

where t1.c1<10

) as t;

1小时未跑出。

GP做多表关联, 关联结果集比较大时, 性能不如MonetDB.

少量的表关联, 因为机器数量占优, 比MonetDB性能要好.

体积较小的单表查询效率不如MonetDB.

体积较大的单表查询效率可能超过MonetDB,

同样依赖于机器的数量.

什么情况下MonetDB性能会下降明显, 当查询的数据扫描量超过内存时, 例如多列的or条件查询, 当多列的存储空间超过内存时.

或者单列的存储空间超过内存大小时.

MonetDB性能会有所下降. 例如这个表364亿条记录, 单列达到了135G, 超过内存(96G).

sql>select count(*) from bt5;

+-------------+

| L1 |

+=============+

| 36400000000 |

+-------------+

1 tuple (41.206ms)

sql>select count(*) from bt5 where id=1;

+------+

| L1 |

+======+

| 728 |

+------+

1 tuple (5m 36s)

对于单列小于内存的场景, MonetDB优势很明显. 以96G的内存为例, 存INT列的话, 可以存256亿条记录.

[参考]

1.

转载地址：http://dlzfa.baihongyu.com/

你可能感兴趣的文章

js document.activeElement 获得焦点的元素

C++ 迭代器运算

【支持iOS11】UITableView左滑删除自定义 - 实现多选项并使用自定义图片

JavaWeb学习笔记（十四）--JSP语法

【算法笔记】多线程斐波那契数列

java8函数式编程实例

jqgrid滚动条宽度/列显示不全问题

在mac OS10.10下安装 cocoapods遇到的一些问题

Tyvj 1728 普通平衡树

javascript性能优化

多路归并排序之败者树

java连接MySql数据库

转：Vue keep-alive实践总结

深入python的set和dict

Android JSON数据解析

DEV实现日期时间效果

java注解【转】

centos 下安装g++

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！

当前时间: 2025-01-06 13:03:17 当前IP: 18.188.62.10 联系邮箱:javaeecc@qq.com Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2

强烈建议你试试无所不能的CHAT-GPT，快点击我

强烈建议你试试无所不能的CHAT-GPT，快点击我