ClickHouse-建表优化

建表优化

数据类型

时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串,全 String 类型在以 Hive为中心的数仓建设中常见,但 ClickHouse 环境不应受此影响。

虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型,但不建议存储 Long 类型,因为 DateTime 不需要经过函数转换处理,执行效率高、可读性好。

create table t_type2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time Int32
) engine = ReplacingMergeTree(create_time)
partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次,否则报错
primary key (id)
order by (id, sku_id);

create table t_type2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time DateTime
) engine = ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);

空值存储类型

官方已经指出 Nullable 类型几乎总是会拖累性能,因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记,并且 Nullable 列无法被索引。因此除非极特殊情况,应直接使用字段默认值表示空,或者自行指定一个在业务中无意义的值(例如用-1 表示没有商品ID)。

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog;
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x + y FROM t_null;

查看存储的文件:(没有权限就用 root 用户)

[root@hadoop113 t_null]# pwd
/var/lib/clickhouse/data/default/t_null
[root@hadoop113 t_null]# ll
总用量 16
-rw-r----- 1 clickhouse clickhouse 91 12月  9 17:22 sizes.json
-rw-r----- 1 clickhouse clickhouse 28 12月  9 17:22 x.bin
-rw-r----- 1 clickhouse clickhouse 28 12月  9 17:22 y.bin
-rw-r----- 1 clickhouse clickhouse 28 12月  9 17:22 y.null.bin

分区和索引

分区粒度根据业务特点决定,不宜过粗或过细。一般选择按天分区,也可以指定为 Tuple(),以单表一亿数据为例,分区大小控制在 10-30 个为最佳。

必须指定索引列,ClickHouse 中的索引列即排序列,通过 order by 指定,一般在查询条件中经常被用来充当筛选条件的属性被纳入进来;可以是单一维度,也可以是组合维度的索引;通常需要满足高级列在前、查询频率大的在前原则;还有基数特别大的不适合做索引列,如用户表的 userid 字段;通常筛选后的数据满足在百万以内为最佳。

比如官方案例的 hits_v1 表:

......
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
......

visits_v1 表:

......
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
......

表参数

Index_granularity 是用来控制索引粒度的,默认是 8192,如非必须不建议调整。

如果表中不是必须保留全量历史数据,建议指定 TTL (生存时间值),可以免去手动过期历史数据的麻烦, TTL 也可以通过 alter table 语句随时修改。

写入和删除优化

(1)尽量不要执行单条或小批量删除和插入操作,这样会产生小分区文件,给后台Merge 任务带来巨大压力，同理其他数据仓库也最好是攒批写入。

(2)不要一次写入太多分区,或数据写入太快,数据写入太快会导致 Merge 速度跟不上而报错,一般建议每秒钟发起 2-3 次写入操作,每次操作写入 2w~5w 条数据(依服务器性能而定)

写入过快报错,报错信息:

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888bytes), maximum: 9.31 GiB

处理方式:

“ Too many parts 处理 ” : 使用 WAL 预写日志,提高写入性能。攒批插入。
in_memory_parts_enable_wal 默认为 true

在服务器内存充裕的情况下增加内存配额,一般通过 max_memory_usage 来实现

在服务器内存不充裕的情况下,建议将超出部分内容分配到系统硬盘上,但会降低执行速度,一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。

常见配置

配置项主要在 config.xml 或 users.xml 中, 基本上都在 users.xml 里

config.xml 的配置项，服务端配置

https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/

users.xml 的配置项，用户配置

https://clickhouse.tech/docs/en/operations/settings/settings/

CPU 资源

配置	描述
background_pool_size	后台线程池的大小 ,merge 线程 就是在该线程池中 执行,该线程 池不仅仅是给 merge 线程用的,默认值 16,允许的前提下建议改成 cpu 个数的 2 倍(线程数) 。
background_schedule_pool_size	执行后台任务(复制表、 Kafka 流、DNS 缓存更新) 的线程数。默认 128,建议改成 cpu 个数的 2 倍(线程数)。
background_distributed_schedule_pool_size	设置为分布式发送 执行 后台任务 的线程数,默 认 16,建议改 成 cpu pool_size 个数的 2 倍( 线程数) 。
max_concurrent_queries	最大并发处理的请求数(包 含 select,insert 等),默认值 100,推 荐 150(不够再加)~300。
max_threads	设置单个查询所能使用的最 大 cpu 个数,默认是 cpu 核数

ClickHouse建议单独部署，不要和其他服务器一起部署。

ClickHouse最低要求CPU为8核。

内存资源

配置	描述
max_memory_usage	此参数在 users.xml中,表示单次 Query 占用内存最大值 ,该值可以设置的比较大, 这样可以提升 集群查询的上限。 保留一点给 OS,比 如128G 内存 的机器,设置为 100GB。
max_bytes_before_external_group_by	一般按照 max_memory_usage 的一半设置内存,当 group 使用内存超过阈值后会刷新到磁盘进行 。 因为 clickhouse 聚合分两个阶 段 :查询并及建立中间数据、合并 中间数据,结合上一 项,建议 50GB。
max_bytes_before_external_sort	当 order by 已使用 max_bytes_before_external_sort 内存就进 行溢写磁盘(基于磁 盘排序 ),如果不设置该值,那么当内存不够 时直接抛错,设置了该 值 order by 可以 正常完成,但是速度相对存内存来说肯定要慢点(实测慢的非常多 , 无法接受 )。
max_table_size_to_drop	此参数在 config.xml 中,应用 于 需要删除表或分区 的情况,默认 是50GB,意思是如果删除 50GB 以上的分区表会失败。建议修改为 0,这样不管多大的分 区表都可以删 除。

ClickHouse也很吃内存，因为内存使用满了之后，会使用磁盘，那么查询的效率就下来了。

存储

ClickHouse 不支持设置多数据目录,为了提升数据 io 性能,可以挂载虚拟券组,一个券组绑定多块物理磁盘提升读写性能,多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。