clickhouse query_log 常用查询语句

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

SELECT*
FROMsystem.query_log
WHEREquery_duration_ms > 30000AND event_time >= '2024-12-31 15:50:00' AND event_time <= '2024-12-31 17:50:00'
ORDER BYevent_time desc;

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

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

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

selecttoStartOfMinute (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'
whereevent_time > '2024-12-31 15:55:00'and event_time < '2024-12-31 16:01:00'and is_initial_query = 1and user not in('default', 'aurora')and type in ('QueryFinish', 'ExceptionWhileProcessing')and query_kind = 'Select'
group bytime
order bycnt;

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

selecttoStartOfMinute (query_start_time) as time,query_kind,count()
from
clusterAllReplicas (default, system.query_log)
wherequery_start_time > '2024-12-31 15:30:00'and query_start_time < '2024-12-31 16:10:00'
group bytime,query_kind
order bytime
limit50

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

selecttoStartOfMinute (query_start_time) as time,avg(query_duration_ms) as a
fromclusterAllReplicas (default, system.query_log)
wherequery_start_time > '2024-12-31 15:30:00'and query_start_time < '2024-12-31 16:10:00'and normalized_query_hash = '808563827218856330'
group bytime
order bya desc
limit50

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

selectsubstring(hostname (), 38, 8) as host,type,query_duration_ms
fromclusterAllReplicas (default, system.query_log)
wherequery_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'
limit50

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

selectsubstring(hostname (), 38, 8) as host,type,query_duration_ms
fromclusterAllReplicas (default, system.query_log)
wherequery_start_time > '2024-12-31 15:59:00'and query_start_time < '2024-12-31 16:01:00'and is_initial_query = 0and query_kind = 'Select'
order byquery_duration_ms desc
limit50

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

selectsubstring(hostname (), 38, 8) as host,type,query_duration_ms
fromclusterAllReplicas (default, system.query_log)
wherequery_start_time > '2024-12-31 15:59:00'and query_start_time < '2024-12-31 16:01:00'and is_initial_query = 1and user != 'default'
order byquery_duration_ms desc
limit50

查看慢日志的时间分布。

select*
from(selecttoStartOfMinute (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 cntfromclusterAllReplicas (default, system.query_log)whereevent_time >= '2024-12-31 12:45:00'and event_time < '2025-01-01 12:00:00'and is_initial_query = 1and has (databases, 'system') = 0and type in (2, 3)and query_kind = 'Select'group bytime)
order bytime;