clickhouse日常使用规范概述 Clickhouse作为业界普遍采用的时序数据库，在百胜中国的消费者IT应用系统中

概述

Clickhouse作为业界普遍采用的时序数据库，在百胜中国的消费者IT应用系统中被作为实时应用程序和分析的最快、资源效率最高的标准中间件，得到广泛使用。截至本文V1.0版本成文时（2024.04.18），百胜S级业务系统使用多套Clickhouse实例、集群。

为了规范Clickhouse的部署和运维工作、指导开发人员正确地使用Clickhouse，IT运维团队结合Clickhouse的技术特点以及公司的实际现状，制定了本规范。

目前百胜采用Clickhouse集群存储日志，本文档的技术标注基于百胜自建Clickhouse规范、阿里云Clickhouse服务（产品名称：云数据库 ClickHouse）和腾讯云Clickhouse（产品名称：腾讯云数据仓库 TCHouse-C）。本文档应随百胜的Clickhouse管理策略不断迭代、拓展和更新内容。

本文档的面向对象为：公司所有与Clickhouse相关的IT架构设计、开发、运维和DBA人员。

以下内容都是基于Clickhouse23.8.8.20版本撰写，Clickhouse各个版本有差异，如果使用的版本低于或高于23.8.8.20的请酌情参考

一、Clickhouse介绍

Clickhouse简介

Clickhouse是最快、资源效率最高的实时数据仓库和列式分布式开源数据库，支持向量化计算引擎、多核并行计算、高压缩比等功能。

特性

列存储： 仅从存储系统中读取必要的列数据，无用列不读取，速度非常快。
数据压缩 ：列存储，相同列的数据连续存储，且底层数据在存储时是经过排序的，这样数据的局部规律性非常强，有利于获得更高的数据压缩比。
向量化执行： SIMD（Single Instruction Multiple Data）即单条指令操作多条数据，它是通过数据并行以提高性能的一种方式，可以简单理解为在寄存器层面对程序中的数据做并行处理，Clickhouse 在能够提升计算效率的地方大量使用了 SIMD，通过使用 SIMD，基本上能带来几倍的性能提升。
多线程 与 分布式 ：分布式领域存在一条定律，计算移动比数据移动更加划算，这也是其核心所在，将数据的计算直接发放到数据所在的服务器，多机并行处理，再把最终的结果汇集在一起；另外 Clickhouse 也通过线程级别并行的方式为效率进一步提速，极致去利用服务器的资源。
DBMS的功能：几乎覆盖了标准SQL的大部分语法，包括DDL和DML，以及配套的各种函数，用户管理及权限管理，数据的备份与恢复；
多样化引擎：ClickHouse和MySQL类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类20多种引擎；

因此，ClickHouse在百胜业务模式中的⼀些常⻅适⽤场景如下：

a.数据量大：ClickHouse的分布式架构支持动态扩缩容，可支撑海量数据存储。

b.查询场景多：ClickHouse支持分区索引和排序索引，具有很高的检索效率，单机每秒可扫描数百万行的数据。

c.写入性能： ClickHouse的 MergeTree 表的写入速度在200MB/s，具有很高吞吐，写入基本没有瓶颈。

d.存储成本： ClickHouse 基于列式存储，数据压缩比很高，同时基于HDFS做冷热分离，能够进一步地降低存储成本。

⼆、技术规范

版本规范

⽬前百胜运维团队⽀持对如下ClickHouse版本的交付与维护⼯作：

ClickHouse 23.8.8.20

架构规范

百胜ClickHouse服务提供基于多副本的⾼可⽤容灾功能，当主节点发⽣故障时，可实现故障的快速恢复。百胜运维团队⽀持交付的架构有2分⽚2副本sox集群、3分片2副本的集群。生产环境下每台机器对应单分片单实例方式部署，测试环境可以使用多分片多实例部署。

单机房

暂时无法在飞书文档外展示此内容

生产环境3分片2副本集群架构图：
- 暂时无法在飞书文档外展示此内容

暂时无法在飞书文档外展示此内容

