注意:本测试使用的是官网的数据集,由于官网聚合操作太多过滤,所以写了一些没有过滤的聚合操作,本测试属于极限测试,真实业务中的聚合操作肯定会有很多过滤操作的

一.表的创建和数据导入

创建表:必须在每个节点创建表,ddl 没有同步 
create database test ; use test ; CREATE TABLE ontime ( Year UInt16, Quarter
UInt8, Month UInt8, DayofMonth UInt8, DayOfWeek UInt8, FlightDate Date,
UniqueCarrier FixedString(7), AirlineID Int32, Carrier FixedString(2), TailNum
String, FlightNum String, OriginAirportID Int32, OriginAirportSeqID Int32,
OriginCityMarketID Int32, Origin FixedString(5), OriginCityName String,
OriginState FixedString(2), OriginStateFips String, OriginStateName String,
OriginWac Int32, DestAirportID Int32, DestAirportSeqID Int32, DestCityMarketID
Int32, Dest FixedString(5), DestCityName String, DestState FixedString(2),
DestStateFips String, DestStateName String, DestWac Int32, CRSDepTime Int32,
DepTime Int32, DepDelay Int32, DepDelayMinutes Int32, DepDel15 Int32,
DepartureDelayGroups String, DepTimeBlk String, TaxiOut Int32, WheelsOff Int32,
WheelsOn Int32, TaxiIn Int32, CRSArrTime Int32, ArrTime Int32, ArrDelay Int32,
ArrDelayMinutes Int32, ArrDel15 Int32, ArrivalDelayGroups Int32, ArrTimeBlk
String, Cancelled UInt8, CancellationCode FixedString(1), Diverted UInt8,
CRSElapsedTime Int32, ActualElapsedTime Int32, AirTime Int32, Flights Int32,
Distance Int32, DistanceGroup UInt8, CarrierDelay Int32, WeatherDelay Int32,
NASDelay Int32, SecurityDelay Int32, LateAircraftDelay Int32, FirstDepTime
String, TotalAddGTime String, LongestAddGTime String, DivAirportLandings
String, DivReachedDest String, DivActualElapsedTime String, DivArrDelay String,
DivDistance String, Div1Airport String, Div1AirportID Int32, Div1AirportSeqID
Int32, Div1WheelsOn String, Div1TotalGTime String, Div1LongestGTime String,
Div1WheelsOff String, Div1TailNum String, Div2Airport String, Div2AirportID
Int32, Div2AirportSeqID Int32, Div2WheelsOn String, Div2TotalGTime String,
Div2LongestGTime String, Div2WheelsOff String, Div2TailNum String, Div3Airport
String, Div3AirportID Int32, Div3AirportSeqID Int32, Div3WheelsOn String,
Div3TotalGTime String, Div3LongestGTime String, Div3WheelsOff String,
Div3TailNum String, Div4Airport String, Div4AirportID Int32, Div4AirportSeqID
Int32, Div4WheelsOn String, Div4TotalGTime String, Div4LongestGTime String,
Div4WheelsOff String, Div4TailNum String, Div5Airport String, Div5AirportID
Int32, Div5AirportSeqID Int32, Div5WheelsOn String, Div5TotalGTime String,
Div5LongestGTime String, Div5WheelsOff String, Div5TailNum String ) ENGINE =
MergeTree(FlightDate, (Year, FlightDate), 8192); CREATE TABLE ontimetest AS
ontime ENGINE = Distributed(perftest_3shards_1replicas, test, ontime, rand());
下载数据
for s in `seq 1987 2017` do for m in `seq 1 12` do wget
http://transtats.bts.gov/PREZIP/On_Time_On_Time_Performance_${s}_${m}.zip done
done
导入数据
for i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' |
clickhouse-client --host 192.168.121.41 --query="INSERT INTO test.ontimetest
FORMAT CSVWithNames"; done
二.性能测试

配置说明

配置:  3台机器,每台机器32内存,200G磁盘
数据量:  10 G, 3.4亿条 ,每条109个字段

count  性能测试  
select count(1) from (select ArrTime,CRSArrTime,FlightDate from ontimetest
limit 200000000) t1 ;
* 1000 万:0.543      15.81 MB
* 1亿 :   0.863       187.58 MB      
* 2亿 :   0.913       261.31 MB      
* 3亿 :   1.181        485.01 MB 
 

group by 性能测试
SELECT OriginCityName, DestCityName, count(*) AS flights FROM (select
OriginCityName,DestCityName from ontimetest limit 100000000) t1 GROUP BY
OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20 ;
* 100万     0.258         42.72 MB
* 1000万   1.336 s       438.28 MB
* 1亿      11.186 s        4.47 GB 
* 2亿      21.983 s        8.89 GB 
* 3亿      30.538 sec    13.32 GB
join 性能测试 
select * from (select ArrTime,CRSArrTime,FlightDate from ontimetest limit
100000000) t1 ALL INNER JOIN (select ArrTime,CRSArrTime,FlightDate from
ontimetest limit 10000000) t2 on t1.ArrTime=t2.CRSArrTime limit 100 ;
* 1千万  join 10万       0.606 s   8.29 MB
* 1亿    join 10万       0.684 s   8.55 MB
* 2亿    join 10万       0.962 s   7.99 MB
* 3亿    join 10万       1.088  s  8.08 MB
* 1千万  join 100万       11.756 s  13.03 MB 
* 1亿    join 100万       11.795 s  13.27  MB
* 2亿    join 100万       11.972 s  13.50 MB
* 3亿    join 100万       12.825s   14.47 MB
* 1千万  join 1000万       150.931 s   42.33 MB
* 1亿    join 1000万       164.897 s   43.50 MB
* 2亿    join 1000万       168.973 s   46.99 MB
* 3亿    join 1000万       169.694     49.63  MB
 

性能测试总结:

* 在count 方面,速度很快,消耗内存较大
* 在group by  方面,速度很快,消耗内存很大
* 在 join 方面,速度很快(相对于spark而言,作为实时查询系统,还是较慢),消耗内存较小,但是消耗CPU较大
其他方面:

* 并发较小,官网查询建议100 Queries / second,所以不适合做业务型高并发查询
* 列式存储:数据格式很类似,所以易于压缩,减小IO的消耗 (hadoop
是行存储,加载数据很耗费时间);在列式的查询中,速度很快,但是在查询字段较多的情况下,速度较慢
定位:

* 实时处理方面,处理的数据量远大于 mysql,但是count 和 group by 消耗内存较多,并发较高的情况下,业务服务器难以承受;group
by,join查询时 性能还是不能达到妙级响应的要求;并发较小 ,100 Queries / second;所以不适合做业务型高并发实时查询
* 在批处理方面,由于其列式存储的设计,减小IO的消耗  等原因 计算性能远超 spark,hive ;100 Queries /
second远高于spark几十个并发任务的数量;所以可以替代hadoop集群作为批处理离线框架