TiDB - Meaning of Slow Query Monitoring Metrics

Translated
1

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

Original topic: TiDB- 慢查询监控指标含义

| username: 超7成网友

I see the slow query monitoring interface of TiDB (using Prometheus and Grafana)
TiDB - query summary - slow query
This panel has three metrics: all_proc, all_cop_proc, and all_cop_wait.
I know that cop_proc and cop_wait are the task processing time and waiting time, so what does the first all_proc and the prefix all_xxx represent?

2
| username: wangccsy | Original post link

I’ll study it.

3
| username: Kongdom | Original post link

:thinking: SQL is executed in a distributed and parallel manner, and “all” may represent the sum of the execution times of each node.

4
| username: 这里介绍不了我 | Original post link

Literal meaning? All the time.

5
| username: 江湖故人 | Original post link

Could it be corresponding to these three times?

select TIME_FORMAT(time,'%H:%i:%s') t,count(*) cnt,avg(query_time) query_time ,avg(process_time) process_time,avg(wait_time) wait_time 
from information_schema.slow_query
where time>date'2024-01-22'
group by TIME_FORMAT(time,'%H:%i:%s') 
order by 1;
6
| username: Jiawei | Original post link

Here is my understanding:

  1. all_proc: The total time required for an SQL statement to execute from start to finish in TiDB, including computation and waiting.
  2. all_cop_proc: The sum of processing times for slow queries on all TiKV Coprocessors.
  3. all_cop_wait: The time TiDB waits for all necessary computations to complete on various TiKV nodes.

In simple terms, these metrics help determine where the current system’s issues are causing slowness, providing a direction for troubleshooting.
If all_cop_wait time is long, it may indicate network latency or high TiKV load.
If all_cop_proc time is long, then it might be necessary to consider query optimization or increasing computational resources.

7
| username: dba远航 | Original post link

All instances of the same type