chproxy&异地双活的架构

生产环境最小资源使用建议： 使用单分片单实例单副本4节点的方式，如下图clickhouse集群方式

暂时无法在飞书文档外展示此内容

chproxy生产环境每台机器最少资源建议：

Chproxy 主要负责请求的转发和负载均衡，其资源消耗相对较低，CPU要求至少4核心，内存要求至少8GB内存。

clickhouse生产环境每台机器最少资源建议：

CPU至少8核心，ClickHouse能够很好地利用多核心，更多核心可以提供更好的并行处理能力
内存至少32GB 。内存是影响ClickHouse性能的关键因素之一，足够的内存可以确保高效的数据处理和缓存。

架构部署文档参考链接百胜日志转储CK方案，chproxy&异地双活部署

分片、副本规范

分片只是一个逻辑概念，其物理承载还是由副本承担的。

分片： clickhouse数据不同，互为分片；目的是实现数据的水平切割

副本： clickhouse数据相同，互为副本；目的是实现数据的备份，防止数据丢失

路径要求规范
- 应用路径： /data/clickhouse/
- 配置路径： /etc/clickhouse-server/config.xml
- 用户配置路径： /etc/clickhouse-server/users.xml
- 数据路径： /data/clickhouse/
- 日志路径： /data/clickhouse/logs/
- 系统服务配置文件： /usr/lib/systemd/system/clickhouse-server.service

备份要求规范
- 备份时间： 每周六凌晨2点20分
- 异地备份： 每天的备份都传到异地服务器
- 归档： 保留1年的数据，1年以前的数据进行删除

使用表引擎规范

⚠️：引擎是区分大小写的

clickhouse的表引擎是服务的核心，主要解决以下问题：

数据的存储方式和位置
支持哪些查询操作以及如何支持；
数据的并发访问；
数据索引的使用；
是否可以支持多线程请求；
是否可以支持数据复制。

百胜云涉及的表引擎有：

Distributed 表引擎： 属于接口类表引擎，自身不存储任何数据，它能作为分布式表的一层透明代理，在集群内部自动开展数据的写入分发以及查询路由工作。
ReplicatedReplacingMergeTree 表引擎： 属于合并树（MergeTree）表引擎，执行高负载任务的最通用和最强大的表引擎，它们的特点是可以快速插入数据以及进行后续的数据处理。还同时支持数据复制（使用Replicated的引擎版本），分区 (partition) 以及一些其它引擎不支持的额外功能。如：它会删除排序键值相同的重复项。

创建帐号规范

用户配置路径： /etc/clickhouse-server/user.xml
用户创建命令示例：

CREATE ROLE dba_opsrule on cluster ck_sox_cluster;
GRANT ALL PRIVILEGES ON *.* TO dba_opsrule on cluster ck_sox_cluster;
CREATE USER dba_ops IDENTIFIED WITH sha256_password BY 'xxxx'HOST IP '::/0' DEFAULT ROLE dba_opsrule on cluster ck_sox_cluster;

三、资源规格

套餐（性能压测）

Clickhouse套餐	集群模式	Clickhouse可用容量	QPS基线	网络吞吐量（每分片）	备注	自建区VM资源
clickhouse.ha.	2分片2副本				4台机器
clickhouse.ha.	3分片2副本				3台机器

具体资源规格请根据实际业务情况进⾏合理选择，避免造成资源浪费。

四、使用规范

JDBC最佳实践

ClickHouse支持标准的JDBC协议，底层基于HTTP接口通信。Clickhouse JDBC Bridge与jdbc表函数或JDBC表引擎相结合，允许ClickHouse从任何可用JDBC驱动程序的外部数据源访问数据。
- 在外部安装ClickHouse JDBC Bridge
  - 主机上安装、配置和运行ClickHouse JDBC Bridge。
  - 在每个ClickHouse主机上，我们将以下配置块添加到ClickHouse服务器配置中：
    1. ```
     <jdbc_bridge>
        <host>JDBC-Bridge-Host</host>
        <port>9019</port>
     </jdbc_bridge>
```

