如何用sql查询dashboard上的写热点 读热点问题。比如分辨写倾斜这种

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

image
image876×214 15.2 KB

之前dashboard得sql我都找到了。但 热力图的sql没抓出来。

2.写自动化脚本巡检生产系统去查询哪些没有走索引并总体耗时最多的sql

SELECT FLOOR(UNIX_TIMESTAMP(MIN(summary_begin_time))) AS agg_begin_time, FLOOR(UNIX_TIMESTAMP(MAX(summary_end_time))) AS agg_end_time, ANY_VALUE(digest_text) AS agg_digest_text, ANY_VALUE(digest) AS agg_digest, SUM(exec_count) AS agg_exec_count, SUM(sum_latency) AS agg_sum_latency, MAX(max_latency) AS agg_max_latency, MIN(min_latency) AS agg_min_latency, CAST( SUM(exec_count * avg_latency) / SUM(exec_count) AS SIGNED ) AS agg_avg_latency, CAST( SUM(exec_count * avg_mem) / SUM(exec_count) AS SIGNED ) AS agg_avg_mem, MAX(max_mem) AS agg_max_mem, ANY_VALUE(schema_name) AS agg_schema_name, ANY_VALUE(plan_digest) AS agg_plan_digest,query_sample_text,index_names FROM `INFORMATION_SCHEMA`.`CLUSTER_STATEMENTS_SUMMARY_HISTORY` where index_names is null and query_sample_text >‘’ GROUP BY schema_name, digest ORDER BY agg_sum_latency DESC limit 10;

这个sql能查出最慢无索引sql

image.png
image.png1456×94 116 KB

3.需要找出系统中单个sql很快但执行次数过多总计最耗时间的sql。让开发放缓存。

SELECT FLOOR(UNIX_TIMESTAMP(MIN(summary_begin_time))) AS agg_begin_time, FLOOR(UNIX_TIMESTAMP(MAX(summary_end_time))) AS agg_end_time, ANY_VALUE(digest_text) AS agg_digest_text, ANY_VALUE(digest) AS agg_digest, SUM(exec_count) AS agg_exec_count, SUM(sum_latency) AS agg_sum_latency, MAX(max_latency) AS agg_max_latency, MIN(min_latency) AS agg_min_latency, CAST( SUM(exec_count * avg_latency) / SUM(exec_count) AS SIGNED ) AS agg_avg_latency, CAST( SUM(exec_count * avg_mem) / SUM(exec_count) AS SIGNED ) AS agg_avg_mem, MAX(max_mem) AS agg_max_mem, ANY_VALUE(schema_name) AS agg_schema_name, ANY_VALUE(plan_digest) AS agg_plan_digest,query_sample_text,index_names FROM `INFORMATION_SCHEMA`.`CLUSTER_STATEMENTS_SUMMARY_HISTORY` GROUP BY schema_name, digest ORDER BY agg_sum_latency DESC limit 10;

image.png
image.png1456×94 116 KB

4.每日找出前1个小时的慢sql让开发整改一次不用多给最慢的一个sql就行多了人家也改不完

select query_time,query from information_schema.CLUSTER_SLOW_QUERY where is_internal = false and Time > date_add(now(),interval -1 hour) and user<>‘root’ and query_time > 7 order by query_time desc limit 1;
有办法找到么

1 个赞
2

这种sql确实挺实用，等一个大佬答复

3

这个特别有用 :muscle:

4

:100: :100: :100:

5

看过DBA巡检也这么写

6

有个派生的疑惑，这个工作能够利用tidb的grafana实现吗?

7

:call_me_hand: 我记得个大佬实现了自动生成网页

8

Grafana可以监控到热点问题，但是不能定位到具体的SQL