clickhouse query_log 常用查询语句

1、查询一段时间耗时超过3秒的语句。

SELECT
  *
FROM
  system.query_log
WHERE
  query_duration_ms > 30000
  AND event_time >= '2024-12-31 15:50:00' 
   AND event_time <= '2024-12-31 17:50:00'
ORDER BY
  event_time desc;

2、查询一段时间报错的语句

SELECT
  *
FROM
  system.query_log
WHERE
exception !=''
  AND event_time >= '2024-12-31 15:50:00' -- 只查看过去一周的数据
    AND event_time <= '2024-12-31 17:50:00'
ORDER BY
  event_time desc;

3、查询某一段时间内指定sql类型的执行次数分布。

select
  toStartOfMinute (query_start_time) as time,
  count() as cnt
from
clusterAllReplicas(default, system.query_log) #这个dms 平台执行不了，需要命令行执行。
# clickhouse-client --host=xxxx.clickhouse.ads.aliyuncs.com --port=3306 --user=root --password='xxx'
where
  event_time > '2024-12-31 15:55:00'
  and event_time < '2024-12-31 16:01:00'
  and is_initial_query = 1
  and user not in('default', 'aurora')
  and type in ('QueryFinish', 'ExceptionWhileProcessing')
  and query_kind = 'Select'
group by
  time
order by
  cnt;

查看某一段时间内sql执行类型的分布。

select
  toStartOfMinute (query_start_time) as time,
  query_kind,
  count()
from
clusterAllReplicas (default, system.query_log)
where
  query_start_time > '2024-12-31 15:30:00'
  and query_start_time < '2024-12-31 16:10:00'
group by
  time,
  query_kind
order by
  time
limit
  50

查看某一类型语句指定时间内平均执行时间

select
  toStartOfMinute (query_start_time) as time,
  avg(query_duration_ms) as a
from
  clusterAllReplicas (default, system.query_log)
where
  query_start_time > '2024-12-31 15:30:00'
  and query_start_time < '2024-12-31 16:10:00'
  and normalized_query_hash = '808563827218856330'
group by
  time
order by
  a desc
limit
  50

查看相同语句再不同节点的执行速度。

select
  substring(hostname (), 38, 8) as host,
  type,
  query_duration_ms
from
  clusterAllReplicas (default, system.query_log)
where
  query_start_time > '2024-12-31 15:30:00'
  and query_start_time < '2024-12-31 16:10:00'
  and initial_query_id = 'c9f00929-1e12-4aff-bda3-0370d1a1ba0f'
  and type = 'QueryFinish'
limit
  50

查看查询慢sql排名靠前的执行节点。

select
  substring(hostname (), 38, 8) as host,
  type,
  query_duration_ms
from
  clusterAllReplicas (default, system.query_log)
where
  query_start_time > '2024-12-31 15:59:00'
  and query_start_time < '2024-12-31 16:01:00'
  and is_initial_query = 0
  and query_kind = 'Select'
order by
  query_duration_ms desc
limit
  50

查看排名靠前的所有类型慢日志节点分布， user != ‘default’

select
  substring(hostname (), 38, 8) as host,
  type,
  query_duration_ms
from
  clusterAllReplicas (default, system.query_log)
where
  query_start_time > '2024-12-31 15:59:00'
  and query_start_time < '2024-12-31 16:01:00'
  and is_initial_query = 1
  and user != 'default'
order by
  query_duration_ms desc
limit
  50

查看慢日志的时间分布。

select
  *
from
  (
    select
      toStartOfMinute (query_start_time) as time,
      avg(query_duration_ms) as query_duration_ms_avg,
      quantile (0.5) (query_duration_ms) AS query_duration_ms_p50,
      quantile (0.95) (query_duration_ms) AS query_duration_ms_p95,
      quantile (0.99) (query_duration_ms) AS query_duration_ms_p99,
      count() as cnt
    from
      clusterAllReplicas (default, system.query_log)
    where
      event_time >= '2024-12-31 12:45:00'
      and event_time < '2025-01-01 12:00:00'
      and is_initial_query = 1
      and has (databases, 'system') = 0
      and type in (2, 3)
      and query_kind = 'Select'
    group by
      time
  )
order by
  time;