实际业务SQL对比测试ClickHouse、TIDB和DorisDB

本人水平有限, 如有误导请见谅

说明

  1. 本次测试使用的SQL语句为公司实际业务SQL, 出于数据安全考虑不做SQL和表结构展示

  2. 进行本次测试时对DorisDB了解甚微, 表结构有待优化, 没有使用DorisDB提供的任何特性, 没有改写SQL(clickhouse需要做一些改写)

  3. 本次测试由于服务器资源有限, 没有严格遵循单一变量原则进行测试

  4. 本次测试TIDB没有部署TIFlash

环境信息

ClickHouse:

  • 6台 华为云ECS h3.4xlarge.4 | 16vCPUs | 64GB | 超高IO SSD

DorisDB:

  • 3台 华为云ECS h3.2xlarge.4 | 8vCPUs | 32GB | 超高IO SSD

由于资源紧张, DorisDB所在服务器上还部署了mysql, 但过测试期间无人使用, 实际可用内存16G.

TIDB:

  • 26台TIKV 华为云ECS i3.2xlarge.8 | 8vCPUs | 64GB | 本地NVME SSD(华为最高性能SSD),
  • 3台TIDB 华为云ECS m3.8xlarge.8| 32vCPUs | 256GB
  • 3台PD 华为云ECS 8vCPUs | 16GB | c3.2xlarge.2
  • 1台monitor 华为云ECS c3.2xlarge.4 | 8vCPUs | 32GB | 超高IO SSD

