Skip to content

ddl: introduce a new system variable to control the store-write-bwlimit when ingesting#57145

Merged
ti-chi-bot[bot] merged 5 commits intopingcap:masterfrom
CbcWestwolf:ddl_ingest_write_limit
Nov 14, 2024
Merged

ddl: introduce a new system variable to control the store-write-bwlimit when ingesting#57145
ti-chi-bot[bot] merged 5 commits intopingcap:masterfrom
CbcWestwolf:ddl_ingest_write_limit

Conversation

@CbcWestwolf
Copy link
Member

@CbcWestwolf CbcWestwolf commented Nov 5, 2024 •
edited
Loading

What problem does this PR solve?

Issue Number: close #57156

Problem Summary:

See the issue description

What changed and how does it work?

Introduce a new system variable to control the store-write-bwlimit when ingesting

Check List

Tests

  • Unit test
  • Integration test
  • Manual test (add detailed scripts or steps below)
  • No need to test
    • I checked and no code files have been changed.

Manual test:

  1. bootstrap a cluster
  2. Turn on tidb_ddl_enable_fast_reorg, and with tidb_enable_dist_task on or off, do the following test:
    1. set the tidb_ddl_reorg_max_write_speed to 1mb
    2. add index for a table, the speed is slow
    3. after setting the tidb_ddl_reorg_max_write_speed to 0(no limitation), the speed of adding index increases

For a table containing 200,000 rows:

