記一次 ClickHouse 效能測試

語言: CN / TW / HK

前言

在工作場景中,我們會採集工廠裝置資料用於智慧控制,資料的儲存用了 InfluxDB,隨著資料規模越來越大,InfluxDB 的效能越來越差,故考慮引入 ClickHouse 分擔 InfluxDB 大資料分析的壓力,再加上我們業務上也用到了 MySQL ,所以本文就來對比下 MySQL、InfluxDB、ClickHouse 在千萬資料量下的寫入耗時、聚合查詢耗時、磁碟佔用等各方面效能指標。

結論先行

最終的結論是,直接使用 ClickHouse 官網提供的 6600w 資料集來做對比測試,在 MySQL、InfluxDB、ClickHouse 同樣分配 4c16g 資源的情況下,ClickHouse 無論是匯入速度、磁碟佔用、查詢效能都完全碾壓 MySQL 和 InfluxDB,具體對比指標如以下表格:

MySQL InfluxDB ClickHouse
匯入耗時 大概耗時70分鐘 大概耗時35分鐘 75秒
磁碟空間 12.35 G 5.9 G 2.66 G
全表count 24366 ms 11674 ms 100 ms
全表max/min 27023 ms 26829 ms 186 ms
全表平均值 24841 ms 12043 ms 123 ms
全表方差 24600 ms OOM 113 ms
複雜查詢1 30260 ms OOM 385 ms
複雜查詢2 470 ms 200 ms 8 ms

為了確保測試結果相對準確,以上每條sql起碼執行5次,然後取中間值。其中 InfluxDB 表現比想象中的要差,甚至還不如 MySQL,可能是資料樣本和測試用例不太適合 InfluxDB 場景導致的,如果大家對測試結果有疑問,可以 git clone [https://github.com/stone0090/clickhouse-test.git](https://github.com/stone0090/clickhouse-test.git) 專案,完整驗證以上對比全過程。

資料庫簡介

MySQL

MySQL 是一個關係型資料庫管理系統,由瑞典 MySQL AB 公司開發,屬於 Oracle 旗下產品,是最流行的關係型資料庫管理系統之一。它所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。它採用了雙授權政策,分為社群版和商業版,由於其體積小、速度快、總體擁有成本低,尤其是開放原始碼這一特點,一般中小型和大型網站的開發都選擇 MySQL 作為網站資料庫。《高效能MySQL》一書中開篇明義講到的最核心的一句話是“MySQL並不完美,但是卻足夠靈活”,它是架構中的萬金油,龐雜非單一的專案中總會有它的用武之地。

InfluxDB

InfluxDB 是一個由 InfluxData 公司開發的開源時序型資料庫,專注於海量時序資料的高效能讀、高效能寫、高效儲存與實時分析,在 DB-Engines Ranking 時序型資料庫排行榜上位列榜首,廣泛應用於DevOps監控、IoT監控、實時分析等場景。

傳統資料庫通常記錄資料的當前值,時序型資料庫則記錄所有的歷史資料,在處理當前時序資料時又要不斷接收新的時序資料,同時時序資料的查詢也總是以時間為基礎查詢條件,並專注於解決以下海量資料場景的問題:

  • 時序資料的寫入:如何支援千萬級/秒資料的寫入;
  • 時序資料的讀取:如何支援千萬級/秒資料的聚合和查詢;
  • 成本敏感:海量資料儲存帶來的是成本問題,如何更低成本地儲存這些資料。

ClickHouse

ClickHouse 是 Yandex(俄羅斯最大的搜尋引擎)開源的一個用於實時資料分析的基於列儲存的資料庫,其處理資料的速度比傳統方法快 100-1000 倍。ClickHouse 的效能超過了目前市場上可比的面向列的 DBMS,每秒鐘每臺伺服器每秒處理數億至十億多行和數十千兆位元組的資料。它是一個用於聯機分析(OLAP)的列式資料庫管理系統(DBMS),簡單介紹一下 OLTP 和 OLAP。

  • OLTP:是傳統的關係型資料庫,主要操作增刪改查,強調事務一致性,比如銀行系統、電商系統。
  • OLAP:是倉庫型資料庫,主要是讀取資料,做複雜資料分析,側重技術決策支援,提供直觀簡單的結果。

那 ClickHouse OLAP 適用場景有:1)讀多於寫;2)大寬表,讀大量行但是少量列,結果集較小;3)資料批量寫入,且資料不更新或少更新;4)無需事務,資料一致性要求低;5)靈活多變,不適合預先建模。