DorisDB:

  • DorisDB-SE-1.12.1 3Fe 3Be, fe和be部署在一起

  • 'root'@127.0.0.1 15:11:37 [db1]> show create table t1\G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      xx1 varchar(128) NOT NULL DEFAULT "" ,
      xx2 date NOT NULL ,
      xx3 varchar(128) NOT NULL DEFAULT "" ,
      xx4 bigint(20) NOT NULL DEFAULT "0" ,
      xx5 bigint(20) NOT NULL DEFAULT "0" ,
      xx6 bigint(20) NOT NULL DEFAULT "0" ,
      xx7 varchar(3) NOT NULL DEFAULT "" ,
      xx8 date NOT NULL ,
      xx9 varchar(24) NOT NULL DEFAULT "" ,
      xx10 tinyint(4) NOT NULL DEFAULT "0" ,
      xx11 varchar(6) NOT NULL DEFAULT "" ,
      xx12 varchar(6) NOT NULL DEFAULT "000000" ,
      xx13 varchar(6) NOT NULL DEFAULT "000000" ,
      xx14 varchar(6) NOT NULL DEFAULT "000000" ,
      xx15 varchar(128) NOT NULL DEFAULT "" ,
      xx16 bigint(20) NOT NULL DEFAULT "0" ,
      xx17 varchar(100) NOT NULL DEFAULT "" ,
      xx18 int(11) NOT NULL DEFAULT "0" ,
      `D_CREATED_AT` datetime NULL COMMENT "创建时间",
      `D_UPDATED_AT` datetime NULL COMMENT "更新时间"
    ) ENGINE=OLAP
    DUPLICATE KEY(`CH_DEALER_ID`)
    COMMENT "OLAP"
    PARTITION BY RANGE(`I_DATE`)
    (PARTITION p201709 VALUES [('0000-01-01'), ('2017-10-01')),
    PARTITION p201710 VALUES [('2017-10-01'), ('2017-11-01')),
    PARTITION p201711 VALUES [('2017-11-01'), ('2017-12-01')),
    PARTITION p201712 VALUES [('2017-12-01'), ('2018-01-01')),
    PARTITION p201801 VALUES [('2018-01-01'), ('2018-02-01')),
    PARTITION p201802 VALUES [('2018-02-01'), ('2018-03-01')),
    PARTITION p201803 VALUES [('2018-03-01'), ('2018-04-01')),
    PARTITION p201804 VALUES [('2018-04-01'), ('2018-05-01')),
    PARTITION p201805 VALUES [('2018-05-01'), ('2018-06-01')),
    PARTITION p201806 VALUES [('2018-06-01'), ('2018-07-01')),
    PARTITION p201807 VALUES [('2018-07-01'), ('2018-08-01')),
    PARTITION p201808 VALUES [('2018-08-01'), ('2018-09-01')),
    PARTITION p201809 VALUES [('2018-09-01'), ('2018-10-01')),
    PARTITION p201810 VALUES [('2018-10-01'), ('2018-11-01')),
    PARTITION p201811 VALUES [('2018-11-01'), ('2018-12-01')),
    PARTITION p201812 VALUES [('2018-12-01'), ('2019-01-01')),
    PARTITION p201901 VALUES [('2019-01-01'), ('2019-02-01')),
    PARTITION p201902 VALUES [('2019-02-01'), ('2019-03-01')),
    PARTITION p201903 VALUES [('2019-03-01'), ('2019-04-01')),
    PARTITION p201904 VALUES [('2019-04-01'), ('2019-05-01')),
    PARTITION p201905 VALUES [('2019-05-01'), ('2019-06-01')),
    PARTITION p201906 VALUES [('2019-06-01'), ('2019-07-01')),
    PARTITION p201907 VALUES [('2019-07-01'), ('2019-08-01')),
    PARTITION p201908 VALUES [('2019-08-01'), ('2019-09-01')),
    PARTITION p201909 VALUES [('2019-09-01'), ('2019-10-01')),
    PARTITION p201910 VALUES [('2019-10-01'), ('2019-11-01')),
    PARTITION p201911 VALUES [('2019-11-01'), ('2019-12-01')),
    PARTITION p201912 VALUES [('2019-12-01'), ('2020-01-01')),
    PARTITION p202001 VALUES [('2020-01-01'), ('2020-02-01')),
    PARTITION p202002 VALUES [('2020-02-01'), ('2020-03-01')),
    PARTITION p202003 VALUES [('2020-03-01'), ('2020-04-01')),
    PARTITION p202004 VALUES [('2020-04-01'), ('2020-05-01')),
    PARTITION p202005 VALUES [('2020-05-01'), ('2020-06-01')),
    PARTITION p202006 VALUES [('2020-06-01'), ('2020-07-01')),
    PARTITION p202007 VALUES [('2020-07-01'), ('2020-08-01')),
    PARTITION p202008 VALUES [('2020-08-01'), ('2020-09-01')),
    PARTITION p202009 VALUES [('2020-09-01'), ('2020-10-01')),
    PARTITION p202010 VALUES [('2020-10-01'), ('2020-11-01')),
    PARTITION p202011 VALUES [('2020-11-01'), ('2020-12-01')),
    PARTITION p202012 VALUES [('2020-12-01'), ('2021-01-01')),
    PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')),
    PARTITION p202102 VALUES [('2021-02-01'), ('2021-03-01')),
    PARTITION p202103 VALUES [('2021-03-01'), ('2021-04-01')),
    PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')),
    PARTITION p202105 VALUES [('2021-05-01'), ('2021-06-01')),
    PARTITION p202106 VALUES [('2021-06-01'), ('2021-07-01')),
    PARTITION p202107 VALUES [('2021-07-01'), ('2021-08-01')),
    PARTITION p202108 VALUES [('2021-08-01'), ('2021-09-01')),
    PARTITION p202109 VALUES [('2021-09-01'), ('2021-10-01')))
    DISTRIBUTED BY HASH(`I_USER_ID`) BUCKETS 192
    PROPERTIES (
    "replication_num" = "1",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-2147483648",
    "dynamic_partition.end" = "7",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.replication_num" = "-1",
    "dynamic_partition.buckets" = "192",
    "dynamic_partition.start_day_of_month" = "1",
    "in_memory" = "false",
    "storage_format" = "DEFAULT"
    );
     
     
    t1表数据均匀分布在三个分片上
    'root'@127.0.0.1 15:11:29 [db1]> ADMIN SHOW REPLICA DISTRIBUTION FROM t1;
    +-----------+------------+-------+---------+
    | BackendId | ReplicaNum | Graph | Percent |
    +-----------+------------+-------+---------+
    | 10003     | 3136       | >     | 33.33 % |
    | 10004     | 3136       | >     | 33.33 % |
    | 10005     | 3136       | >     | 33.33 % |
    +-----------+------------+-------+---------+
    3 rows in set (0.03 sec)
     
     
    'root'@127.0.0.1 15:01:48 [db1]> SHOW DATA;
    +-------------+-------------+--------------+
    | TableName   | Size        | ReplicaCount |
    +-------------+-------------+--------------+
    | t1          | 10.335 GB   | 9408         |
    +-------------+-------------+--------------+
    

