How to Analyze Whether a Slow Query is Caused by TiDB or TiKV

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

Original topic: 慢查询怎么分析是tidb还是tikv的

| username: TiDBer_30hewbUu

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.1.1
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

SHOW TABLE STATUS LIKE 'table';
```**Bold Text**
	id                  	task	estRows	operator info                                                                                                                                                                                                                            	actRows	execution info                      	memory  	disk
	Sort_5              	root	8000   	Column#3                                                                                                                                                                                                                                 	85     	time:805.5ms, loops:2               	24.8 KB 	0 Bytes
	└─Projection_7      	root	8000   	Column#3, Column#5, Column#6, Column#7, Column#8, Column#9, Column#10, Column#11, Column#12, Column#13, Column#14, Column#15, Column#16, Column#17, Column#18, ifnull(cast(Column#19, var_string(20)), )->Column#26, Column#20, Column#21	85     	time:805.1ms, loops:2, Concurrency:5	129.1 KB	N/A
	  └─Selection_8     	root	8000   	eq(lower(Column#2), "pj_major_5790001")                                                                                                                                                                                                  	85     	time:804.7ms, loops:2               	405.2 KB	N/A
	    └─MemTableScan_9	root	10000  	table:TABLES   


This is a slow query on the offline server. The query on the online cloud server only takes 0.0* milliseconds, which is 10 times faster.
| username: TiDBer_30hewbUu | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: tony5413 | Original post link

The information provided is incomplete.

| username: TiDBer_30hewbUu | Original post link

What information do you need? This is the slow query information from the dashboard.

| username: Kongdom | Original post link

Which is the slow query statement?
Judging by the execution plan, it should be read from TiDB’s memory, right?

| username: 考试没答案 | Original post link

root belongs to TiDB. cop belongs to TiKV.

Is this what it means?

| username: Kongdom | Original post link

That’s the way it is.

| username: tidb菜鸟一只 | Original post link

Typically, it’s the TiKV that is slow, and TiDB is rarely slow, right?

| username: TiDBer_30hewbUu | Original post link

The query statement is the one at the beginning of the article: SHOW TABLE STATUS LIKE 'table'. Both the image and text information are from the dashboard, and there is no TiKV information.

| username: Kongdom | Original post link

I think it should be a query on TiDB.

| username: Running | Original post link

TiDB is responsible for optimizing, scheduling, and precompiling, while the actual execution is on TiKV.

| username: 海石花47 | Original post link

TiDB itself does not store persistent metadata; this operation should be executed on TiKV. The first execution is a bit slow because there is no cache, but if you execute it consecutively, it will be much faster in subsequent attempts.

| username: TiDBer_30hewbUu | Original post link

It didn’t get faster.

| username: TiDBer_30hewbUu | Original post link

The SQL execution time for slow queries in the dashboard, what does it mean? Isn’t it the sum of the times below? If not, is that SQL execution time for TiDB or TiKV? Through the dashboard, I can’t pinpoint where the slowness in my query is.

| username: TiDBer_30hewbUu | Original post link

Is there any expert who can answer this?

| username: Kongdom | Original post link

I remember sharing this before; it’s not just a simple summation. Because it involves distributed parallel computing, some of the time is included in other computations.

| username: 海石花47 | Original post link

It seems like you didn’t capture the entire image, right? I remember there is also the time taken to send to the client below.

| username: 海石花47 | Original post link

The sum of all the times below will exceed the total time spent. However, in this chart, the sum of the times below is less than the total time.

| username: 海石花47 | Original post link

  1. How large is the data volume of this table?
  2. How many CPU cores does TiDB have? Is the CPU usage high?
  3. What is the health status of this table? If the health status is very low, try running an ANALYZE.
| username: TiDBer_30hewbUu | Original post link

This is a SHOW statement. My current question is about the slow query where the SQL execution time is 807ms. What does this represent? Is it the time consumed by TiDB, TiKV, or the total time? Why don’t the values below add up to this value? How can I analyze slow queries through the dashboard in this case?