应用约束
- 指令集检查
ClickHouse可以在任何具有x86_64，AArch64或PowerPC64LE CPU架构的Linux，FreeBSD或Mac OS X上运行。官方预构建的二进制文件通常针对x86_64进行编译，并利用SSE 4.2指令集。

检查当前CPU是否支持SSE 4.2的命令:
```
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"
```

输出SSE 4.2 supported为正常

文件数限制修改

调整CentOS系统对打开文件数的限制， cat >> /etc/security/limits.conf <<EOF soft nofile 65536 hard nofile 65536 soft nproc 131072 hard nproc 131072 EOF cat >> /etc/security/limits.d/20-nproc.conf <<EOF soft nofile 65536 hard nofile 65536 soft nproc 131072 hard nproc 131072 EOF

集群约束：引用zookeeper集群来管理ClickHouse集群的元数据和一些协调任务
配置约束

shard标签要求示例：

#集群中以下标签要求全覆盖
            <shard>
              <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>172.21.28.106</host>
                    <port>9000</port>
                    <user>default</user>
                    <password>xxxxxxxx</password> 
                </replica>
                <replica>
                    <host>172.21.28.107</host>
                    <port>9000</port>
                    <user>default</user>
                    <password>xxxxxxxx</password> 
                </replica>
            </shard>

interserver_http_host标签要求示例：

#需要对应到本机IP地址。
<interserver_http_host>172.21.28.105</interserver_http_host>

3. ### 命令规范

**To do**

1.  【强制】查询是需要指定分区
2.  【强制】千万以上数据集进行GROUP BY 需要指定 LIMIT 子句
3.  【强制】开启自动清理数据功能
4.  【强制】分布式表提供查询，代理 + 本地表提供数据写入
5.  【强制】应设置用户登录密码
6.  【强制】减少慢查询的数量
7.  【强制】周期性备份数据
8.  【建议】JOIN 查询时，小表在右，先把右表加载到内存，再去一一匹配左表
9.  【建议】适当调大后台 merge 线程数 background\_pool\_size
10. 【建议】数据规模在 TB 级别，建议使用 SSD（Solid State Drive） 磁盘

Not to do

【强制】生产环境禁止使用 SELECT *命令
【强制】生产环境禁止小批次写入数据
【建议】建表时能用数值型或日期时间型表示的字段就不要用字符串

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

【建议】不要空值存储

存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）

【建议】写入数据中不宜包含过多分区
【建议】写入数据不可乱序，或者写入太快

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

【建议】当多表联查时，查询的数据仅从其中一张表出时，可考虑用IN操作而不是JOIN
【建议】减少参与 JOIN 运算的数据量 (无谓词下推)

五、监控告警

监控告警架构

Prometheus + Clickhouse metrics_port + Grafana方式进行监控，并对核心指标提供监控大屏及告警通知。

监控屏地址

登录grafana找到ID为14192的面板

监控屏注解

大屏面板	面板参数	参数/面板说明
Nodes面板	instance	机器实例共4台
status	clickhouse运行状态
basedata Num	clickhouse实例中数据库的数量
Num of Tables	clickhouse实例中表的数量
Versioninteger	版本号
Curd_Queries面板	-	clickhouse包含增删改查操作请求速率均值
Memory面板	-	clickhouse实例下的内存占用数值
TCP connect面板	-	clickhouse实例下的TCP连接数
Ck_Query面板	-	采集时间点运行的Query数量
global active thread面板	-	clickhouse实例全局活跃线程数
Delayed_Insert面板	-	由于写入负载过高，延迟写入的Block数量
Readonly tables面板	-	clickhouse实例中当前处于只读状态的表个数
Http connect面板	-	clickhouse实例下的http连接数
Inserted_Bytes面板	-	所有表中插入的字节数，每秒的数据均值
Selected_Bytes面板	-	所有表中查询的字节数，每秒的数据均值

阈值标记