環境準備

在阿里雲買一臺 16c64g 的伺服器,作業系統 centos 7.8,使用 sealos 一鍵安裝 k8s,使用 helm 一鍵安裝 mysql(5.7)、influxdb(1.8)、clickhouse(22.3) ,每個應用各分配 4c16g 的資源。

# 下載 sealos
$ wget https://github.com/labring/sealos/releases/download/v4.0.0/sealos_4.0.0_linux_amd64.tar.gz \
&& tar zxvf sealos_4.0.0_linux_amd64.tar.gz sealos && chmod +x sealos && mv sealos /usr/bin

# 初始化一個單節點 Kubernetes
$ sealos run labring/kubernetes:v1.24.0 labring/calico:v3.22.1 --masters [xxx.xxx.xxx.xxx] -p [your-ecs-password]

# 去掉 master 的汙點,允許安裝應用到 master 和 control-plane
$ kubectl taint nodes --all node-role.kubernetes.io/master-
$ kubectl taint nodes --all node-role.kubernetes.io/control-plane-

# 獲取 mysql、influxdb、clickhouse 一鍵安裝 Helm-Charts
$ wget https://github.com/stone0090/clickhouse-test/archive/refs/tags/v1.0.0.tar.gz
$ tar -zxvf v1.0.0.tar.gz

# 安裝 Kubernetes 包管理工具 Helm,以及 mysql、influxdb、clickhouse 3大資料庫
$ sealos run labring/helm:v3.8.2
$ helm install mysql clickhouse-test-1.0.0/helm-charts/mysql/
$ helm install influxdb clickhouse-test-1.0.0/helm-charts/influxdb/
$ helm install clickhouse clickhouse-test-1.0.0/helm-charts/clickhouse/

資料匯入

直接使用 ClickHouse 官方提供的測試資料 https://clickhouse.com/docs/zh/getting-started/example-datasets/opensky ,此資料集中的資料是從完整的 OpenSky 資料集中派生和清理而來的,以說明 COVID-19 新冠肺炎大流行期間空中交通的發展情況。它涵蓋了自2019年1月1日以來該網路超過2500名成員看到的所有航班,總資料量有6600w。

# 在伺服器 /home/flightlist 目錄執行以下命令,該目錄會被掛載到 mysql-pod、influxdb-pod、clickhouse-pod 內
$ wget -O- https://zenodo.org/record/5092942 | grep -oP 'https://zenodo.org/record/5092942/files/flightlist_\d+_\d+\.csv\.gz' | xargs wget

# 批量解壓 flightlist.gz 資料
$ for file in flightlist_*.csv.gz; do gzip -d "$file"; done

# 將 csv 處理成 influxdb 匯入所需的 txt 格式(此過程大概耗時1小時)
$ python clickhouse-test-1.0.0/influxdb_csv2txt.py

MySQL

# 進入 mysql pod
$ kubectl exec -it [influxdb-podname] -- bash

