首页 > 其他分享 >什么?部署ClickHouse的服务器CPU利用率100%了?

什么?部署ClickHouse的服务器CPU利用率100%了?

时间:2024-05-28 14:31:40浏览次数:33  
标签:event 00 read 100% 查询 time query CPU ClickHouse

背景

    某客户现场的ClickHouse所在服务器资源占用率100%了,引发了服务器告警。观察Grafana监控面板发现,从12点左右出现了大量的碎片写入,从而引起了相关指标的快速上升。

    本文主要通过ClickHouse官方的系统表system.query_log表进行问题排查定位,结合Grafana监控面板最终定位到问题根本原因。

最近写入sql执行是否有异常,判断是否是因为批量的数据写入导致的CPU利用率突增

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('cluster_name', system, query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
ORDER BY event_time DESC  
LIMIT 100

昨天有没有大于5分钟的慢查询

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('cluster_name', system, query_log)  
WHERE (event_date = yesterday()) AND query_duration_ms > 30000 AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
ORDER BY query_duration_ms desc  
LIMIT 100

磁盘占用最高的前10张表

SELECT   
    database,   
    table,   
    sum(bytes_on_disk) AS bytes_on_disk  
FROM clusterAllReplicas('cluster_name', system, parts)  
WHERE active AND (database != 'system')  
GROUP BY   
    database,   
    table  
ORDER BY bytes_on_disk DESC  
LIMIT 10

查询频率前10的用户

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('cluster_name', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
GROUP BY user  
ORDER BY query_times DESC  
LIMIT 10

统计SQL 查询次数,判断哪次查询时间最长以及查询的平均时长

select
	left(query,
	100) as sql,
	count() as queryNum,
	sum(query_duration_ms) as totalTime,
	totalTime / queryNum as avgTime
from
	system.query_log ql
where
	event_time > toDateTime('2024-05-20 12:00:00')
	and event_time < toDateTime('2024-05-20 17:00:00')
group by
	sql
order by
	queryNum desc
limit 10

查询不包含insert into语句的5个小时查询次数超过1000次的 SQL

select
	*
from
	(
	select
		LEFT(query,
		100) as sql,
		count() as quneryNum,
		sum(query_duration_ms) as totalTime,
		totalTime / queryNum as avgTime
	from
		system.query_log ql
	where
		event_time > toDateTime('2024-05-20 12:00:00')
		and event_time < toDateTime('2024-05-20 17:00:00')
		and query not like '%INSERT INTO%'
	group by
		sql
	order by
		avgTime desc)
where
	queryNum > 1000
limit 50

由于上述 SQL均做了截取,故需根据所查询 SQL 进一步匹配 SQL。

select
	query
from
	system.query_log
where
	event_time > toDateTime('2024-05-20 12:00:00')
	and event_time < toDateTime('2024-05-20 17:00:00')
	and query like '%需要匹配的sql查询%'
limit 5;

是否有left join查询,如果大表进行left join查询很可能导致CPU过高

select
	*
from
	(
	select
		LEFT(query,100) as sql,
		count() as quneryNum,
		sum(query_duration_ms) as totalTime,
		totalTime / queryNum as avgTime
	from
		system.query_log ql
	where
		sql like '%前面定位到的sql的信息%'
		and read_rows != 0
		and event_time > toDateTime('2024-05-20 12:00:00')
		and event_time < toDateTime('2024-05-20 17:00:00')
		and query not like '%INSERT INTO%'
	group by
		sql
	order by
		queryNum desc)

根据小时聚合每个小时查询次数耗时

select
	toHour(event_time) as t,
	count() as queryNum,
	sum(query_duration_ms) as totalTime,
	totalTime / queryNum as avgTime
from
	system.query_log ql
where
	event_time > toDateTime('2024-05-20 08:00:00')
	and event_time < toDateTime('2024-05-20 17:00:00')
	and query not like '%INSERT INTO%'
	and query like '%前面定位到的sql的信息%'
	and read_rows != 0
group by
	t
limit 50

根据小时聚合每个分钟查询次数耗时

select
	toMinute(event_time) as t,
	count() as queryNum,
	sum(query_duration_ms) as totalTime,
	totalTime / queryNum as avgTime
from
	system.query_log ql
where
	event_time > toDateTime('2024-05-20 12:00:00')
	and event_time < toDateTime('2024-05-20 13:00:00')
	and query not like '%INSERT INTO%'
	and query like '%前面定位到的sql的信息%'
	and read_rows != 0
group by
	t
limit 50

left join查询个数

select
	*
from
	(
	select
		LEFT(query,100) as sql,
		count() as quneryNum,
		sum(query_duration_ms) as totalTime,
		totalTime / queryNum as avgTime
	from
		system.query_log ql
	where
		query like '% JOIN%'
		and read_rows != 0
		and event_time > toDateTime('2024-05-20 12:00:00')
		and event_time < toDateTime('2024-05-20 21:00:00')
		and query not like '%INSERT INTO%'
	group by
		sql
	order by
		queryNum desc)

发现有问题的表时,查询该表结构

show create table "shard1"."xxx_replica"

总结

遇到此类问题可先查看日志,首先在(Clickhouse 日志 Zookeeper 日志)日志中看能否找到有用的信息,例如直接报错信息等,如果在日志中找不到太多有用的信息的话,可以从下面入手。

一般遇到 CPU的load 值比较高的情况时,基本上都是因为查询引起的。当遇到这种问题时可先查询带有JOIN 的 SQL 语句是不是很多。

通过Grafana等监控工具,快速定位问题发生的时间段。

通过查询query_log表中的执行记录,分析是否有大查询、慢查询,找到具体的sql,条件允许的情况下可以停止大查询观察CPU的load值是否降低。(kill掉相关sql,KILL QUERY WHERE query_id='')

本次排查过程主要使用query_log表,下面为重要字段:

event_time — 查询开始时间.

query_duration_ms — 查询消耗的时间(毫秒).

read_rows— 从参与了查询的所有表和表函数读取的总行数.

query — 查询语句.

Clickhouse query_log 表中所有字段

  • type (Enum8) — 执行查询时的事件类型. 值:

    • 'QueryStart' = 1 — 查询成功启动.

    • 'QueryFinish' = 2 — 查询成功完成.

    • 'ExceptionBeforeStart' = 3 — 查询执行前有异常.

    • 'ExceptionWhileProcessing' = 4 — 查询执行期间有异常.

  • event_date (Date) — 查询开始日期.

  • event_time (DateTime) — 查询开始时间.

  • event_time_microseconds (DateTime64) — 查询开始时间(毫秒精度).

  • query_start_time (DateTime) — 查询执行的开始时间.

  • query_start_time_microseconds (DateTime64) — 查询执行的开始时间(毫秒精度).

  • query_duration_ms (UInt64) — 查询消耗的时间(毫秒).

  • read_rows (UInt64) — 从参与了查询的所有表和表函数读取的总行数. 包括:普通的子查询, INJOIN的子查询. 对于分布式查询 read_rows 包括在所有副本上读取的行总数。 每个副本发送它的 read_rows 值,并且查询的服务器-发起方汇总所有接收到的和本地的值。 缓存卷不会影响此值。

  • read_bytes (UInt64) — 从参与了查询的所有表和表函数读取的总字节数. 包括:普通的子查询, INJOIN的子查询. 对于分布式查询 read_bytes 包括在所有副本上读取的字节总数。 每个副本发送它的 read_bytes 值,并且查询的服务器-发起方汇总所有接收到的和本地的值。 缓存卷不会影响此值。

  • written_rows (UInt64) — 对于 INSERT 查询,为写入的行数。 对于其他查询,值为0。

  • written_bytes (UInt64) — 对于 INSERT 查询时,为写入的字节数。 对于其他查询,值为0。

  • result_rows (UInt64) — SELECT 查询结果的行数,或INSERT 的行数。

  • result_bytes (UInt64) — 存储查询结果的RAM量.

  • memory_usage (UInt64) — 查询使用的内存.

  • query (String) — 查询语句.

  • exception (String) — 异常信息.

  • exception_code (Int32) — 异常码.

  • stack_trace (String) — Stack Trace. 如果查询成功完成,则为空字符串。

  • is_initial_query (UInt8) — 查询类型. 可能的值:

    • 1 — 客户端发起的查询.

    • 0 — 由另一个查询发起的,作为分布式查询的一部分.

  • user (String) — 发起查询的用户.

  • query_id (String) — 查询ID.

  • address (IPv6) — 发起查询的客户端IP地址.

  • port (UInt16) — 发起查询的客户端端口.

  • initial_user (String) — 初始查询的用户名(用于分布式查询执行).

  • initial_query_id (String) — 运行初始查询的ID(用于分布式查询执行).

  • initial_address (IPv6) — 运行父查询的IP地址.

  • initial_port (UInt16) — 发起父查询的客户端端口.

  • interface (UInt8) — 发起查询的接口. 可能的值:

    • 1 — TCP.

    • 2 — HTTP.

  • os_user (String) — 运行 clickhouse-client的操作系统用户名.

  • client_hostname (String) — 运行clickhouse-client 或其他TCP客户端的机器的主机名。

  • client_name (String) — clickhouse-client 或其他TCP客户端的名称。

  • client_revision (UInt32) — clickhouse-client 或其他TCP客户端的Revision。

  • client_version_major (UInt32) — clickhouse-client 或其他TCP客户端的Major version。

  • client_version_minor (UInt32) — clickhouse-client 或其他TCP客户端的Minor version。

  • client_version_patch (UInt32) — clickhouse-client 或其他TCP客户端的Patch component。

  • http_method (UInt8) — 发起查询的HTTP方法. 可能值:

    • 0 — TCP接口的查询.

    • 1 — GET

    • 2 — POST

  • http_user_agent (String) — The UserAgent The UserAgent header passed in the HTTP request。

  • quota_key (String) — 在quotas 配置里设置的“quota key” (见 keyed).

  • revision (UInt32) — ClickHouse revision.

  • ProfileEvents (Map(String, UInt64))) — Counters that measure different metrics. The description of them could be found in the table 系统。活动

  • Settings (Map(String, String)) — Names of settings that were changed when the client ran the query. To enable logging changes to settings, set the log_query_settings 参数为1。

  • thread_ids (Array(UInt64)) — 参与查询的线程数.

  • Settings.Names (Array(String)) — 客户端运行查询时更改的设置的名称。 要启用对设置的日志记录更改,请将log_query_settings参数设置为1。

  • Settings.Values (Array(String)) — Settings.Names 列中列出的设置的值

标签:event,00,read,100%,查询,time,query,CPU,ClickHouse
From: https://blog.csdn.net/zcs_978176963/article/details/139066538

相关文章

  • AP5101C高压线性LED恒流驱动芯片 6-100V 2A LED灯电源驱动
    产品描述AP5101C是一款高压线性LED恒流芯片,简单、内置功率管,适用于6-100V输入的高精度降压LED恒流驱动芯片。电流2.0A。AP5101C可实现内置MOS做2.0A,外置MOS可做3.0A的。AP5101C内置温度保护功能,温度保护点为130度,温度达到130度时,输出电流慢慢减小,达到保护芯片电......
  • SQLServer如何查询近3分钟最消耗CPU的SQL
    在SQLServer中,要查询近3分钟最消耗CPU的SQL语句,可以使用sys.dm_exec_query_stats动态管理视图结合sys.dm_exec_sql_text函数来获取SQL语句的文本。不过,直接查询近3分钟的数据可能需要一些额外的逻辑来筛选时间范围,因为sys.dm_exec_query_stats并不直接提供时间筛选的功能。一种......
  • CPU温度告警
    CPU温度阈值CPU温度有3档阈值(TCC\PROCHOT\ThermalTrip),HDM中的sensor是CPUDTS,如CPUDTS=0对应的是TCC告警。ThermalControlCircuit,TCC:轻微,温度控制电路PROCHOT:紧急,处理器过热ThermalTrip:极限温度,热跳闸(1)TCC不会影响CPU频率,也不会触发关机;但HDM会出现警报提示(2)如果C......
  • MySQL中如何快速定位占用CPU过高的SQL
    作为DBA工作中都会遇到过数据库服务器CPU飙升的场景,我们该如何快速定位问题?又该如何快速找到具体是哪个SQL引发的CPU异常呢?下面我们说两个方法。聊聊MySQL中如何快速定位占用CPU过高的SQL。技术人人都可以磨炼,但处理问题的思路和角度各有不同,希望这篇文章可以抛砖引玉。 以一......
  • DB Link导致SCN Headroom以及2012年1月的CPU或PSU补丁问题研究
    转自:https://www.cnblogs.com/dc-chen/p/7245290.htmlhttps://www.laoxiong.net/scn-ora-19706-_external_scn_rejection_threshold_hours-parameter.htmlhttps://www.modb.pro/db/4664https://www.iteye.com/blog/tianmaotalk-2437997一、基础概念1、SCN(SystemChangeNumb......
  • AP2917双路输出降压恒流驱动IC 5-100V 12W 摩托车灯照明IC
    AP2917是一款可以一路灯串切换两路灯串的降压恒流驱动器,高效率、外围简单、内置功率管,适用于5-100V输入的高精度降压LED恒流驱动芯片。内置功率管输出最大功率可达12W,最大电流1.2A。AP2917一路灯亮切换两路灯亮,其中一路灯亮可以全亮,可以半亮。AP2917工作频率固定在......
  • 阿里面试:NIO为什么会导致CPU100%?
    在Java中总共有三种IO类型:BIO(BlockingI/O,阻塞I/O)、NIO(Non-blockingI/O,非阻塞I/O)和AIO(AsynchronousI/O,异步I/O),它们的区别如下:在JDK1.4之前,只有BIO一种模式,其开发过程相对简单,新来一个连接就会创建一个新的线程处理,但随着请求并发度的提升,BIO很快遇到了性能瓶颈。......
  • Java 进程 CPU 占用过高问题排查
    1.Java进程CPU占用过高问题排查1.1.运行环境1.2.定位CPU占用高的进程1.3.定位CPU占用高的线程1.4.将线程ID转换为十六进制1.5.找到线程对应的栈信息1.5.1.使用jstack1.5.2.使用jcmd1.5.3.使用arthas1.5.4.使用jattach1.Java进程CPU......
  • LeetCode刷题之HOT100之两数相加
    2024/5/27大家早上好呀,昨晚没睡好,四个小时不到,估计是太兴奋了。昨天去长乐十七孔、下沙赶海啦。远看沙滩上的人群就像一根根木桩矗立在浅滩上,走近些,才发现都佝偻着腰,两只手在沙地淘金(摸花蛤)。放几张图图一、十七孔水库附近图二、十七孔——右侧礁石是妈祖像图三、追......
  • 100313. 所有球里面不同颜色的数目
    题目描述给你一个整数limit和一个大小为nx2的二维数组queries。总共有limit+1个球,每个球的编号为[0,limit]中一个互不相同的数字。一开始,所有球都没有颜色。queries中每次操作的格式为[x,y],你需要将球x染上颜色y。每次操作之后,你需要求出所有球中不同......