Are there any methods or configurations to speed up the insertion when importing large amounts of data into the TiDB database using txt files?

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

Original topic: 大数据量数据以txt导入tidb数据库的时候有没有加快其插入速度的方法或者配置

| username: TiDBer_微风轻吟

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.5.1
[Reproduction Path] Mainly inserting a large amount of data, 180,000 rows, over 80MB
[Encountered Problem: Phenomenon and Impact] The insertion speed feels a bit slow. Are there any optimization methods?
The id is an auto-increment id, and the id is not inserted during the insertion. Seeking help from experts to clarify.

| username: 随缘天空 | Original post link

I recommend using ETL tools for importing.

| username: TiDBer_微风轻吟 | Original post link

Okay, thank you, I’ll go take a look.

| username: TiDBer_微风轻吟 | Original post link

Do you have any native configurations for TiDB or related tools that can solve this problem? It seems like ETL mainly focuses on data processing rather than speeding up the import process (maybe I don’t understand enough, I just saw this :joy:).

| username: xfworld | Original post link

lightning is super fast

| username: kkpeter | Original post link

Concurrent batch writing

| username: 随缘天空 | Original post link

Actually, your data volume is not that large, just a few hundred thousand records. If you use TiDB, you might need to convert the txt file into a csv format to use it. Alternatively, you can write a program to read the txt file and then perform batch inserts, which should also be fast.

| username: YuchongXU | Original post link

Inserting slices of txt with multiple processes

| username: 像风一样的男子 | Original post link

80MB is not that large. You can directly open it with a text editor, convert it to CSV format, and then import it. It will only take a few minutes.

| username: WinterLiu | Original post link

The amount isn’t that large, right? Converting it to CSV and importing it using Lightning should be quick.

| username: 呢莫不爱吃鱼 | Original post link

First convert it to CSV, then use Lightning. It only takes a few minutes.

| username: 小于同学 | Original post link

You can first convert it to CSV, then use Lightning to import it.

| username: tidb菜鸟一只 | Original post link

You didn’t concatenate all the data to be inserted into an insert statement, did you…?

| username: yytest | Original post link

Try using a third-party tool to import it.

| username: hey-hoho | Original post link

You can directly use Lightning to import the txt file, 180,000 rows will be done in minutes:

| username: TiDBer_QYr0vohO | Original post link

You can just use lightningx.