# 連上 mysql 建庫、建表
$ mysql -uroot -p123456
$ use test;
$ CREATE TABLE `opensky` (`callsign` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`icao24` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`registration` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`typecode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`origin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`destination` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`firstseen` datetime DEFAULT NULL,`lastseen` datetime DEFAULT NULL,`day` datetime DEFAULT NULL,`latitude_1` double DEFAULT NULL,`longitude_1` double DEFAULT NULL,`altitude_1` double DEFAULT NULL,`latitude_2` double DEFAULT NULL,`longitude_2` double DEFAULT NULL,`altitude_2` double DEFAULT NULL,KEY `idx_callsign` (`callsign`),KEY `idx_origin` (`origin`),KEY `idx_destination` (`destination`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# 匯入資料(大概耗時70分鐘)
$ load data local infile 'flightlist_20190101_20190131.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by '\n' ignore 1 lines;
# 省略其他29條匯入命令:load data local infile 'flightlist_*_*.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by '\n' ignore 1 lines;

# 檢查資料是否匯入成功
$ select count(*) from test.opensky;

InfluxDB

# 進入 influxdb pod
$ kubectl exec -it [influxdb-podname] -- bash

# 匯入資料(大概耗時30分鐘)
$ influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_20190101_20190131.txt -precision=ns;
# 省略其他29條匯入命令:influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_*_*.txt -precision=ns;

# 檢查資料是否匯入成功
$ influx -username 'admin' -password 'admin123456'
$ select count(latitude_1) from test.autogen.opensky;

ClickHouse

# 進入 clickhouse pod
$ kubectl exec -it [clickhouse-podname] -- bash

# 連上 clickhouse 建庫、建表
$ clickhouse-client
$ create database test;
$ use test;
$ CREATE TABLE opensky(callsign String,number String,icao24 String,registration String,typecode String,origin String,destination String,firstseen DateTime,lastseen DateTime,day DateTime,latitude_1 Float64,longitude_1 Float64,altitude_1 Float64,latitude_2 Float64,longitude_2 Float64,altitude_2 Float64) ENGINE = MergeTree ORDER BY (origin, destination, callsign);
$ exit

# 匯入資料(大概耗時75秒)
$ cd /tmp/flightlist
$ for file in flightlist_*.csv; do cat "$file" | clickhouse-client --date_time_input_format best_effort --query "INSERT INTO test.opensky FORMAT CSVWithNames"; done

# 檢查資料是否匯入成功
$ clickhouse-client
$ SELECT count() FROM test.opensky;

測試場景

MySQL

$ mysql -uroot -p123456
$ use test;
-- 開啟效能分析
set profiling = 1;
-- 查詢磁碟空間
select table_rows as `總行數`, (data_length + index_length)/1024/1024/1024 as `磁碟佔用(G)` from information_schema.`TABLES` where table_name = 'opensky';
-- 全表count
select count(latitude_1) from opensky;
-- 全表max/min
select max(longitude_1),min(altitude_1) from opensky;
-- 全表平均值
select avg(latitude_2) from opensky;
-- 全表方差
select var_pop(longitude_2) from opensky;
-- 複雜查詢1:全表多個欄位聚合查詢
select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky;
-- 複雜查詢2:從莫斯科三個主要機場起飛的航班數量
SELECT origin, count(1) AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin;
-- 輸出分析結果
show profiles;

InfluxDB

$ influx -username 'admin' -password 'admin123456'
$ use test;
-- 耗時統計,queryReqDurationNs 是累計查詢時間,2次任務的時間相減就是耗時
select queryReq,queryReqDurationNs/1000/1000,queryRespBytes from _internal."monitor".httpd order by time desc limit 10;
-- 查詢磁碟空間
select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s group by "database";
-- 全表count
select count(latitude_1) from opensky;
-- 全表max/min
select max(longitude_1),min(altitude_1) from opensky;
-- 全表平均值
select mean(latitude_2) from opensky;
-- 全表方差
select stddev(longitude_2) from opensky;
-- 複雜查詢1:全表多個欄位聚合查詢
select count(latitude_1),max(longitude_1),min(altitude_1),mean(latitude_2) from opensky;
-- 複雜查詢2:從莫斯科三個主要機場起飛的航班數量
SELECT count(latitude_1) AS c FROM opensky WHERE origin =~/^UUEE|UUDD|UUWW$/ GROUP BY origin;

ClickHouse

$ clickhouse-client
$ use test;
-- 耗時統計
select event_time_microseconds,query_duration_ms,read_rows,result_rows,memory_usage,query from system.query_log where query like '%opensky%' and query_duration_ms <> 0 and query not like '%event_time_microseconds%' order by event_time_microseconds desc limit 5;
-- 查詢磁碟空間
SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'opensky';
-- 全表count
select count(latitude_1) from opensky;
-- 全表max/min
select max(longitude_1),min(altitude_1) from opensky;
-- 全表平均值
select avg(latitude_2) from opensky;
-- 全表方差
select var_pop(longitude_2) from opensky;
-- 複雜查詢1:全表多個欄位聚合查詢
select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky;
-- 複雜查詢2:從莫斯科三個主要機場起飛的航班數量
SELECT origin, count() AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin;

ClickHouse 為什麼快

1、列式儲存

資料是按列儲存,資料即是索引;查詢只訪問涉及的列,降低系統I/O;每一列都由一個執行緒來處理,高效利用CPU資源;還為向量化執行做好了鋪墊。

2、資料壓縮

資料壓縮的本質是按照一定的步長對資料進行匹配掃描,當發現重複資料的時候就進行編碼轉換。

因為是列式儲存,所以資料特徵很相似,所以資料中的重複項多,則壓縮率越高,則資料體量越小,則磁碟I/O壓力越小,則網路中傳輸越快。

3、向量化執行引擎

SIMD(Single Instruction Multiple Data)即單條指令操作多條資料,它是通過資料並行以提高效能的一種方式,可以簡單理解為在暫存器層面對程式中的資料做並行處理,Clickhouse 在能夠提升計算效率的地方大量使用了 SIMD,通過使用 SIMD,基本上能帶來幾倍的效能提升。

4、多執行緒和分散式

分散式領域存在一條定律,計算移動比資料移動更加划算,這也是其核心所在,將資料的計算直接發放到資料所在的伺服器,多機並行處理,再把最終的結果彙集在一起;另外 ClickHouse 也通過執行緒級別並行的方式為效率進一步提速,極致去利用伺服器的資源。

5、多樣的表引擎

MergeTree 儲存結構對寫入的資料做排序然後進行有序儲存,有序儲存主要有兩大優勢:

  • 列存檔案在按塊做壓縮時,排序鍵中的列值是連續或者重複的,使得列存塊的資料可以獲得極致的壓縮比;
  • 儲存有序本身可以加速查詢的索引結構,根據排序鍵中列的等值條件或者 rang 條件,我們可以快速找到目標所在的近似位置區間,並且這種索引結構是不會產生額外的儲存開銷。

MergeTree 是 ClickHouse 表引擎中最核心的引擎,其他引擎均以 MergeTree 引擎為基礎,並在資料合併過程中實現了不同的特性,從而構成了 MergeTree 表引擎家族。

ClickHouse 的優缺點

優點:極致的查詢分析效能,較低的儲存成本,高吞吐的資料寫入,多樣化的表引擎,完備的 DBMS 功能;

缺點:不支援事務,不支援真正的刪除/更新,分散式能力較弱;不支援高併發,官方建議 QPS 為100;非標準的 SQL,join 的實現比較特殊,且效能不好;頻繁小批量資料操作會影響查詢效能;

目前還沒有一個 OLAP 引擎能夠滿足各種場景的需求,其本質原因是,沒有一個系統能同時在查詢效率、時效性、可維護性三個方面做到完美,只能說 ClickHouse 是為了極致查詢效能做了一些取捨。

ClickHouse 優缺點都很明顯,是否採用還是要取決於和實際業務場景的契合度,適合自己的架構才是最好架構。

參考引用