How to use SQL to query write hotspots and read hotspots on the dashboard, such as identifying write skew

translator_bot · June 20, 2024, 1:50pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: tidb狂热爱好者

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

Previously, I found the SQL from the dashboard. But I couldn’t capture the SQL for the heatmap.

2. Write an automated script to inspect the production system and query the SQLs that do not use indexes and have the highest overall execution time

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;

This SQL can find the slowest SQL without indexes

Need to find the SQL in the system that is very fast individually but has a high total execution time due to excessive execution counts. Ask the developers to cache it.

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;

4. Find the slowest SQL in the last hour every day and ask the developers to fix it. Just one slowest SQL is enough, as they can’t fix too many.

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;
Is there a way to find it?