置顶 ClickHouse performance benchmark
发布于 10 个月前 作者 zhtsh 2309 次浏览 来自 分享

测试table: fb_campaign: 维度表,362M(未压缩),170万记录,17列 fb_campaigninsight_action_agegender_pre:事实表,51G(未压缩),3.4亿记录,15列

测试机器:gcp上以前用于vertica测试的三台虚拟机,配置为:20vcore,160g memory,500g ssd

测试查询:

  1. count(): select count() from fb_campaigninsight_action_agegender_pre

  2. 一个维度聚合:select campaign_id,sum(click_1d),sum(click_7d),sum(click_28d),sum(view_1d),sum(view_7d),sum(view_28d),sum(value) as value from fb_campaigninsight_action_agegender_pre group by campaign_id order by value desc limit 20

  3. 二个维度聚合:select campaign_id,age,sum(click_1d),sum(click_7d),sum(click_28d),sum(view_1d),sum(view_7d),sum(view_28d),sum(value) as value from fb_campaigninsight_action_agegender_pre group by campaign_id,age order by value desc limit 20

  4. 三个维度聚合:select campaign_id,age,gender,sum(click_1d),sum(click_7d),sum(click_28d),sum(view_1d),sum(view_7d),sum(view_28d),sum(value) as value from fb_campaigninsight_action_agegender_pre group by campaign_id,age,gender order by value desc limit 20

  5. 两表join+聚合:select campaign_id,campaign_name,buying_type,sum(click_1d),sum(click_7d),sum(click_28d),sum(view_1d),sum(view_7d),sum(view_28d), sum(value) as value from fb_campaigninsight_action_agegender_pre any inner join fb_campaign using (campaign_id) group by campaign_id,campaign_name,buying_type order by value desc limit 20

  6. 两表join+聚合优化写法:select campaign_id,campaign_name,buying_type,click_1d,click_7d,click_28d,view_1d,view_7d,view_28d, value from (select campaign_id,sum(click_1d) as click_1d,sum(click_7d) as click_7d,sum(click_28d) as click_28d, sum(view_1d) as view_1d,sum(view_7d) as view_7d,sum(view_28d) as view_28d,sum(value) as value from fb_campaigninsight_action_agegender_pre group by campaign_id order by value desc limit 20) any inner join fb_campaign using (campaign_id)

测试类型:first query (cold cache) 与 second query (warm cache) 测试结果:

single node vs cluster image.png

clickhouse vs vertica image.png

2 回复

有导入表的测试结果吗? 我在测试中发现导入慢270G数据得50分钟。

回到顶部