ToB企服应用市场:ToB评测及商务社交产业平台

标题: clickhouse query_log 常用查询语句 [打印本页]
作者: 滴水恩情    时间: 2025-1-7 02:13
标题: 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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4