Method for Calculating Monitoring Metrics for Batch Table Scan

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

Original topic: batch table scan 监控指标计算方法

| username: TiDBer_KkruFifg

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

Monitoring Metrics Location
overview - tikv - coprocessor executor count
batch_table_scan

Question
Assume a SQL query as follows:
select id, name, age, city from t1 where name=‘aaa’ limit 1000;
where name has an index
This SQL query will return 1000 rows of data.
How many batch_table_scan will this SQL query have? How can this value be calculated more accurately?
Can the execution plan calculate this or are there other methods?

Currently, we are encountering an issue where, at a certain moment, the cluster duration fluctuates, and the batch_table_scan increases from the usual 260k to over 590k.

| username: WalterWj | Original post link

Check if there were any slow SQL queries during that time. Did the QPS increase significantly? :thinking:

| username: TiDBer_KkruFifg | Original post link

QPS did not increase.
Slow SQL increased, but not too much.
Thank you.

| username: TiDBer_KkruFifg | Original post link

Could it be that a table scan is caused by not using a covering index and having to go back to the table to fetch data?

| username: TiDBer_KkruFifg | Original post link

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

| username: TiDBer_KkruFifg | Original post link

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

| username: TiDBer_KkruFifg | Original post link

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

| username: TiDBer_KkruFifg | Original post link

The default value of tidb_dml_batch_size is 2000. You can adjust it according to your needs.

| username: TiDBer_KkruFifg | Original post link

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

| username: TiDBer_KkruFifg | Original post link

The main reason is that the default value of the tidb_distsql_scan_concurrency parameter is 15, which is relatively high. You can try reducing this value to see if it improves performance.

| username: TiDBer_KkruFifg | Original post link

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

| username: 有猫万事足 | Original post link

First of all, each operator in the current TiKV implements the BatchExecutor interface.

The calculation code for this metric is as follows:

So the algorithm for this batch_table_scan metric is that as long as a TableScan occurs once, this metric will increase by 1.

Actually, I feel like you’re taking a roundabout way. If TableScan surges at the same time, there must be a lot of slow queries.
It’s better to start with slow queries.

| username: andone | Original post link

[tikv/components/tidb_query_executors/src/runner.rs at 36e2154f12e85ce5edc0a47d03757d826c37ac64 · tikv/tikv · GitHub]

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.