Lighting Import Error [ERROR] [tidb.go:708] ["execute statement failed"] [rows=

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

Original topic: [lighting 导入报错[ERROR] [tidb.go:708] [“execute statement failed”] rows=

| username: xingzhenxiang

【TiDB Usage Environment】Testing
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

[2023/11/23 10:59:05.172 +08:00] [ERROR] [tidb.go:708] [“execute statement failed”] [rows=“[”] [error=“Error 9005 (HY000): Region is unavailable”]
[2023/11/23 10:59:05.172 +08:00] [ERROR] [tidb.go:708] [“execute statement failed”] [rows=“[)]”] [stmt=“REPLACE INTO db.table VALUES()”] [error=“Error 9005 (HY000): Region is unavailable”]

| username: 有猫万事足 | Original post link

You can refer to the content of this article to troubleshoot:

| username: xingzhenxiang | Original post link

Why is this situation still occurring when my cluster has been writing with Lightning for so long?

| username: 有猫万事足 | Original post link

Currently, I am also feeling confused. I see your statement is “replace into,” which might be a way of logical import + conflict overwrite?
It could be that a certain TiKV is too busy or has gone offline.

Do you have the configuration for Lightning and the monitoring for TiKV?

| username: xingzhenxiang | Original post link

Here is the relevant information
tidb-lightning.toml (2.2 KB)

| username: 小龙虾爱大龙虾 | Original post link

It is very likely caused by excessive pressure on the TiKV node.

| username: xingzhenxiang | Original post link

The IO and CPU load are both not high.

| username: 小龙虾爱大龙虾 | Original post link

Follow the troubleshooting steps summarized by the expert.

| username: xingzhenxiang | Original post link

The key is that I haven’t seen where adjustments can alleviate this purely write environment.

| username: 有猫万事足 | Original post link

Indeed, it is logical import + conflict overwrite.

I have also used this combination, and my feeling is that if the replace into conflicts with the data, and if other columns have not changed, it is actually TiDB’s CPU that is heavily consumed, not IO. If IO is heavily consumed, it indicates that a large amount of data with the same ID but completely different content is being processed.

Could it be that you are doing sharding aggregation, but forgot to change the primary key of the table, resulting in a large amount of data with the same ID but different content on each shard, being repeatedly written? This can easily cause TiKV to crash. When I used physical import, such conflicts even caused the table and index data to become inconsistent in the end. :sweat_smile:
This is a thought, you might want to check it out.

| username: 有猫万事足 | Original post link

If it is a scheduled daily lightning import, there is another possibility: the previous import file was not cleaned up during a certain import, and then it was imported again during this incremental import, which could also result in a large number of ID conflicts.

| username: xingzhenxiang | Original post link

No, it’s a new import test for an empty cluster, 18T.

| username: xingzhenxiang | Original post link

Pure data import test

| username: 有猫万事足 | Original post link

For an empty cluster, I still recommend using physical mode for import.
I have used physical mode to import CSV files; I’m not sure about SQL files, but at least it’s certain that SST files are not the only ones that can be imported using physical mode.
If you need to quickly import into an empty cluster, I think it’s worth a try.

| username: xingzhenxiang | Original post link

I have 18TB of data, and I see the official documentation mentions 10TB. I have just one database.

| username: heiwandou | Original post link

Test the import in batches.

| username: xingzhenxiang | Original post link

Please explain in detail how to batch, how to handle database creation statements, how to handle table structures, and how to split data SQL. Thank you.

| username: h5n1 | Original post link

What is the concurrency of the import region_concurrency?

| username: xingzhenxiang | Original post link

region-concurrency = 56, the second adjustment is 42, the third adjustment is 32, the fourth adjustment is 24, the fifth adjustment is 14

| username: h5n1 | Original post link

Does it still report this error at version 14?