图片来自 Pexels
本文记录一下测试结果,有做超大数据量分析技术选型需求的朋友可以参考下。
服务器信息
如下:
- CPU:Intel Xeon Gold 6240 @ 8x 2.594GHz
- 内存:32G
- 系统:CentOS 7.6
- Linux 内核版本:3.10.0
- 磁盘类型:机械硬盘
- 文件系统:ext4
Clickhouse 信息
如下:
- 部署方式:单机部署
- 版本:20.8.11.17
测试情况
测试数据和测试方法来自 Clickshouse 官方的 Star Schema Benchmark:
按照官方指导造出了测试数据之后,先看一下数据量和空间占用情况。
①数据量和空间占用
如下图:
可以看到 Clickhouse 的压缩率很高,压缩率都在 50 以上,基本可以达到 70 左右。
数据体积的减小可以非常有效的减少磁盘空间占用、提高 I/O 性能,这对整体查询性能的提升非常有效。
supplier、customer、part、lineorder 为一个简单的「供应商-客户-订单-地区」的星型模型。
lineorder_flat 为根据这个星型模型数据关系合并的大宽表,所有分析都直接在这张大宽表中执行,减少不必要的表关联,符合我们实际工作中的分析建表逻辑。
以下性能测试的所有分析 SQL 都在这张大宽表中运行,未进行表关联查询。
查询性能测试详情
①Query 1.1
1. SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
2. FROM lineorder_flat
3. WHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25)
4.
5. ┌────────revenue─┐
6. │ 44652567249651 │
7. └────────────────┘
8.
9. 1 rows in set. Elapsed: 0.242 sec. Processed 91.01 million rows, 728.06 MB (375.91 million rows/s., 3.01 GB/s.)
扫描行数:91,010,000,大约 9100 万
耗时(秒):0.242。
查询列数:2。
结果行数:1。
②Query 1.2
1. SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
2. FROM lineorder_flat
3. WHERE (toYYYYMM(LO_ORDERDATE) = 199401) AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6)) AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35))
4.
5. ┌───────revenue─┐
6. │ 9624332170119 │
7. └───────────────┘
8.
9. 1 rows in set. Elapsed: 0.040 sec. Processed 7.75 million rows, 61.96 MB (191.44 million rows/s., 1.53 GB/s.)
扫描行数:7,750,000,775 万。
耗时(秒):0.040。
查询列数:2。
返回行数:1。
③Query 2.1
1. SELECT
2. sum(LO_REVENUE),
3. toYear(LO_ORDERDATE) AS year,
4. P_BRAND
5. FROM lineorder_flat
6. WHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA')
7. GROUP BY
8. year,
9. P_BRAND
10. ORDER BY
11. year ASC,
12. P_BRAND ASC
13.
14. ┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
15. │ 64420005618 │ 1992 │ MFGR#121 │
16. │ 63389346096 │ 1992 │ MFGR#1210 │
17. │ ........... │ .... │ ..........│
18. │ 39679892915 │ 1998 │ MFGR#128 │
19. │ 35300513083 │ 1998 │ MFGR#129 │
20. └─────────────────┴──────┴───────────┘
21.
22. 280 rows in set. Elapsed: 8.558 sec. Processed 600.04 million rows, 6.20 GB (70.11 million rows/s., 725.04 MB/s.)
扫描行数:600,040,000,大约 6 亿。
耗时(秒):8.558。
查询列数:3。
结果行数:280。
④Query 2.2
1. SELECT
2. sum(LO_REVENUE),
3. toYear(LO_ORDERDATE) AS year,
4. P_BRAND
5. FROM lineorder_flat
6. WHERE ((P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA')
7. GROUP BY
8. year,
9. P_BRAND
10. ORDER BY
11. year ASC,
12. P_BRAND ASC
13.
14. ┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
15. │ 66450349438 │ 1992 │ MFGR#2221 │
16. │ 65423264312 │ 1992 │ MFGR#2222 │
17. │ ........... │ .... │ ......... │
18. │ 39907545239 │ 1998 │ MFGR#2227 │
19. │ 40654201840 │ 1998 │ MFGR#2228 │
20. └─────────────────┴──────┴───────────┘
21.
22. 56 rows in set. Elapsed: 1.242 sec. Processed 600.04 million rows, 5.60 GB (482.97 million rows/s., 4.51 GB/s.)
扫描行数:600,040,000,大约 6 亿。
耗时(秒):1.242。
查询列数:3。
结果行数:56。
⑤Query 3.1
1. SELECT
2. C_NATION,
3. S_NATION,
4. toYear(LO_ORDERDATE) AS year,
5. sum(LO_REVENUE) AS revenue
6. FROM lineorder_flat
7. WHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997)
8. GROUP BY
9. C_NATION,
10. S_NATION,
11. year
12. ORDER BY
13. year ASC,
14. revenue DESC
15.
16. ┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐
17. │ INDIA │ INDIA │ 1992 │ 537778456208 │
18. │ INDONESIA │ INDIA │ 1992 │ 536684093041 │
19. │ ..... │ ....... │ .... │ ............ │
20. │ CHINA │ CHINA │ 1997 │ 525562838002 │
21. │ JAPAN │ VIETNAM │ 1997 │ 525495763677 │
22. └───────────┴───────────┴──────┴──────────────┘
23.
24. 150 rows in set. Elapsed: 3.533 sec. Processed 546.67 million rows, 5.48 GB (154.72 million rows/s., 1.55 GB/s.)
扫描行数:546,670,000,大约 5 亿 4 千多万。
耗时(秒):3.533。
查询列数:4。
结果行数:150。
⑥Query 3.2
1. SELECT
2. C_CITY,
3. S_CITY,
4. toYear(LO_ORDERDATE) AS year,
5. sum(LO_REVENUE) AS revenue
6. FROM lineorder_flat
7. WHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)
8. GROUP BY
9. C_CITY,
10. S_CITY,
11. year
12. ORDER BY
13. year ASC,
14. revenue DESC
15.
16. ┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐
17. │ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 │
18. │ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 │
19. │ .......... │ .......... │ .... │ .......... │
20. │ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 │
21. │ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │
22. └────────────┴────────────┴──────┴────────────┘
23.
24. 600 rows in set. Elapsed: 1.000 sec. Processed 546.67 million rows, 5.56 GB (546.59 million rows/s., 5.56 GB/s.)
扫描行数:546,670,000,大约 5 亿 4 千多万。
耗时(秒):1.00。
查询列数:4。
结果行数:600。
⑦Query 4.1
1. SELECT
2. toYear(LO_ORDERDATE) AS year,
3. C_NATION,
4. sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
5. FROM lineorder_flat
6. WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
7. GROUP BY
8. year,
9. C_NATION
10. ORDER BY
11. year ASC,
12. C_NATION ASC
13.
14. ┌─year─┬─C_NATION──────┬────────profit─┐
15. │ 1992 │ ARGENTINA │ 1041983042066 │
16. │ 1992 │ BRAZIL │ 1031193572794 │
17. │ .... │ ...... │ ............ │
18. │ 1998 │ PERU │ 603980044827 │
19. │ 1998 │ UNITED STATES │ 605069471323 │
20. └──────┴───────────────┴───────────────┘
21.
22. 35 rows in set. Elapsed: 5.066 sec. Processed 600.04 million rows, 8.41 GB (118.43 million rows/s., 1.66 GB/s.)
扫描行数:600,040,000,大约 6 亿。
耗时(秒):5.066。
查询列数:4。
结果行数:35。
⑧Query 4.2
1. SELECT
2. toYear(LO_ORDERDATE) AS year,
3. S_NATION,
4. P_CATEGORY,
5. sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
6. FROM lineorder_flat
7. WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((year = 1997) OR (year = 1998)) AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
8. GROUP BY
9. year,
10. S_NATION,
11. P_CATEGORY
12. ORDER BY
13. year ASC,
14. S_NATION ASC,
15. P_CATEGORY ASC
16.
17. ┌─year─┬─S_NATION──────┬─P_CATEGORY─┬───────profit─┐
18. │ 1997 │ ARGENTINA │ MFGR#11 │ 102369950215 │
19. │ 1997 │ ARGENTINA │ MFGR#12 │ 103052774082 │
20. │ .... │ ......... │ ....... │ ............ │
21. │ 1998 │ UNITED STATES │ MFGR#24 │ 60779388345 │
22. │ 1998 │ UNITED STATES │ MFGR#25 │ 60042710566 │
23. └──────┴───────────────┴────────────┴──────────────┘
24.
25. 100 rows in set. Elapsed: 0.826 sec. Processed 144.42 million rows, 2.17 GB (174.78 million rows/s., 2.63 GB/s.)
扫描行数:144,420,000,大约 1 亿 4 千多万。
耗时(秒):0.826。
查询列数:4。
结果行数:100。
性能测试结果汇总
如下图:
在当前软硬件环境下,扫描 6 亿多行数据,常见的分析语句首次运行最慢在 8 秒左右能返回结果。
相同的分析逻辑更换条件再次查询的时候效率有明显的提升,可以缩短到 1 秒左右。
如果只是简单的列查询没有加减乘除、聚合等逻辑,扫描全表 6 亿多行数据首次查询基本可以在 2 秒内执行完成。
作者:LifeLogs