Optimization Suggestions Needed

Translated
1

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

Original topic: 求优化建议

| username: KuYeDao

【TiDB Usage Environment】

pd * 3 (4c8g)
tidb * 3(4c8g)
tikv * 3(4c8g256g)

【Overview】 Scenario + Problem Overview

    **There are only 230,000 records.**

   Table creation statement:

CREATE TABLE group_info (
group_id bigint(20) NOT NULL COMMENT ‘Group ID’,
parent_group_id bigint(20) NOT NULL COMMENT ‘Parent Group ID’,
parent_group_creator_id varchar(100) NOT NULL COMMENT ‘Parent Group Creator ID’,
group_title varchar(220) NOT NULL COMMENT ‘Group Name’,
group_type tinyint(4) NOT NULL COMMENT ‘Group Type’,
group_state tinyint(4) NOT NULL COMMENT ‘Group State’,
start_time datetime NOT NULL COMMENT ‘Start Time’,
end_time datetime NOT NULL COMMENT ‘End Time’,
group_creator_id varchar(100) NOT NULL COMMENT ‘Creator ID’,
referee_id varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘Referee ID’,
notice_type tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘Notice Type’,
is_delete tinyint(4) DEFAULT ‘0’ COMMENT ‘Is Deleted’,
create_time datetime NOT NULL COMMENT ‘Creation Time’,
update_time datetime NOT NULL COMMENT ‘Update Time’,
create_user varchar(50) NOT NULL COMMENT ‘Creator’,
update_user varchar(50) NOT NULL COMMENT ‘Updater’,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Timestamp’,
PRIMARY KEY (group_id) /*T![clustered_index] CLUSTERED */,
KEY idx_parent_group_id (parent_group_id) COMMENT ‘Parent Group Index’,
KEY idx_group_creator_id (group_creator_id) COMMENT ‘Group Creator ID Index’,
KEY idx_parent_group_id (parent_group_creator_id) COMMENT ‘Parent Group Creator ID Index’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘Group Basic Information Table’

  Query statement

Execution plan SQL: select group_id, group_creator_id from group_info where group_creator_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) and group_state = 1 and is_delete = 0 order by group_id desc limit 10

Stress test SQL: select group_id, group_creator_id from group_info where group_creator_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) and group_state = 1 and start_time < now() and end_time > now() and is_delete = 0 order by group_id desc limit 11

【Background】 What operations have been performed

Execution plan:

image
image1380×138 38.7 KB
EXPLAIN_ANALYZE.xlsx (9.8 KB)

【Phenomenon】 Business and database phenomenon

    Stress test can only reach 85QPS, with only one TiKV CPU maintaining above 90%, while the other six machines are below 15%

【Problem】 Current issues encountered

Performance is much lower than expected, seeking optimization suggestions
One point of confusion: In the second phase of TiKV, condition filtering, it seems like it should stop after finding 10 records, why does it find all 1528 records that meet the conditions?
Understand that too much data retrieval can degrade performance, but even if it only hits a single TiKV, would it be this bad?
Because normal execution SQL takes about 30ms, it feels like the performance is good

(New user on trial, but have learned a lot of principles, should be able to understand)

【Business Impact】

【TiDB Version】

5.3

Company cluster, unable to execute scripts

2
| username: h5n1 | Original post link
  1. Is HAProxy/LVS used for load balancing in front of TiDB?
  2. Use show table group_info regions to check the number of regions. If there is only one TiKV with high load, it indicates a hotspot. Solutions for hotspots:
    (1) Use the pd-ctl operator add split-region xxx or split table command to split the region. After splitting the region, it needs to run for a while to wait for PD to perform hotspot scheduling, or use pd-ctl operator add transfer-leader xxx xx to manually schedule the leader to an idle TiKV.
    (2) Redesign the table structure. TiDB provides several ways to solve hotspots: set the bigint primary key to auto_random, set non-bigint primary keys with shard_row_id_bits + pre_split_regions, or use hash partitioning. By default, new tables will have regions on the original TiKV. Before creating the table, set the tidb_scatter_region variable to ensure regions are evenly scheduled.
  3. Since there is an ORDER BY in the SQL, it needs to fetch all matching records and then sort them before taking the top 11. In the execution plan, topN_20 sorts and takes the top 11 on each TiKV, then returns to TiDB, which sorts again and takes the top 11 (i.e., TopN9).
3
| username: xiaohetao | Original post link

The pressure on each node is unbalanced. Is there a load balancing configuration on the front end?
Is the distribution of region/region leaders balanced?

I think starting from these two aspects should help identify the problem. For specific handling methods, refer to the previous replies.

4
| username: KuYeDao | Original post link

Yes, okay, thank you very much.

5
| username: KuYeDao | Original post link

Yes, mainly the performance is much lower than expected, which I don’t quite understand. Even if it hits a single shard, it still feels like it shouldn’t only have 85 pqs, which is quite puzzling.

6
| username: KuYeDao | Original post link

Because in tests with other tables, a full table scan of an index with 480,000 records only took a few dozen milliseconds. With 85 queries per second concurrency, each query fetching 7000 records, it still only involves a few hundred thousand records.

7
| username: KuYeDao | Original post link

Since the orderby is PK, I understand that each TiKV only needs to return 10 rows. Moreover, the current data is actually distributed on only one TiKV. Assuming there are a total of 3, it seems that at most 30 rows will be returned. Considering our data hit rate, we only need to scan around 100 rows.

8
| username: h5n1 | Original post link

There is a variable tidb_enable_paging. Try adjusting it at the session level and see how it works.

9
| username: KuYeDao | Original post link

Hello, based on my experience with MySQL, it seems that the combination of order by + limit is quite important for SQL optimization. So I would like to ask, is the logic of finding all 1528 rows because the TiKV layer has not yet implemented consideration for order by + limit?

10
| username: h5n1 | Original post link

From the execution plan, topN_20 is executed on TiKV, which means that TiKV fetches the limit rows and then returns them to TiKV. TiDB’s data storage is divided by regions, with each region being a continuous space. TiDB constructs a coprocessor task for each query, with one task per region, and each task returns the result to TiDB. Since topN is pushed down to TiKV, each coprocessor task only returns the limit number of rows needed by TiDB. The total number of rows is 1528, with 11 cop tasks (tasks: 11). TiDB then sorts the limit 10 rows from the 11 tasks and finally takes the limit 10 rows.

11
| username: KuYeDao | Original post link

So can it be understood that step Selection_19 considers order by + limit, and ideally, the number of rows found should be the number of regions * limit?

12
| username: h5n1 | Original post link

Yes, check if limit is pushed down to TiKV.

13
| username: system | Original post link

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