Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

add index for small partition table on v8.1.0 is slower than it on v7.5.1 #54230

Closed
seiya-annie opened this issue Jun 26, 2024 · 2 comments · Fixed by #56406
Closed

add index for small partition table on v8.1.0 is slower than it on v7.5.1 #54230

seiya-annie opened this issue Jun 26, 2024 · 2 comments · Fixed by #56406
Assignees
@tangenta
Labels
component/ddl This issue is related to DDL of TiDB. type/enhancement The issue or PR belongs to an enhancement.

Comments

@seiya-annie
Copy link

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists tt1, tt2;
set global tidb_partition_prune_mode='dynamic';
set session tidb_partition_prune_mode='dynamic';
CREATE TABLE tt1 (
    id INT NOT NULL,
    listid INT,
    name varchar(10),
    primary key (listid) clustered
)
PARTITION BY LIST (listid) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4)
);
CREATE TABLE tt2 (
    id INT NOT NULL,
    listid INT
);
create index idx_listid on tt1(id,listid);
create index idx_listid on tt2(listid);

2. What did you expect to see? (Required)

3. What did you see instead (Required)

on v7.5.1 cluster

mysql> create index idx_listid on tt1(id,listid);
Query OK, 0 rows affected (0.58 sec)

mysql> create index idx_listid on tt2(listid);
Query OK, 0 rows affected (0.51 sec)

on v8.1.0 and nightly cluster

mysql> create index idx_listid on tt1(id,listid);
Query OK, 0 rows affected (1.99 sec)

mysql> create index idx_listid on tt2(listid);
Query OK, 0 rows affected (1.35 sec)

tidb_enable_dist_task is off in v7.5.1, and on in 8.1.0

4. What is your TiDB version? (Required)

v8.1.0 and master
@seiya-annie seiya-annie added the type/enhancement The issue or PR belongs to an enhancement. label Jun 26, 2024
@seiya-annie seiya-annie changed the title add index in small partition table on v8.1.0 is slower than it on v7.5.1 add index for small partition table on v8.1.0 is slower than it on v7.5.1 Jun 26, 2024
@seiya-annie seiya-annie added the component/ddl This issue is related to DDL of TiDB. label Jun 26, 2024
@tangenta tangenta self-assigned this Jun 26, 2024
@Defined2014
Copy link
Contributor

It also influences check_dev_2 in ci env. Please fix it, 🙏

@mayjiang0203
Copy link

mayjiang0203 commented Jul 15, 2024 •
edited
Loading

Reproduce it by enabling tidb_enable_dist_task in the nightly version. So it has nothing to do with the version, it is only related to tidb_enable_dist_task.

MySQL [(none)]> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.2.0-alpha-527-g0c9a679
Edition: Community
Git Commit Hash: 0c9a67911775a9575e9ed2dcb860288cc2c37c45
Git Branch: HEAD
UTC Build Time: 2024-07-05 03:13:29
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test]> CREATE TABLE tt1 (
    ->     id INT NOT NULL,
    ->     listid INT,
    ->     name varchar(10),
    ->     primary key (listid) clustered
    -> )
    -> PARTITION BY LIST (listid) (
    ->     PARTITION p1 VALUES IN (1),
    ->     PARTITION p2 VALUES IN (2),
    ->     PARTITION p3 VALUES IN (3),
    ->     PARTITION p4 VALUES IN (4)
    -> );
Query OK, 0 rows affected (0.52 sec)

MySQL [test]> CREATE TABLE tt2 (
    ->     id INT NOT NULL,
    ->     listid INT
    -> );
Query OK, 0 rows affected (0.51 sec)

MySQL [test]> create index idx_listid on tt1(id,listid);
Query OK, 0 rows affected (3.02 sec)

MySQL [test]> create index idx_listid on tt2(listid);
Query OK, 0 rows affected (2.01 sec)

MySQL [test]> drop index idx_listid on tt1;
Query OK, 0 rows affected (0.52 sec)

MySQL [test]> drop index idx_listid on tt2;
Query OK, 0 rows affected (0.52 sec)

MySQL [test]> select @@tidb_enable_dist_task;
+-------------------------+
| @@tidb_enable_dist_task |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

MySQL [test]> set global tidb_enable_dist_task=0;
Query OK, 0 rows affected (0.02 sec)

MySQL [test]> select @@tidb_enable_dist_task;
+-------------------------+
| @@tidb_enable_dist_task |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

MySQL [test]> create index idx_listid on tt1(id,listid);
Query OK, 0 rows affected (1.01 sec)

MySQL [test]> create index idx_listid on tt2(listid);
Query OK, 0 rows affected (0.51 sec)

ddl logs with tidb_enable_dist_task enabled

