Navicat Importing SQL to TiDB Prompts "Lost Connection to Server During Query"

Translated
1

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

Original topic: Navicat导入SQL到TIDB提示lost connection to server during query

| username: Johnnes_Xnn

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.0
[Reproduction Path] Using Navicat to import a SQL file with 100,000 records, it prompts “lost connection to server during query”
[Encountered Issue: Import prompts “lost connection to server during query”]
[Resource Configuration]

image
image861×436 3.41 KB

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

Did the KV resources run out and cause a restart?

3
| username: tidb狂热爱好者 | Original post link

The prompt you mentioned indicates that TiDB is out of memory. Please share the architecture diagram of your machine.

4
| username: Jolyne | Original post link

The disk should not meet the standard.

5
| username: 哈喽沃德 | Original post link

The max_allowed_packet setting is probably too low.

6
| username: tidb狂热爱好者 | Original post link

Hmm, it feels like the disk is a cloud disk.

7
| username: 哈喽沃德 | Original post link

Is there a commit among these 100,000 SQL statements? Is it committed once every 1,000 lines?

8
| username: 哈喽沃德 | Original post link
  1. Adjust the batch-size parameter: The batch-size parameter determines the amount of data written each time. By increasing the batch-size parameter, you can reduce the number of write operations, thereby improving insertion efficiency.
  2. Adjust the max-allowed-packet parameter: The max-allowed-packet parameter determines the maximum amount of data for a single network transmission. Appropriately increasing this parameter can reduce the number of network transmissions, thereby improving insertion efficiency.
  3. Adjust the max-connections parameter: The max-connections parameter determines the maximum number of connections for TiDB. If there are many insert operations, you can appropriately increase this parameter to improve insertion efficiency.
9
| username: tony5413 | Original post link

Can you check the database logs?

10
| username: 哈喽沃德 | Original post link

I wrote my own program specifically to address the issue of low execution efficiency for large file SQL.

11
| username: Johnnes_Xnn | Original post link

No, the disk had an issue and couldn’t write, so it was restarted. Then, another TiKV was started. It was taken offline yesterday, but it still shows as offline.

12
| username: Johnnes_Xnn | Original post link

The disk was tested before and basically met the standards. It is an SSD, but it might be a cloud disk.

13
| username: Johnnes_Xnn | Original post link

Using Navicat’s data synchronization feature, it commits once per transaction.

14
| username: 哈喽沃德 | Original post link

Cloud storage might be slow, but it shouldn’t throw errors. Try setting those three parameters as I suggested, and then process the SQL again to see if it works.

15
| username: tidb狂热爱好者 | Original post link

I think you need to understand what a cloud disk is. Many cloud disks have poor performance.

16
| username: 哈喽沃德 | Original post link

Many cloud providers now offer SSD fast disks.

17
| username: Johnnes_Xnn | Original post link

Got it.

18
| username: 哈喽沃德 | Original post link

Yes, that’s him.

19
| username: wangccsy | Original post link

Did the service restart? Or was it shut down?

20
| username: Johnnes_Xnn | Original post link

Is there a problem with this value?