ClickHouse:

  • 20.10.3.30, 三分片两副本

  • SHOW CREATE TABLE t1
     
    Row 1:
    ──────
    statement: CREATE TABLE db1.t1
    (
        `I_ID` Int64 COMMENT '主键',
        `xx2` Int32 DEFAULT 0 ,
        `xx3` String DEFAULT '' ,
        `xx1` String DEFAULT '' ,
        `xx4` Int64 DEFAULT 0 ,
        `xx5` Int64 DEFAULT 0 ,
        `xx6` Int64 DEFAULT 0 ,
        `xx7` String DEFAULT '' ,
        `xx8` DateTime DEFAULT '0001-01-01 00:00:00' ,
        `xx9` String DEFAULT '' ,
        `xx10` Int8 DEFAULT 0 ,
        `xx11` String DEFAULT '' ,
        `xx12` String DEFAULT '000000' ,
        `xx13` String DEFAULT '000000' ,
        `xx14` String DEFAULT '000000' ,
        `xx15` String DEFAULT '' ,
        `xx16` Int64 DEFAULT 0 ,
        `xx17` String DEFAULT '' ,
        `xx18` Int32 DEFAULT 0 ,
        `D_CREATED_AT` DateTime DEFAULT toDateTime(now()) COMMENT '创建时间',
        `D_UPDATED_AT` DateTime DEFAULT toDateTime(now()) COMMENT '更新时间'
    )
    ENGINE = ReplicatedReplacingMergeTree('/clickhouse/db1/tables/{layer}-{shard}/t1', '{replica}')
    PARTITION BY toYYYYMM(D_CREATED_AT)
    ORDER BY (xx3, xx1, xx4, xx2)
    SETTINGS index_granularity = 8192
     
     
     
     
    t1表数据均匀分布在三个分片上
    SELECT
        _shard_num,
        count(*)
    FROM
    (
        SELECT
            _shard_num,
            a.*
        FROM t1 AS a
    )
    GROUP BY _shard_num
        WITH ROLLUP
     
    ┌─_shard_num─┬───count()─┐
    │          3 │ 176223822 │
    │          2 │ 176614748 │
    │          1 │ 176629240 │
    └────────────┴───────────┘
    ┌─_shard_num─┬───count()─┐
    │          0 │ 529467810 │
    └────────────┴───────────┘
     
     
    #du -sh t1_local
    4.2G    t1_local
     
     
    4.2*3=12.6G
    

TIDB:

  • 4.0.7 所有33个节点均独立部署

  • t1表数据分布在所有26个TIKV上, 但region leader分布并不均匀
     
     
    SELECT
        LEADER_STORE_ID,
        count(*) AS cnt
    FROM t1
    GROUP BY LEADER_STORE_ID
    ORDER BY cnt DESC
     
    ┌─LEADER_STORE_ID─┬─cnt─┐
    │         1840333 │ 568 │
    │         1840338 │ 538 │
    │         1840334 │ 534 │
    │               4 │ 524 │
    │         4783004 │ 500 │
    │         1840336 │ 493 │
    │         1840335 │ 489 │
    │         1840342 │ 480 │
    │         1840332 │ 467 │
    │         1840343 │ 463 │
    │               2 │ 452 │
    │         1840331 │ 450 │
    │         4783006 │ 421 │
    │         4783005 │ 413 │
    │         1840339 │ 404 │
    │         1840337 │ 403 │
    │               3 │ 403 │
    │              16 │ 398 │
    │         1840341 │ 388 │
    │         1840344 │ 386 │
    │               5 │ 379 │
    │         4783002 │ 359 │
    │         4783003 │ 347 │
    │               1 │ 341 │
    │         1840340 │ 332 │
    │          348046 │ 307 │
    └─────────────────┴─────┘
     
     
     
    MySQL [stat]> show table status like 't1'\G
    *************************** 1. row ***************************
               Name: t1
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 534491754
     Avg_row_length: 177
        Data_length: 94744599269
    Max_data_length: 0
       Index_length: 86064550224
          Data_free: 0
     Auto_increment: 6023598555
        Create_time: 2020-08-28 08:34:27
        Update_time: NULL
         Check_time: NULL
          Collation: utf8mb4_unicode_ci
           Checksum:
     Create_options:
            Comment: xx日汇总表
    1 row in set (0.03 sec)
     
    MySQL [stat]> select 94744599269+86064550224;
    +-------------------------+
    | 94744599269+86064550224 |
    +-------------------------+
    |            180809149493 |
    +-------------------------+
    1 row in set (0.00 sec)
     
    MySQL [stat]> select 180809149493/1024/1024/1024;
    +-----------------------------+
    | 180809149493/1024/1024/1024 |
    +-----------------------------+
    |            168.391642619856 |
    +-----------------------------+
    1 row in set (0.00 sec)
    

