最近由于項(xiàng)目需求使用到了 clickhouse 做分析數(shù)據(jù)庫，于是用測試環(huán)境做了一個(gè)單表 6 億數(shù)據(jù)量的性能測試，記錄一下測試結(jié)果，有做超大數(shù)據(jù)量分析技術(shù)選型需求的朋友可以參考下。

服務(wù)器信息

CPU：Intel Xeon Gold 6240 @ 8x 2.594GHz
內(nèi)存：32G
系統(tǒng)：CentOS 7.6
Linux內(nèi)核版本：3.10.0
磁盤類型：機(jī)械硬盤
文件系統(tǒng)：ext4

Clickhouse信息

部署方式：單機(jī)部署
版本：20.8.11.17

測試情況

測試數(shù)據(jù)和測試方法來自 clickshouse 官方的 Star Schema Benchmark，URL：https://clickhouse.com/docs/en/getting-started/example-datasets/star-schema/

按照官方指導(dǎo)造出了測試數(shù)據(jù)之后，先看一下數(shù)據(jù)量和空間占用情況。

數(shù)據(jù)量和空間占用

可以看到 clickhouse 的壓縮率很高，壓縮率都在 50 以上，基本可以達(dá)到 70 左右。數(shù)據(jù)體積的減小可以非常有效的減少磁盤空間占用、提高 I/O 性能，這對整體查詢性能的提升非常有效。

supplier、customer、part、lineorder 為一個(gè)簡單的「供應(yīng)商-客戶-訂單-地區(qū)」的星型模型，lineorder_flat 為根據(jù)這個(gè)星型模型數(shù)據(jù)關(guān)系合并的大寬表，所有分析都直接在這張大寬表中執(zhí)行，減少不必要的表關(guān)聯(lián)，符合我們實(shí)際工作中的分析建表邏輯。

以下性能測試的所有分析 SQL 都在這張大寬表中運(yùn)行，未進(jìn)行表關(guān)聯(lián)查詢。

查詢性能測試詳情

Query 1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25)
┌────────revenue─┐│ 44652567249651 │└────────────────┘
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.)

描行數(shù)：91,010,000?大約9100萬

耗時(shí)(秒)：0.242

查詢列數(shù)：2

結(jié)果行數(shù)：1

Query 1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE (toYYYYMM(LO_ORDERDATE) = 199401) AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6)) AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35))
┌───────revenue─┐│ 9624332170119 │└───────────────┘
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.)

描行數(shù)：7,750,000?775萬

耗時(shí)(秒)：0.040

查詢列數(shù)：2

返回行數(shù)：1

Query 2.1

SELECT     sum(LO_REVENUE),    toYear(LO_ORDERDATE) AS year,    P_BRANDFROM lineorder_flatWHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA')GROUP BY     year,    P_BRANDORDER BY     year ASC,    P_BRAND ASC
┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐│     64420005618 │ 1992 │ MFGR#121  ││     63389346096 │ 1992 │ MFGR#1210 ││     ........... │ .... │ ..........││     39679892915 │ 1998 │ MFGR#128  ││     35300513083 │ 1998 │ MFGR#129  │└─────────────────┴──────┴───────────┘
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.)

掃描行數(shù)：600,040,000?大約6億

耗時(shí)(秒)：8.558

查詢列數(shù)：3

結(jié)果行數(shù)：280

Query 2.2

SELECT     sum(LO_REVENUE),    toYear(LO_ORDERDATE) AS year,    P_BRANDFROM lineorder_flatWHERE ((P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA')GROUP BY     year,    P_BRANDORDER BY     year ASC,    P_BRAND ASC
┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐│     66450349438 │ 1992 │ MFGR#2221 ││     65423264312 │ 1992 │ MFGR#2222 ││     ........... │ .... │ ......... ││     39907545239 │ 1998 │ MFGR#2227 ││     40654201840 │ 1998 │ MFGR#2228 │└─────────────────┴──────┴───────────┘
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.)

掃描行數(shù)：600,040,000?大約6億

耗時(shí)(秒)：1.242

查詢列數(shù)：3

結(jié)果行數(shù)：56

Query 3.1

SELECT     C_NATION,    S_NATION,    toYear(LO_ORDERDATE) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997)GROUP BY     C_NATION,    S_NATION,    yearORDER BY     year ASC,    revenue DESC
┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐│ INDIA     │ INDIA     │ 1992 │ 537778456208 ││ INDONESIA │ INDIA     │ 1992 │ 536684093041 ││ .....     │ .......   │ .... │ ............ ││ CHINA     │ CHINA     │ 1997 │ 525562838002 ││ JAPAN     │ VIETNAM   │ 1997 │ 525495763677 │└───────────┴───────────┴──────┴──────────────┘
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.)

