lightning import is slow when importing lots of tables with AUTO_INCREMENT
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
1. Use 10 lightning to parall import 500k tables into TiDB cluster, each lightning import 50k tables and each table 10k rows, table schema is like
```
CREATE TABLE `xxx` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
...
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin shard_row_id_bits=4 pre_split_regions=3 AUTO_INCREMENT=15570001
```
### 2. What did you expect to see? (Required)
import should be fast.
### 3. What did you see instead (Required)
import is very slow. there are lots of "LTER TABLE xxx AUTO_INCREMENT=yyy"

```
[2024/01/05 05:53:28.666 +00:00] [INFO] [import.go:1392] [progress] [total=5.2%] [tables="19049/49700 (38.3%)"] [chunks="1634/32219 (5.1%)"] [engines="3299/64475 (5.1%)"] [restore-bytes=38.17GiB/739.6GiB] [restore-rows=16340000/316641144 (estimated)] [import-bytes=42.61GiB/811.5GiB(estimated)] ["encode speed(MiB/s)"=3.1018280653207904] [state=writing] [remaining=63h44m4s]
[2024/01/05 05:58:28.665 +00:00] [INFO] [import.go:1392] [progress] [total=5.2%] [tables="19095/49700 (38.4%)"] [chunks="1680/32219 (5.2%)"] [engines="3391/64475 (5.3%)"] [restore-bytes=38.3GiB/739.6GiB] [restore-rows=16800000/324446207( estimated)] [import-bytes=42.81GiB/812.6GiB(estimated)] ["encode speed(MiB/s)"=3.0400480858329564] [state=writing] [remaining=65h1m22s]
[2024/01/05 06:03:28.666 +00:00] [INFO] [import.go:1392] [progress] [total=5.3%] [tables="19135/49700 (38.5%)"] [chunks="1720/32219 (5.3%)"] [engines="3473/64475 (5.4%)"] [restore-bytes=38.63GiB/739.6GiB] [restore-rows=17200000/329287206 (estimated)] [import-bytes=43.23GiB/813.5GiB(estimated)] ["encode speed(MiB/s)"=2.996975566464612] [state=writing] [remaining=65h27m2s]
[2024/01/05 06:08:28.666 +00:00] [INFO] [import.go:1392] [progress] [total=5.4%] [tables="19168/49700 (38.6%)"] [chunks="1753/32219 (5.4%)"] [engines="3538/64475 (5.5%)"] [restore-bytes=39.73GiB/739.6GiB] [restore-rows=17530000/326368852 (estimated)] [import-bytes=44.43GiB/813.8GiB(estimated)] ["encode speed(MiB/s)"=3.0133043036409615] [state=writing] [remaining=65h19m10s]
[2024/01/05 06:13:28.666 +00:00] [INFO] [import.go:1392] [progress] [total=5.7%] [tables="19215/49700 (38.7%)"] [chunks="1800/32219 (5.6%)"] [engines="3633/64475 (5.6%)"] [restore-bytes=42.16GiB/739.6GiB] [restore-rows=18000000/315760125 (estimated)] [import-bytes=46.99GiB/811.4GiB(estimated)] ["encode speed(MiB/s)"=3.128525492965816] [state=writing] [remaining=62h52m50s]
```
### 4. What is your TiDB version? (Required)
```
[root@lightning-0 /]# /tidb-lightning -V
Release Version: v7.6.0-pr50074
Git Commit Hash: 9ff6d402636d79aa6bca9172d72bf1b147a2f6f5
Git Branch: heads/refs/tags/v7.6.0-pr50074
Go Version: go1.21.5
UTC Build Time: 2024-01-04 07:45:23
Race Enabled: false
[root@tc-tidb-0 /]# /tidb-server -V
Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e2ecfedc000ffb92c67207bea81380f020
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:50:14
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: unistore
```
Contributor guide
Assessment
This issue has not been assessed yet.