[2024/07/15 17:00:34.963 +08:00] [INFO] [ddl_worker.go:261] ["add DDL jobs"] [category=ddl] ["batch count"=1] [jobs="ID:126, Type:add index, State:queueing, SchemaState:none, SchemaID:2, TableID:123, RowCount:0, ArgLen:6, start time: 2024-07-15 17:00:34.954 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0; "] [table=true]
[2024/07/15 17:00:34.964 +08:00] [INFO] [ddl.go:1214] ["start DDL job"] [category=ddl] [job="ID:126, Type:add index, State:queueing, SchemaState:none, SchemaID:2, TableID:123, RowCount:0, ArgLen:6, start time: 2024-07-15 17:00:34.954 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0"] [query="create index idx_listid on tt2(listid)"]
[2024/07/15 17:00:34.979 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=67] [neededSchemaVersion=68] ["start time"=682.775µs] [gotSchemaVersion=68] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:34.982 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=68]
[2024/07/15 17:00:34.996 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=68] [neededSchemaVersion=69] ["start time"=602.628µs] [gotSchemaVersion=69] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:34.998 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=69]
[2024/07/15 17:00:35.012 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=69] [neededSchemaVersion=70] ["start time"=603.728µs] [gotSchemaVersion=70] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:35.015 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=70]
[2024/07/15 17:00:36.260 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=70] [neededSchemaVersion=71] ["start time"=722.917µs] [gotSchemaVersion=71] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:36.263 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=71]
[2024/07/15 17:00:36.277 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=71] [neededSchemaVersion=72] ["start time"=604.536µs] [gotSchemaVersion=72] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:36.280 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=72]
[2024/07/15 17:00:36.306 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=72] [neededSchemaVersion=73] ["start time"=564.507µs] [gotSchemaVersion=73] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:00:36.308 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=126] [version=73]
[2024/07/15 17:00:36.967 +08:00] [INFO] [ddl.go:1320] ["DDL job is finished"] [category=ddl] [jobID=126]
[2024/07/15 17:00:36.967 +08:00] [INFO] [callback.go:140] ["performing DDL change, must reload"] [category=ddl]

ddl logs with tidb_enable_dist_task disabled

[2024/07/15 17:01:48.697 +08:00] [INFO] [ddl_worker.go:261] ["add DDL jobs"] [category=ddl] ["batch count"=1] [jobs="ID:130, Type:add index, State:queueing, SchemaState:none, SchemaID:2, TableID:123, RowCount:0, ArgLen:6, start time: 2024-07-15 17:01:48.654 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0; "] [table=true]
[2024/07/15 17:01:48.697 +08:00] [INFO] [ddl.go:1214] ["start DDL job"] [category=ddl] [job="ID:130, Type:add index, State:queueing, SchemaState:none, SchemaID:2, TableID:123, RowCount:0, ArgLen:6, start time: 2024-07-15 17:01:48.654 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0, LocalMode: false, UniqueWarnings:0"] [query="create index idx_listid on tt2(listid)"]
[2024/07/15 17:01:48.713 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=87] [neededSchemaVersion=88] ["start time"=672.319µs] [gotSchemaVersion=88] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:48.716 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=88]
[2024/07/15 17:01:48.730 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=88] [neededSchemaVersion=89] ["start time"=575.301µs] [gotSchemaVersion=89] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:48.733 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=89]
[2024/07/15 17:01:48.747 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=89] [neededSchemaVersion=90] ["start time"=688.687µs] [gotSchemaVersion=90] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:48.750 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=90]
[2024/07/15 17:01:48.949 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=90] [neededSchemaVersion=91] ["start time"=636.804µs] [gotSchemaVersion=91] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:48.952 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=91]
[2024/07/15 17:01:48.966 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=91] [neededSchemaVersion=92] ["start time"=607.192µs] [gotSchemaVersion=92] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:48.968 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=92]
[2024/07/15 17:01:48.997 +08:00] [INFO] [domain.go:293] ["diff load InfoSchema success"] [isV2=false] [currentSchemaVersion=92] [neededSchemaVersion=93] ["start time"=636.151µs] [gotSchemaVersion=93] [phyTblIDs="[123]"] [actionTypes="[7]"] [diffTypes="[\"add index\"]"]
[2024/07/15 17:01:49.001 +08:00] [INFO] [domain.go:909] ["mdl gets lock, update self version to owner"] [jobID=130] [version=93]
[2024/07/15 17:01:49.200 +08:00] [INFO] [ddl.go:1320] ["DDL job is finished"] [category=ddl] [jobID=130]

@tangenta tangenta mentioned this issue Sep 29, 2024
Merged
13 tasks
ti-chi-bot bot pushed a commit that referenced this issue Oct 25, 2024
@tangenta
ddl: skip adding index reorg work for empty tables (#56406)
71238b1 
close #51421, close #54230
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tangenta tangenta

Labels
component/ddl This issue is related to DDL of TiDB. type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

ddl: skip adding index reorg work for empty tables tangenta/tidb
4 participants
@mayjiang0203 @Defined2014 @tangenta @seiya-annie