掃描行數(shù)：546,670,000?大約5億4千多萬

耗時(shí)(秒)：3.533

查詢列數(shù)：4

結(jié)果行數(shù)：150

Query 3.2

SELECT     C_CITY,    S_CITY,    toYear(LO_ORDERDATE) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)GROUP BY     C_CITY,    S_CITY,    yearORDER BY     year ASC,    revenue DESC
┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐│ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 ││ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 ││ .......... │ .......... │ .... │ .......... ││ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 ││ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │└────────────┴────────────┴──────┴────────────┘
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.)

查詢列數(shù)：4

結(jié)果行數(shù)：600

Query 4.1

SELECT     toYear(LO_ORDERDATE) AS year,    C_NATION,    sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))GROUP BY     year,    C_NATIONORDER BY     year ASC,    C_NATION ASC
┌─year─┬─C_NATION──────┬────────profit─┐│ 1992 │ ARGENTINA     │ 1041983042066 ││ 1992 │ BRAZIL        │ 1031193572794 ││ .... │ ......        │  ............ ││ 1998 │ PERU          │  603980044827 ││ 1998 │ UNITED STATES │  605069471323 │└──────┴───────────────┴───────────────┘
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.)??

掃描行數(shù)：600,040,000?大約6億

耗時(shí)(秒)：5.066

查詢列數(shù)：4

結(jié)果行數(shù)：35

Query 4.2

SELECT     toYear(LO_ORDERDATE) AS year,    S_NATION,    P_CATEGORY,    sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((year = 1997) OR (year = 1998)) AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))GROUP BY     year,    S_NATION,    P_CATEGORYORDER BY     year ASC,    S_NATION ASC,    P_CATEGORY ASC
┌─year─┬─S_NATION──────┬─P_CATEGORY─┬───────profit─┐│ 1997 │ ARGENTINA     │ MFGR#11    │ 102369950215 ││ 1997 │ ARGENTINA     │ MFGR#12    │ 103052774082 ││ .... │ .........     │ .......    │ ............ ││ 1998 │ UNITED STATES │ MFGR#24    │  60779388345 ││ 1998 │ UNITED STATES │ MFGR#25    │  60042710566 │└──────┴───────────────┴────────────┴──────────────┘
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.)

掃描行數(shù)：144,420,000?大約1億4千多萬

耗時(shí)(秒)：0.826

查詢列數(shù)：4

結(jié)果行數(shù)：100

性能測試結(jié)果匯總

在當(dāng)前軟硬件環(huán)境下，掃描 6 億多行數(shù)據(jù)，常見的分析語句首次運(yùn)行最慢在 8 秒左右能返回結(jié)果，相同的分析邏輯更換條件再次查詢的時(shí)候效率有明顯的提升，可以縮短到 1 秒左右，如果只是簡單的列查詢沒有加減乘除、聚合等邏輯，掃描全表 6 億多行數(shù)據(jù)首次查詢基本可以在 2 秒內(nèi)執(zhí)行完成。

clickhouse 億級數(shù)據(jù)性能測試

服務(wù)器信息

Clickhouse信息

測試情況

數(shù)據(jù)量和空間占用

可以看到 clickhouse 的壓縮率很高，壓縮率都在 50 以上，基本可以達(dá)到 70 左右。數(shù)據(jù)體積的減小可以非常有效的減少磁盤空間占用、提高 I/O 性能，這對整體查詢性能的提升非常有效。

查詢性能測試詳情

Query 1.1

Query 1.2

Query 2.1

Query 2.2

Query 3.1

Query 3.2

Query 4.1

Query 4.2

性能測試結(jié)果匯總