ck相关排查方法

1.1.ck查询近一月每天各种状态SQL条数有多少

select
  date,
  arrayStringConcat(arrayMap((x,y) -> concat('{', x, ':' ,toString(y), '}'), groupArray(kind), groupArray(number)), ',') as value 
from
(
  select
    event_date as date,
    if(empty(query_kind), 'Others', query_kind) as kind,
    count() as number
  from
    cluster(cluster1, system.query_log)
  where
    type='QueryStart' and 
    event_date>=(now()-toIntervalMonth(1)) and
    not hasAny(databases, ['system', 'default', 'INFORMATION_SCHEMA', 'information_schema']) 
    and is_initial_query
  group by
    event_date,query_kind
  order by event_date,query_kind
)
group by date;

2..查询过去某段时间cpu消耗情况

--1.过去某段时间CPU消耗情况：
-- 节点,优先采用CPU
select
  event_time,
  bar(value, 0, 100, 100)
from
  system.asynchronous_metric_log
where
  event_date=today() and
  event_time>='2025-07-09 06:00:00' and
  event_time<='2025-07-09 18:00:00' and
  metric like 'OSUserTime' 
order by metric,event_time;

3.ck并发

select
    hostname() as host,
    event_time,
    CurrentMetric_Query,
    ProfileEvent_Query,
    ProfileEvent_SelectQuery,
    ProfileEvent_InsertQuery
from
  cluster(cluster1,system.metric_log)
where
  event_date=today() and
  event_time>'2025-07-09 00:00:01' and event_time<'2025-07-09 18:00:00'
  and CurrentMetric_Query >= 50
order by host, event_time;

4.查询某天小时级别的各种状态SQL条数

SELECT
    hour,
    arrayStringConcat(arrayMap((x, y) -> concat('{', x, ':', toString(y), '}'),
                      groupArray(kind),
                      groupArray(number)), ',') AS hourly_stats
FROM
(
    SELECT
        toStartOfHour(event_time) AS hour,  
        if(empty(query_kind), 'Others', query_kind) AS kind,
        count() AS number
    FROM
        cluster(cluster1, system.query_log)
    WHERE
        type = 'QueryStart' AND
        event_date = '2025-07-09' AND
        not hasAny(databases, ['system', 'default', 'INFORMATION_SCHEMA', 'information_schema']) AND
        is_initial_query
    GROUP BY
        hour,  
        query_kind
    ORDER BY
        hour,
        query_kind
)
GROUP BY
    hour
ORDER BY
    hour ASC;

5.查询耗时前10的SQL

 select
    event_time,
    query_id,
    query_duration_ms,
    formatReadableSize(read_bytes) as bytes,
    memory_usage,
    query,
    tables[1]
  from 
    cluster(cluster1, system.query_log)
  where
    type='QueryFinish' and
    event_date=today() and
    event_time>= '2025-07-09 00:00:01' and
    event_time<= '2025-07-09 20:05:00' and
    query_kind in('Select') and
    not hasAny(databases, ['system', 'default', 'INFORMATION_SCHEMA', 'information_schema']) and
    is_initial_query
  order by query_duration_ms desc limit 20;