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

配景

    某客户现场的ClickHouse所在服务器资源占用率100%了，引发了服务器告警。观察Grafana监控面板发现，从12点左右出现了大量的碎片写入，从而引起了相关指标的快速上升。
    本文重要通过ClickHouse官方的系统表system.query_log表进行问题排查定位，团结Grafana监控面板最终定位到问题根本原因。

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

1. SELECT   
2.     event_time,   
3.     user,   
4.     query_id AS query,   
5.     read_rows,   
6.     read_bytes,   
7.     result_rows,   
8.     result_bytes,   
9.     memory_usage,   
10.     exception  
11. FROM clusterAllReplicas('cluster_name', system, query_log)  
12. WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
13. ORDER BY event_time DESC  
14. LIMIT 100

复制代码

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

1. SELECT   
2.     event_time,   
3.     user,   
4.     query_id AS query,   
5.     read_rows,   
6.     read_bytes,   
7.     result_rows,   
8.     result_bytes,   
9.     memory_usage,   
10.     exception  
11. FROM clusterAllReplicas('cluster_name', system, query_log)  
12. WHERE (event_date = yesterday()) AND query_duration_ms > 30000 AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
13. ORDER BY query_duration_ms desc  
14. LIMIT 100

复制代码

磁盘占用最高的前10张表

1. SELECT   
2.     database,   
3.     table,   
4.     sum(bytes_on_disk) AS bytes_on_disk  
5. FROM clusterAllReplicas('cluster_name', system, parts)  
6. WHERE active AND (database != 'system')  
7. GROUP BY   
8.     database,   
9.     table  
10. ORDER BY bytes_on_disk DESC  
11. LIMIT 10

复制代码

查询频率前10的用户

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

复制代码

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

1. select
2.         left(query,
3.         100) as sql,
4.         count() as queryNum,
5.         sum(query_duration_ms) as totalTime,
6.         totalTime / queryNum as avgTime
7. from
8.         system.query_log ql
9. where
10.         event_time > toDateTime('2024-05-20 12:00:00')
11.         and event_time < toDateTime('2024-05-20 17:00:00')
12. group by
13.         sql
14. order by
15.         queryNum desc
16. limit 10

复制代码

查询不包罗insert into语句的5个小时查询次数凌驾1000次的 SQL

1. select
2.         *
3. from
4.         (
5.         select
6.                 LEFT(query,
7.                 100) as sql,
8.                 count() as quneryNum,
9.                 sum(query_duration_ms) as totalTime,
10.                 totalTime / queryNum as avgTime
11.         from
12.                 system.query_log ql
13.         where
14.                 event_time > toDateTime('2024-05-20 12:00:00')
15.                 and event_time < toDateTime('2024-05-20 17:00:00')
16.                 and query not like '%INSERT INTO%'
17.         group by
18.                 sql
19.         order by
20.                 avgTime desc)
21. where
22.         queryNum > 1000
23. limit 50

复制代码

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

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

复制代码

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

1. select
2.         *
3. from
4.         (
5.         select
6.                 LEFT(query,100) as sql,
7.                 count() as quneryNum,
8.                 sum(query_duration_ms) as totalTime,
9.                 totalTime / queryNum as avgTime
10.         from
11.                 system.query_log ql
12.         where
13.                 sql like '%前面定位到的sql的信息%'
14.                 and read_rows != 0
15.                 and event_time > toDateTime('2024-05-20 12:00:00')
16.                 and event_time < toDateTime('2024-05-20 17:00:00')
17.                 and query not like '%INSERT INTO%'
18.         group by
19.                 sql
20.         order by
21.                 queryNum desc)

复制代码

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

1. select
2.         toHour(event_time) as t,
3.         count() as queryNum,
4.         sum(query_duration_ms) as totalTime,
5.         totalTime / queryNum as avgTime
6. from
7.         system.query_log ql
8. where
9.         event_time > toDateTime('2024-05-20 08:00:00')
10.         and event_time < toDateTime('2024-05-20 17:00:00')
11.         and query not like '%INSERT INTO%'
12.         and query like '%前面定位到的sql的信息%'
13.         and read_rows != 0
14. group by
15.         t
16. limit 50

复制代码

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

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

复制代码

left join查询个数

1. select
2.         *
3. from
4.         (
5.         select
6.                 LEFT(query,100) as sql,
7.                 count() as quneryNum,
8.                 sum(query_duration_ms) as totalTime,
9.                 totalTime / queryNum as avgTime
10.         from
11.                 system.query_log ql
12.         where
13.                 query like '% JOIN%'
14.                 and read_rows != 0
15.                 and event_time > toDateTime('2024-05-20 12:00:00')
16.                 and event_time < toDateTime('2024-05-20 21:00:00')
17.                 and query not like '%INSERT INTO%'
18.         group by
19.                 sql
20.         order by
21.                 queryNum desc)

复制代码

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

1. 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) — 从参与了查询的所有表和表函数读取的总行数. 包括：普通的子查询, IN 和 JOIN的子查询. 对于分布式查询 read_rows 包括在所有副本上读取的行总数。 每个副本发送它的 read_rows 值，并且查询的服务器-发起方汇总所有吸收到的和本地的值。 缓存卷不会影响此值。
  
• read_bytes (UInt64) — 从参与了查询的所有表和表函数读取的总字节数. 包括：普通的子查询, IN 和 JOIN的子查询. 对于分布式查询 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 列中列出的设置的值
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。