clickhouse query_log 常用查询语句

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

1. SELECT
2.   *
3. FROM
4.   system.query_log
5. WHERE
6.   query_duration_ms > 30000
7.   AND event_time >= '2024-12-31 15:50:00'
8.    AND event_time <= '2024-12-31 17:50:00'
9. ORDER BY
10.   event_time desc;

复制代码

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

1. SELECT
2.   *
3. FROM
4.   system.query_log
5. WHERE
6. exception !=''
7.   AND event_time >= '2024-12-31 15:50:00' -- 只查看过去一周的数据
8.     AND event_time <= '2024-12-31 17:50:00'
9. ORDER BY
10.   event_time desc;

复制代码

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

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

复制代码

检察某一段时间内sql执行范例的分布。

1. select
2.   toStartOfMinute (query_start_time) as time,
3.   query_kind,
4.   count()
5. from
6. clusterAllReplicas (default, system.query_log)
7. where
8.   query_start_time > '2024-12-31 15:30:00'
9.   and query_start_time < '2024-12-31 16:10:00'
10. group by
11.   time,
12.   query_kind
13. order by
14.   time
15. limit
16.   50

复制代码

检察某一范例语句指定时间内平均执行时间

1. select
2.   toStartOfMinute (query_start_time) as time,
3.   avg(query_duration_ms) as a
4. from
5.   clusterAllReplicas (default, system.query_log)
6. where
7.   query_start_time > '2024-12-31 15:30:00'
8.   and query_start_time < '2024-12-31 16:10:00'
9.   and normalized_query_hash = '808563827218856330'
10. group by
11.   time
12. order by
13.   a desc
14. limit
15.   50

复制代码

检察雷同语句再不同节点的执行速率。

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

复制代码

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

1. select
2.   substring(hostname (), 38, 8) as host,
3.   type,
4.   query_duration_ms
5. from
6.   clusterAllReplicas (default, system.query_log)
7. where
8.   query_start_time > '2024-12-31 15:59:00'
9.   and query_start_time < '2024-12-31 16:01:00'
10.   and is_initial_query = 0
11.   and query_kind = 'Select'
12. order by
13.   query_duration_ms desc
14. limit
15.   50

复制代码

检察排名靠前的所有范例慢日志节点分布， user != ‘default’

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

复制代码

检察慢日志的时间分布。

1. select
2.   *
3. from
4.   (
5.     select
6.       toStartOfMinute (query_start_time) as time,
7.       avg(query_duration_ms) as query_duration_ms_avg,
8.       quantile (0.5) (query_duration_ms) AS query_duration_ms_p50,
9.       quantile (0.95) (query_duration_ms) AS query_duration_ms_p95,
10.       quantile (0.99) (query_duration_ms) AS query_duration_ms_p99,
11.       count() as cnt
12.     from
13.       clusterAllReplicas (default, system.query_log)
14.     where
15.       event_time >= '2024-12-31 12:45:00'
16.       and event_time < '2025-01-01 12:00:00'
17.       and is_initial_query = 1
18.       and has (databases, 'system') = 0
19.       and type in (2, 3)
20.       and query_kind = 'Select'
21.     group by
22.       time
23.   )
24. order by
25.   time;

复制代码

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