测试结果

单位毫秒, Y轴值越小越好

Q19 DorisDB oom(可用内存只有16G, 而ClickHouse有50G, TIDB 256G)

QUERY DorisDB ClickHouse TIDB
Q1 790 488 30,807
Q2 530 1,231 26,866
Q3 320 361 25,007
Q4 660 400 7,594
Q5 20 21 326
Q6 880 492 86,034
Q7 9,220 2,271 89,744
Q8 530 634 6,566
Q9 270 605 2,690
Q10 90 387 1,916
Q11 110 536 2,555
Q12 410 7,427 12,834
Q13 580 5,631 13,179
Q14 21,700 8,849 171,423
Q15 9,380 4,836 6,292
Q16 16,290 8,649 98,126
Q17 770 838 7,755
Q18 420 551 5,170
Q19 无结果 19,458 无结果
Q20 2,610 1,423 2,184
Q21 150 1,561 1,948
Q22 460 509 3,208
Q23 未测试 未测试 未测试
Q24 39,190 15,915 76,091

由于TIDB性能过差, 上述图表中无法很好展示DorisDB和ClickHouse的对比结果, 故下图中排除TIDB制图

QUERY DorisDB ClickHouse
Q1 340 488
Q2 530 1,231
Q3 200 361
Q4 390 400
Q5 240 21
Q6 810 492
Q7 2,370 2,271
Q8 290 634
Q9 240 605
Q10 100 387
Q11 120 536
Q12 890 7,427
Q13 1,410 5,631
Q14 7,780 8,849
Q15 5,050 4,836
Q16 8,400 8,649
Q17 690 838
Q18 260 551
Q19 无结果 19,458
Q20 1,140 1,423
Q21 170 1,561
Q22 290 509
Q23 未测试 未测试
Q24 14,580 15,915

结论

  1. TIDB
    1. 优点: 持标准SQL; 支持MySQL协议; 数据自动分片无需人工维护, 易用性不错; 支持窗口函数; 支持高并发
    2. 缺点: 成本最高, 性能最差(仅针对OLAP领域); 没有物化视图功能; 仅能覆盖20% OLAP场景;
  2. ClickHouse
    1. 优点: 单机性强悍, 性价比最高; 单表查询速度极快; 有物化视图功能
    2. 缺点:不支持标准SQL; 不支持MySQL协议; 作分片后关联需注意SQL写法, 小表要写到最后; 增加分片数据无法自动rebalance, 人工维护成本高; 函数学习成本较高; 不支持高并发
  3. DorisDB
    1. 优点: 支持标准SQL; 支持MySQL协议; 提供Colocation Join, 分析函数, 布隆过滤, 物化视图, bitmap索引等特性, 增加分片数据自动rebalance; 多表关联性能较好; 支持高并发
    2. 缺点: 新型数据库, Apache Doris发展已有11年历史, 17年前由百度捐献给Apache, 目前仍在孵化中, 社区比较活跃, 有很多互联网公司应用案例用户, 但DorisDB应用案例还较少, 还需持续观望;

从实际业务场景测试结果看, DorisDB和ClickHouse性能和成硬件本均秒杀TIDB, 同时DorisDB性能优于ClickHouse且是本次测试中硬件配置最差的. DorisDB支持MySQL协议, 标准SQL, 对于研发的代码改动量更少, 易用性更佳.

1赞