【数据库维护】Clickhouse数据库维护关键系统表相关指标说明，支撑定位慢SQL及多实例场景下分析各实例运行情况

背景

当前使用环境上以Docker容器化部署Clickhouse服务8个实例，但在实际运行过程中，发现8个实例内存负载不均衡，ck-0实例在固定时间段内存会直线上升，直至服务报错memory exceeded max memory limit。
为排查ck-0节点内存直线上升阶段，对应实例执行任务情况等，梳理clickhouse系统库相关表关键指标说明。

system系统库关键表说明

注意点：system系统库各表数据分别存储在各实例上，各实例情况统计须查询各实例具体情况。
即：节点一的运行指标/merge进程/insert进程/查询进程等，须登录实例一查看。

system.metrics表

记录当前实例运行过程各项关键指标数据，可以支撑分析问题发生情况下各指标趋势。
表字段说明

字段名称	字段说明
metric	指标名称
value	指标值
description	指标说明
name	指标别名

关键指标说明

指标名称	指标说明
Query	正在执行的查询数量
Merge	正在执行的merge数量
TCPConnection	TCP连接数量
HTTPConnection	HTTP连接数量
PartsActive	活跃的数据分块，小而多的数据库会对性能有影响
AddressesActive	正在连接的客户端IP地址数量

查询方式

查询所有可用指标

select * from system.metrics;

查询特定指标的值
如查询所有Connection相关的指标

select metric,value from system.metrics where metric like '%Connection%';

system.processes表

记录当前实例正在运行的查询/插入等操作，与show processlist 命令查询结果一致。
表关键字段说明

字段名称	字段说明
user	执行本次操作的数据库用户
address	执行本次操作的客户端IP地址，IPV6格式，K8S场景下为实例IP
elapsed	操作耗时，秒
read_rows	读取行数
read_bytes	读取字节数
written_rows	读取行数
written_bytes	读取字节数
memory_usage	内存使用情况，字节
query	执行的具体脚本，insert脚本不包含数据部分
is_cancelled	是否取消查询
is_all_data_sent	所有数据是否已发送给客户端，可用于判断服务端查询是否已完成

查询方式

关注当前实例正在运行的查询个数，可通过count计数

select count(*) from system.processes;

关注当前实例内存占用情况，可用过sum求和

select count(*) as total_process,sum(memory_usage)/1024/1024 as total_memory_Mb from system.processes;

如通过上述命令得到某实例内存占用过大，可通过下面语句查询具体详情。

select memory_usage/1024/1024 as memory_Mb,elapsed,read_rows,written_rows,address,user,query from system.processes order by memory_usage desc;

说明：

• 除以1024，是进行单位转换，方便理解
• 可以根据实际需求选择排序字段
• read_rows和written_rows两个字段，根据查询类型不同一般只有一个字段有值

system.merges

记录当前实例正在进行的merge合并操作，消耗内存的主要行为之一，将插入clcikhouse中的一个分区中的小片段数据根据系统配置阈值进行合并操作。
表关键字段说明

字段名称	字段说明
database	数据库名称
table	表名
elapsed	耗时，秒
progress	进度，0-1之间
num_parts	需要被merge的数据分块数量
result_part_name	合并后的数据分块名称，对应磁盘上一个文件
total_size_bytes_compressed	压缩数据总大小
rows_read	读取行数
bytes_read_uncompressed	读取未压缩字节大小
rows_written	写入行数
bytes_written_uncompressed	写入未压缩字节大小
memory_usage	内存占用字节
merge_type	合并类型
merge_algorithm	合并算法

查询方式

查询当前实例正在执行的merge个数，可通过count(*)查询；查询当前实例正在运行的merge进程对应的内存消耗。

select count(*), sum(memory_usage)/1024/1024 as memory_Mb from system.merges;

查询具体merge进程详情（大于10秒的）

select database,tables,memory_usage/1024/1024 as memory_Mb,elapsed,num_parts,rows_read,rows_written from system.merges where elapsed > 10 order by memory_usage desc;

说明：
可根据实际关注点，参考字段说明，自行调整sql

system.query_log

记录当前实例已经运行过的SQL信息，SQL执行开始和结束均会写入一条记录，可以协助定位慢SQL等相关信息。
须注意数据保留周期。
表关键字段说明

字段名称	字段说明
type	类型，包含QueryStart，QueryFinish，ExceptionBeforeStart，ExceptionWhileProcessing，一般情况下关注已执行成功记录。
event_date	开始日期，如20255-02-16
event_time	开始时间
event_time_microseconds	开始时间，带毫秒
query_duration_ms	执行耗时
read_rows	读取行数
read_bytes	读取字节数
written_rows	写入行数
written_bytes	写入字节数
result_rows	结果行数，read或者written
result_bytes	结果字节数
memory_usage	内存占用情况
query	执行的脚本
query_kind	查询的类型
databases	数据库信息
tables	表信息
partitions	分区信息，尽量一次查询/写入操作同一个分区，效率会更高
exception_code	异常编码
exception	异常信息
stack_trace	堆栈信息
is_initial_query	是否初始化查询，对应其他initial字段
user	数据库用户
address	客户端地址，即谁发起的查询
interface	TCP/HTTP

查询方式

查询指定时间段内耗时大于10秒的记录

select event_time,query_duration_ms/1000 as duration_s,query_kind,result_rows,result_bytes/1024/1024 as result_Mb,memory_usage/1024/1024 as   memory_usage_Mb,address,partitions,query,exception from system.query_log where query_duration_ms > 10000 order by memory_usage desc

说明：
具体查询字段，过滤字段以及排序字段，根据字段说明自行调整。

其他附录说明

多实例场景下批量查询方式

for number in 0 1 2 3 4 5 6 7;do echo "------------------"; ./clickhouse-client -h 实例IP或者K8S域名-$number --port 26734 -u clickhouse用户名 --password 用户名对应的密码 --query "select metric,value from system.metrics where metric like '%Connection%' "; echo "------------------"; done   

说明：

• in后面数字代表不同的实例编号，-h使用k8s域名如clickhouse-0.xxx.com，可以通过$number获取变量，实现批量查询各个节点信息
• echo打印一堆横杠，以区分不同实例打印结果
• –query参数输入待执行的SQL脚本

K8s集群场景下，根据已查询到的客户端address确认具体的实例名称

K8s容器部署场景下，已获取address情况下，期望明确具体的发起查询的服务实例，可通过以下命令查询

kubectl get pod -A -o wide | grep 实例IP

-A可以根据实际情况修改为具体的命名空间，查询范围更具体一些，当前查询所有命名空间。