Dumpling Export Failure

Translated
1

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

Original topic: dumpling导出失败

| username: wonderful1984

【TiDB Usage Environment】
【TiDB Version】
Release Version: v2.1.17
Git Commit Hash: 603f0b95d391c336132f33de6877feb6e6984e54
Git Branch: HEAD
UTC Build Time: 2019-09-11 11:49:06
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

TiKV
Release Version: 2.1.17
Git Commit Hash: 295eb3385d8f5fbd242d493078221dfd9a022b17
Git Commit Branch: HEAD
UTC Build Time: 2019-09-11 11:47:42
Rust Version: rustc 1.29.0-nightly (4f3c7a472 2018-07-17)

tidb-toolkit-v5.1.1-linux-amd64/bin/dumpling -V
Release version: v5.1.1
Git commit hash: 279359a4b81d48dd21692c0a7c17d73f6d313b4a
Git branch: heads/refs/tags/v5.1.1
Build timestamp: 2021-07-23 01:15:14Z
Go version: go version go1.16.4 linux/amd64

【Encountered Problem】
When dealing with large tables exceeding 100G, it always reports:
[mysql] 2022/07/26 20:14:52 packets.go:36: read tcp 127.0.0.1:43191->127.0.0.1:10080: i/o timeout

[2022/07/26 20:14:52.438 +08:00] [ERROR] [dump.go:574] [“split chunks - get max min failed”] [query=“SELECT MIN(id),MAX(id) FROM db.tb”] [error=“invalid connection”]
[2022/07/26 20:14:52.483 +08:00] [INFO] [collector.go:195] [“backup failed summary”] [total-ranges=0] [ranges-succeed=0] [ranges-failed=0]
[2022/07/26 20:29:54.917 +08:00] [ERROR] [main.go:77] [“dump failed error stack info”] [error=“invalid connection”] [errorVerbose=“invalid connection
github.com/pingcap/errors.AddStack
\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174
github.com/pingcap/errors.Trace
\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/juju_adaptor.go:15
github.com/pingcap/dumpling/v4/export.(*Dumper).selectMinAndMaxIntValue
\tgithub.com/pingcap/dumpling/v4/export/dump.go:575
github.com/pingcap/dumpling/v4/export.(*Dumper).concurrentDumpTable
\tgithub.com/pingcap/dumpling/v4/export/dump.go:504
github.com/pingcap/dumpling/v4/export.(*Dumper).buildConcatTask.func1
\tgithub.com/pingcap/dumpling/v4/export/dump.go:367
runtime.goexit
\truntime/asm_amd64.s:1371”]

However, when directly logging in and executing:
SELECT MIN(id),MAX(id) FROM db.tb

It takes 19 minutes.

【Reproduction Path】
mysql_backup/tidb-toolkit-v5.1.1-linux-amd64/bin/dumpling -h 127.0.0.1 -B db --no-schemas --params “character_set_client=utf8,character_set_connection=utf8” --filetype sql -t 1 -o mysql_backup/db

【Problem Phenomenon and Impact】
Dumping export fails as described above.

【Attachments】

  • Relevant logs, configuration files, Grafana monitoring (https://metricstool.pingcap.com/)
  • TiUP Cluster Display information
  • TiUP Cluster Edit config information
  • TiDB-Overview monitoring
  • Corresponding module Grafana monitoring (if any, such as BR, TiDB-binlog, TiCDC, etc.)
  • Corresponding module logs (including logs one hour before and after the issue)

If the question is related to performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output.

2
| username: Mark | Original post link

It looks like it was caused by a timeout. Increase the timeout setting and also limit the number of lines per file.

3
| username: wonderful1984 | Original post link

Which timeout parameter value should be set?

+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_rollback_on_timeout     | OFF      |
| interactive_timeout            | 28800    |
| net_write_timeout              | 3600     |
| tidb_wait_split_region_timeout | 300      |
| slave_net_timeout              | 3600     |
| net_read_timeout               | 3600     |
| connect_timeout                | 3600     |
| rpl_semi_sync_master_timeout   |          |
| innodb_flush_log_at_timeout    | 1        |
| innodb_lock_wait_timeout       | 50       |
| delayed_insert_timeout         | 300      |
| rpl_stop_slave_timeout         | 31536000 |
| wait_timeout                   | 28800    |
| lock_wait_timeout              | 31536000 |
+--------------------------------+----------+
4
| username: banana_jian | Original post link

dumpling --read-timeout=1h
Set a timeout of one hour.

5
| username: Mark | Original post link

It is recommended to adjust one parameter at a time to identify the key issue. Start with the -r limit, then limit the file size, timeout, and GC time, excluding them one by one.

6
| username: Mark | Original post link

The version of dumpling is a bit low. If the error persists, switch to the mydumper tool, or consider upgrading the dumpling tool.

7
| username: buchuitoudegou | Original post link

An “invalid connection” means reading a disconnected database connection. You can try the solution mentioned above.

However, both TiDB and dumpling versions are a bit outdated, so if it’s really a bug in the tool, it might be hard to resolve :joy:

8
| username: wonderful1984 | Original post link

Currently, we are considering upgrading through the export-import method.

9
| username: lance6716 | Original post link

This query operation has a fixed 15-minute timeout. Is there no index on your table that can speed it up?

10
| username: cs58_dba | Original post link 
# Set the upper limit for long queries. If there is a query longer than this, mydumper will exit. You can also set it to kill the long query.
#mydumper -u leshami -p pwd -B sakila --long-query-guard 200 --kill-long-queries
11
| username: h5n1 | Original post link

What does the structure of the large table look like?

12
| username: wonderful1984 | Original post link

The id is an auto-increment primary key. Remember that in TiDB 4.x and below, select min, max from tb performs a full table scan. min and max cannot be used simultaneously; they need to be split into two separate SQL statements. Only select min from tb; select max from tb; is the fastest.

13
| username: banana_jian | Original post link

Isn’t setting a timeout useful?

14
| username: wonderful1984 | Original post link

This is useful, thank you, thank you :+1:

15
| username: banana_jian | Original post link

:blush: As long as it’s useful, no need to be polite.

16
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.