Clickhouse-CPU内存资源优化配置

优化

CPU资源优化

config.xml 官网配置项 https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/

物理机的CPU资源：

# 总核数 = 物理CPU个数 X 每颗物理CPU的核数 
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数

# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l

# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq

# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l

CPU配置优化：

background_pool_size

此配置在users.xml中，用于设置后台线程池的大小， merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，允许的前提下建议改成 cpu 个数的 2倍（线程数）。

<background_pool_size>128</background_pool_size>

background_schedule_pool_size

此配置在users.xml中，用于设置执行后台任务（复制表、 Kafka 流、DNS 缓存更新）的线程数。 默认 128，建议改成 cpu 个数的 2 倍（线程数）。

<background_schedule_pool_size>128</background_schedule_pool_size>

background_distributed_schedule_pool_size

此配置在users.xml中，用于设置分布式发送执行后台任务的线程数，默认 16，建议改成 cpu个数的 2 倍（线程数）。

<background_distributed_schedule_pool_size>128</background_distributed_schedule_pool_size>

max_concurrent_queries

此配置在config.xml中，用于设置最大并发处理的请求数 (包含 select,insert 等)，默认值 100，推荐 200(不够再加)~300。

<max_concurrent_queries>200</max_concurrent_queries>

内存资源优化

config.xml 官网的配置项 https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/

物理机的内存资源：

# 以GB显示，如下物理机内存资源为125gb
$ free -g
total        used        free      shared  buff/cache   available
Mem:            125          48          26           0          50          76
Swap:             0           0           0

内存配置优化：

服务器内存优化

echo 'never' | sudo tee /sys/kernel/mm/transparent_hugepage/enabled

max_memory_usage

此参数在 users.xml中, 表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，比如 128G内存的机器，设置为 100GB。

<max_memory_usage>100000000000</max_memory_usage>

对于clickhouse而言，一下语句都算查询语句，包括insert插入

max_bytes_before_external_group_by

此参数在 users.xml中, 一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。注意变量是字节

<max_bytes_before_external_group_by>50000000000</max_bytes_before_external_group_by>

max_bytes_before_external_sort

此参数在 users.xml中, 当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，但比报错好)。

建议是max_memory_usage的0.8

<max_bytes_before_external_sort>80000000000</max_bytes_before_external_sort>

max_table_size_to_drop

此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。建议修改为 0，
这样不管多大的分区表都可以删除。

<max_table_size_to_drop>0</max_table_size_to_drop>

CPU内存优化配置示例

config.xml

<max_concurrent_queries>200</max_concurrent_queries>
<max_table_size_to_drop>0</max_table_size_to_drop>

users.xml

<background_pool_size>128</background_pool_size>
<max_memory_usage>100000000000</max_memory_usage>
<max_bytes_before_external_group_by>50000000000</max_bytes_before_external_group_by>
<max_bytes_before_external_sort>80000000000</max_bytes_before_external_sort>
<background_schedule_pool_size>128</background_schedule_pool_size>
<background_distributed_schedule_pool_size>128</background_distributed_schedule_pool_size>

冷热磁盘

官网链接：https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes

熔断机制

可以设置单次sql查询的熔断机制，比如用户某次查询时间过长或占用太多内存时熔断机制，此处可以查看官网
官网地址：https://clickhouse.com/docs/en/operations/quotas

sql优化

强烈建议所有待执行的sql都先经过语法优化后的语法：