TiDB root@127.0.0.1:test> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 200000   |
+----------+
1 row in set
Time: 0.272s
TiDB root@127.0.0.1:test> select * from t1 limit 3;
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                                                                                                                                            |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0  | fIhixzEuzMNSXLflhCwAktUGYYhXHfgqVHhmognLsnbaEQhXXvzJOehnEXunFcaeVoZSgLNXEFWTAWjfcPYCQRYNKRlHFGJCstAarZvqyeqwmJoHquIfGHFAUIUovzCeyisLSCrIeypScZdsubrPCcbiyBggiehBqnplnbRoVgNogIVdymQwXdUoyhXNzUzLdBhuqdYcznbnCbkZoFdPQiDYBhECaxNtGwbIjHqGsziqIZTHSxzxlmQVZrsUaRb |
| 1  | UdcuwmGBTvKnZbvFUISeLxhzCYmmGqBkTBodTGxKlPIheJffnZUezUAKzVSOCcIFyJxAGmWrqtJhIblTwOLuSPEbzyxGjStWbCrKQxndouhfvnbgeqzCWdNGcSPCjWOyKGixbEjHejDrCXOuhTVdMqouiEKhWPlqBGydoAVUSBHAVqSJdjIhgASkbzIWiFbrtgOMYuPqSqnoMnMaJdatMUoWofkVhRdxOorjyOyWtfaMKTdWdXxQvIjRmZUezRl |
| 2  | UJttwvfdAXHvQxCITRMoAvLNVElknAuGssAOciBtXzqtoiYISIiicCelyiWGNqjHKfXgqxUJWbbtlhFuEdhvlVPGrcwcArMwcmLaIxklvCDqOJnBNgoPMGTSQaMesuugXAuHHmgjgoIpkQGUdsCcAiDOhTmGBYhZfzqcdCXZNDOFiqOCsYQUCiFrMzEOOcZeJPTHGaZCsMHSQhXxcFqThkWUULEIQRlgBaQBgexlXOFqASzhtBGaFtRMcMlbIGF |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set
Time: 0.019s
TiDB root@127.0.0.1:test> show create table t1;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (                                         |
|       |   `id` int NOT NULL,                                        |
|       |   `data` varchar(255) DEFAULT NULL,                         |
|       |   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */     |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+
1 row in set
Time: 0.013s

Setting tidb_ddl_reorg_max_write_speed from 4mb to unlimited would result in different speeds of adding index:

TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '4mb';
Query OK, 0 rows affected
Time: 0.010s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 63.228s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.107s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '8mb';
Query OK, 0 rows affected
Time: 0.031s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 33.176s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.105s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '16mb';
Query OK, 0 rows affected
Time: 0.059s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 15.432s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.220s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '32mb';
Query OK, 0 rows affected
Time: 0.026s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 7.717s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.139s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '64mb';
Query OK, 0 rows affected
Time: 0.018s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 4.387s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.140s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = '128mb';
Query OK, 0 rows affected
Time: 0.019s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 4.375s

TiDB root@127.0.0.1:test> alter table t1 drop index idx_data;
Query OK, 0 rows affected
Time: 0.096s
TiDB root@127.0.0.1:test> set @@global.tidb_ddl_reorg_max_write_speed = 0;
Query OK, 0 rows affected
Time: 0.038s
TiDB root@127.0.0.1:test> CREATE INDEX idx_data ON test.t1 (data)
Query OK, 0 rows affected
Time: 4.229s

With the tidb_ddl_reorg_max_write_speed increased, the metric shows that the number of write operations increased as expected
image

To verify that decreasing the tidb_ddl_reorg_max_write_speed helps to lower the impact of ADD INDEX on online service, we observe that with a further speed limitation, the QPS of sysbench increases.

image

Side effects

  • Performance regression: Consumes more CPU
  • Performance regression: Consumes more Memory
  • Breaking backward compatibility

Documentation

  • Affects user behaviors
  • Contains syntax changes
  • Contains variable changes
  • Contains experimental features
  • Changes MySQL compatibility

Release note

Please refer to Release Notes Language Style Guide to write a quality release note.

Add a system variable tidb_ddl_reorg_max_write_speed to limit the speed of ingesting when adding index
增加系统变量 tidb_ddl_reorg_max_write_speed 来限制加索引时 ingesting 的速度上限

@ti-chi-bot ti-chi-bot bot added do-not-merge/needs-linked-issue do-not-merge/needs-tests-checked release-note-none Denotes a PR that doesn't merit a release note. size/S Denotes a PR that changes 10-29 lines, ignoring generated files. labels Nov 5, 2024
@codecov
Copy link

codecov bot commented Nov 5, 2024 •
edited
Loading

Codecov Report

Attention: Patch coverage is 68.96552% with 9 lines in your changes missing coverage. Please review.

Project coverage is 74.8591%. Comparing base (3651427) to head (91b644b).
Report is 81 commits behind head on master.

Additional details and impacted files 
@@               Coverage Diff                @@
##             master     #57145        +/-   ##
================================================
+ Coverage   73.2148%   74.8591%   +1.6442%     
================================================
  Files          1652       1717        +65     
  Lines        456167     485802     +29635     
================================================
+ Hits         333982     363667     +29685     
+ Misses       101669      99640      -2029     
- Partials      20516      22495      +1979
Flag Coverage Δ
integration 49.3224% <37.9310%> (?)
unit 72.4685% <68.9655%> (-0.0263%) ⬇️

Flags with carried forward coverage won't be shown. Click here to find out more.

Components Coverage Δ
dumpling 52.7673% <ø> (-0.1806%) ⬇️
parser ∅ <ø> (∅)
br 59.7891% <ø> (+13.8076%) ⬆️

@ti-chi-bot ti-chi-bot bot added size/M Denotes a PR that changes 30-99 lines, ignoring generated files. do-not-merge/needs-triage-completed and removed size/S Denotes a PR that changes 10-29 lines, ignoring generated files. do-not-merge/needs-linked-issue do-not-merge/needs-tests-checked labels Nov 6, 2024
@CbcWestwolf CbcWestwolf changed the title *: wip ddl: Introduce a new system variable to control the store-write-bwlimit when ingesting Nov 6, 2024
@CbcWestwolf CbcWestwolf changed the title ddl: Introduce a new system variable to control the store-write-bwlimit when ingesting ddl: introduce a new system variable to control the store-write-bwlimit when ingesting Nov 6, 2024
@CbcWestwolf
Copy link
Member Author

CbcWestwolf commented Nov 6, 2024

/retest

@CbcWestwolf
Copy link
Member Author

CbcWestwolf commented Nov 7, 2024

/hold

@ti-chi-bot ti-chi-bot bot added do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. release-note Denotes a PR that will be considered when it comes time to generate release notes. needs-cherry-pick-release-7.5 Should cherry pick this PR to release-7.5 branch. needs-cherry-pick-release-8.1 Should cherry pick this PR to release-8.1 branch. needs-cherry-pick-release-8.5 Should cherry pick this PR to release-8.5 branch. needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. and removed release-note-none Denotes a PR that doesn't merit a release note. labels Nov 7, 2024
@CbcWestwolf
Copy link
Member Author

CbcWestwolf commented Nov 12, 2024

/run-check-issue-triage-complete

@CbcWestwolf
Copy link
Member Author

CbcWestwolf commented Nov 12, 2024

/unhold

@ti-chi-bot ti-chi-bot bot removed the do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. label Nov 12, 2024
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 14, 2024

In response to a cherrypick label: new pull request created to branch release-7.1: #57375.

@ti-chi-bot ti-chi-bot mentioned this pull request Nov 14, 2024
Closed
13 tasks
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 14, 2024

In response to a cherrypick label: new pull request created to branch release-7.5: #57376.

ti-chi-bot pushed a commit to ti-chi-bot/tidb that referenced this pull request Nov 14, 2024
@CbcWestwolf @ti-chi-bot
This is an automated cherry-pick of pingcap#57145
9a62d7f 
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
@ti-chi-bot ti-chi-bot mentioned this pull request Nov 14, 2024
Merged
13 tasks
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 14, 2024

In response to a cherrypick label: new pull request created to branch release-8.1: #57377.

ti-chi-bot pushed a commit to ti-chi-bot/tidb that referenced this pull request Nov 14, 2024
@CbcWestwolf @ti-chi-bot
This is an automated cherry-pick of pingcap#57145
503d49e 
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
@ti-chi-bot ti-chi-bot mentioned this pull request Nov 14, 2024
Merged
13 tasks
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Nov 14, 2024

In response to a cherrypick label: new pull request created to branch release-8.5: #57378.

@ti-chi-bot ti-chi-bot mentioned this pull request Nov 14, 2024
Merged
13 tasks
@CbcWestwolf CbcWestwolf deleted the ddl_ingest_write_limit branch November 14, 2024 05:58
@CbcWestwolf CbcWestwolf mentioned this pull request Nov 18, 2024
Merged
16 tasks
ti-chi-bot bot pushed a commit that referenced this pull request Nov 19, 2024
@ti-chi-bot
ddl: introduce a new system variable to control the `store-write-bwli…
c104575 
…mit` when ingesting (#57145) (#57378)

close #57156
This was referenced Dec 10, 2024
Merged
Merged
Merged
Merged
Open
Open
Open
Open
ti-chi-bot bot pushed a commit that referenced this pull request Dec 11, 2024
@ti-chi-bot
ddl: introduce a new system variable to control the `store-write-bwli…
c0e0d45 
…mit` when ingesting (#57145) (#57376)

close #57156
tangenta pushed a commit to tangenta/tidb that referenced this pull request Dec 30, 2024
@ti-chi-bot @tangenta
ddl: introduce a new system variable to control the `store-write-bwli…
150ffae 
…mit` when ingesting (pingcap#57145) (pingcap#57376)

close pingcap#57156
tangenta pushed a commit to tangenta/tidb that referenced this pull request Jan 7, 2025
@ti-chi-bot @tangenta
ddl: introduce a new system variable to control the `store-write-bwli…
df76950 
…mit` when ingesting (pingcap#57145) (pingcap#57376)

close pingcap#57156
tangenta pushed a commit to tangenta/tidb that referenced this pull request Jan 7, 2025
@ti-chi-bot @tangenta
Cherry-pick pingcap#57044, pingcap#57145 and pingcap#57553 to v7.5.1
a850faa
ti-chi-bot bot pushed a commit that referenced this pull request Feb 5, 2025
@ti-chi-bot
ddl: introduce a new system variable to control the `store-write-bwli…
0bb7df5 
…mit` when ingesting (#57145) (#57374)

close #57156
@ti-chi-bot ti-chi-bot bot removed needs-cherry-pick-release-8.1 Should cherry pick this PR to release-8.1 branch. needs-cherry-pick-release-7.1 Should cherry pick this PR to release-7.1 branch. labels Apr 18, 2025
@CbcWestwolf CbcWestwolf mentioned this pull request Apr 29, 2025
Closed
ti-chi-bot bot pushed a commit that referenced this pull request Jun 23, 2025
@ti-chi-bot
ddl: introduce a new system variable to control the `store-write-bwli…
ae2ded3 
…mit` when ingesting (#57145) (#57377)

close #57156
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@tangenta tangenta tangenta approved these changes

@fzzf678 fzzf678 fzzf678 approved these changes

+1 more reviewer

@yudongusa yudongusa yudongusa approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

approved lgtm needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. needs-cherry-pick-release-7.5 Should cherry pick this PR to release-7.5 branch. needs-cherry-pick-release-8.5 Should cherry pick this PR to release-8.5 branch. release-note Denotes a PR that will be considered when it comes time to generate release notes. size/M Denotes a PR that changes 30-99 lines, ignoring generated files.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

ddl ingest can not set store-write-bwlimit while using lightning local backend

5 participants

@CbcWestwolf @ti-chi-bot @tangenta @yudongusa @fzzf678