Error Importing Data with pt-archiver

Translated
1

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

Original topic: pt-archiver导入数据报错

| username: LBX流鼻血

[TiDB Usage Environment] Online
[TiDB Version] 6.1
[Encountered Problem] While working with MariaDB 10.4.18
[Reproduction Path] What operations were performed when the problem occurred
[Problem Phenomenon and Impact]
When performing data synchronization with pt-archiver, there were a total of 190 million records. When it reached over 140 million, the following error occurred:
DBD::mysql::st execute failed: Error: KV error safe to retry Error(Txn(Error(Mvcc(Error(TxnLockNotFound { start_ts: TimeStamp(434848378955497480), commit_ts: TimeStamp(434848395365449748), key: [116, 128, 0, 0, 0, 0, 0, 1, 1, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 1, 14, 96, 14, 44, 14, 109, 14, 41, 255, 14, 47, 14, 51, 14, 51, 14, 139, 255, 2, 33, 14, 139, 14, 74, 14, 185, 255, 14, 74, 2, 33, 14, 42, 14, 42, 255, 14, 139, 14, 96, 2, 33, 14, 49, 255, 14, 139, 14, 45, 14, 48, 2, 33, 255, 14, 74, 14, 45, 14, 41, 14, 46, 255, 14, 46, 14, 109, [for Statement “LOAD DATA LOCAL INFILE ? INTO TABLE dddd.ttttCHARACTER SET utf8(id,amount_code,group_code,group_name,customer_code,customer_name,sales_center_code,sales_center_name,account_code,amount,is_amount_control,is_discount_control,tenant_code,create_time,created_by,update_time,updated_by,version,delete_flag,remark,freeze_date,freeze_time)” with ParamValues: 0=‘/tmp/VA7wb2D5FMpt-archiver’] at ./pt-archiver line 6212.

Could the experts please assist and guide on what might be causing this issue and what details need to be paid attention to during synchronization?

2
| username: songxuecheng | Original post link

Refer to this: TiDB 锁冲突问题处理 | PingCAP 归档文档站

3
| username: cs58_dba | Original post link

In production, transactions are generally committed in batches. This table of yours is indeed quite large.

4
| username: Mark | Original post link

It might be related to TiKV locks, has the transaction commit TTL timed out?
For pt-arch, it is recommended to appropriately reduce the batch commit quantity based on the data volume and machine performance.
When using the pt-arch tool with MySQL, you can set it to 500-1000-10000-20000-50000 based on your needs. Normally, for a single MySQL machine with standard SSD, the operation time for 100 million records is generally 120 minutes, and during this period, keeping the batch size within 1000 can effectively reduce latency.

5
| username: cs58_dba | Original post link

It also has something to do with the size of each row of data. We generally set it to commit a transaction with up to 20,000 rows.

6
| username: Mark | Original post link

The efficiency of 1000 is also good, it’s just the difference in batch size.

7
| username: LBX流鼻血 | Original post link

Thanks to the experts above for providing the solution.