面板	metric_key（指标标识）	metric_desc（指标描述）	采集间隔	告警规则	告警分级	指标分类(发现问题指标、定位问题指标、优化指标)	监控场景
Delayed_Insert面板	ClickHouseMetrics_DelayedInserts	由于写入负载过高，延迟写入的Block数量	15s	>2	Critical	发现问题指标定位问题指标	监控延迟写入的Block数量
global active thread面板	ClickHouseMetrics_GlobalThreadActive	clickhouse 全局活跃线程数	15s	<50	Critical	发现问题指标	监控活跃线程数
Http connect面板	ClickHouseMetrics_HTTPConnection	clickhouse 当前http连接数	15s	>200	warning	发现问题指标	监控连接数
Memory面板	ClickHouseMetrics_MemoryTracking	当前时刻分配给server的内存总数	15s	>25GiB	Critical	发现问题指标优化指标	监控内存指标
Readonly tables面板	ClickHouseMetrics_ReadonlyReplica	ck中当前处于只读状态的表个数	15s	>50	Critical	发现问题指标优化指标	监控表状态
Inserted_Bytes面板	ClickHouseProfileEvents_InsertedBytes	单位时间内所有表中插入的字节数，每秒的数据均值	15s	>700Mib/s	warning	发现问题指标	监控插入数据量
Selected_Bytes面板	ClickHouseProfileEvents_SelectedBytes	单位时间内所有表中插入的字节数，每秒的数据均值	15s	>700Mib/s	warning	发现问题指标	监控查询数据量
TCP connect面板	ClickHouseMetrics_TCPConnection	clickhouse 的tcp连接数	15s	>200	warning	发现问题指标	监控连接数
Ck_Query面板	ClickHouseMetrics_Query	采集时间点运行的Query的数量	15s	>50	Critical	发现问题指标	监控瞬时并发

metrics端口

配置文件： /etc/clickhouse-server/config.xml

<prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
        <status_info>true</status_info>
        <errors>true</errors>
    </prometheus>

端口： 9363

路由： /metrics

附录

基本数据类型

数据类型
Int8
Int16
Int32
UInt32
UInt64
Float32
Float64
Decimal
Date
Date Time
Date Time64
Enum
String
FixedString

数据类型
Int8
Int16
Int32
UInt32
UInt64
Float32
Float64
Decimal
Date
Date Time
Date Time64
Enum
String
FixedString

命令清单

详细命令参考：clickhouse.com/docs/zh/sql…

类别	含义	命令	官方链接
clickhouse实例	启动服务	systemctl start clickhouse-server
查看服务状态	systemctl status clickhouse-server
重启服务	systemctl restart clickhouse-server
停止服务	systemctl stop clickhouse-server
ClickHouse Client	交互模式	clickhouse-client --host=... --port=... --user=... --password=...	clickhouse.com/docs/zh/int…
启用多行查询	clickhouse-client -m
以批处理模式运行查询	clickhouse-client --query='SELECT 1'echo 'SELECT 1'	clickhouse-clientclickhouse-client <<< 'SELECT 1'
从指定格式的文件中插入数据	clickhouse-client --query='INSERT INTO table VALUES' < data.txtclickhouse-client --query='INSERT INTO table FORMAT TabSeparated' < data.tsv
数据表基本操作	追加新字段	ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after];	clickhouse.com/docs/zh/sql…
修改字段类型	ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr];
修改备注	ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment';
删除已有字段	ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name;
修改数据表名称	RENAME TABLE old_name TO new_name;	clickhouse.com/docs/zh/sql…
分区操作	查询分区信息	SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_xx';
删除指定分区	ALTER TABLE tb_name DROP PARTITION partition_expr;	clickhouse.com/docs/zh/sql…

clickhouse日常使用规范

概述

一、Clickhouse介绍

Clickhouse简介

特性

⼆、技术规范

版本规范

架构规范

分片、副本规范

路径要求规范

备份要求规范

使用表引擎规范

创建帐号规范

三、资源规格

套餐（性能压测）

四、使用规范

JDBC最佳实践

应用约束

五、监控告警

监控告警架构

监控屏地址

监控屏注解

阈值标记

metrics端口

附录

基本数据类型

命令清单