Statistics Collection Failed with Error: [tikv:1317] Query execution was interrupted

Translated
1

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

Original topic: 统计信息收集失败显示：[tikv:1317]Query execution was interrupted错误

| username: 麻烦是朋友

[TiDB Usage Environment]
Test/PoC

[TiDB Version & Cluster Information]
Cluster version: v6.5.0
Deploy user: tidb
SSH type: builtin
Dashboard URL: http://xxx.xxx.xxx.xxx:2379/dashboard
Grafana URL: http://xxx.xxx.xxx.xxx:3000

ID Role Host Ports OS/Arch Status Data Dir Deploy Dir

xxx.xxx.xxx.xxx:9093 alertmanager xxx.xxx.xxx.xxx 9093/9094 linux/x86_64 Up /tidb/alertmanager-9093/data /tidb/alertmanager-9093
xxx.xxx.xxx.xxx:8300 cdc xxx.xxx.xxx.xxx 8300 linux/x86_64 Up /tidb/cdc-data/cdc-8300 /tidb/cdc-deploy/cdc-8300
xxx.xxx.xxx.xxx:8310 cdc xxx.xxx.xxx.xxx 8310 linux/x86_64 Up /tidb/cdc-data/cdc-8310 /tidb/cdc-deploy/cdc-8310
xxx.xxx.xxx.xxx:8300 cdc xxx.xxx.xxx.xxx 8300 linux/x86_64 Up /tidb/cdc-data/cdc-8300 /tidb/cdc-deploy/cdc-8300
xxx.xxx.xxx.xxx:8310 cdc xxx.xxx.xxx.xxx 8310 linux/x86_64 Up /tidb/cdc-data/cdc-8310 /tidb/cdc-deploy/cdc-8310
xxx.xxx.xxx.xxx:3000 grafana xxx.xxx.xxx.xxx 3000 linux/x86_64 Up - /tidb/grafana-3000
xxx.xxx.xxx.xxx:2379 pd xxx.xxx.xxx.xxx 2379/2380 linux/x86_64 Up /tidb/pd/data /tidb/pd
xxx.xxx.xxx.xxx:2379 pd xxx.xxx.xxx.xxx 2379/2380 linux/x86_64 Up|L|UI /tidb/pd/data /tidb/pd
xxx.xxx.xxx.xxx:2379 pd xxx.xxx.xxx.xxx 2379/2380 linux/x86_64 Up /tidb/pd/data /tidb/pd
xxx.xxx.xxx.xxx:9090 prometheus xxx.xxx.xxx.xxx 9090/12020 linux/x86_64 Up /tidb/prometheus-8249/data /tidb/prometheus-8249
xxx.xxx.xxx.xxx:5001 tidb xxx.xxx.xxx.xxx 5001/10081 linux/x86_64 Up - /tidb/tidb1
xxx.xxx.xxx.xxx:5002 tidb xxx.xxx.xxx.xxx 5002/10082 linux/x86_64 Up - /tidb/tidb2
xxx.xxx.xxx.xxx:5003 tidb xxx.xxx.xxx.xxx 5003/10083 linux/x86_64 Up - /tidb/tidb3
xxx.xxx.xxx.xxx:5004 tidb xxx.xxx.xxx.xxx 5004/10084 linux/x86_64 Up - /tidb/tidb4
xxx.xxx.xxx.xxx:5001 tidb xxx.xxx.xxx.xxx 5001/10081 linux/x86_64 Up - /tidb/tidb1
xxx.xxx.xxx.xxx:5002 tidb xxx.xxx.xxx.xxx 5002/10082 linux/x86_64 Up - /tidb/tidb2
xxx.xxx.xxx.xxx:5003 tidb xxx.xxx.xxx.xxx 5003/10083 linux/x86_64 Up - /tidb/tidb3
xxx.xxx.xxx.xxx:5004 tidb xxx.xxx.xxx.xxx 5004/10084 linux/x86_64 Up - /tidb/tidb4
xxx.xxx.xxx.xxx:5001 tidb xxx.xxx.xxx.xxx 5001/10081 linux/x86_64 Up - /tidb/tidb1
xxx.xxx.xxx.xxx:5002 tidb xxx.xxx.xxx.xxx 5002/10082 linux/x86_64 Up - /tidb/tidb2
xxx.xxx.xxx.xxx:5001 tidb xxx.xxx.xxx.xxx 5001/10081 linux/x86_64 Up - /tidb/tidb1
xxx.xxx.xxx.xxx:5002 tidb xxx.xxx.xxx.xxx 5002/10082 linux/x86_64 Up - /tidb/tidb2
xxx.xxx.xxx.xxx:5001 tidb xxx.xxx.xxx.xxx 5001/10081 linux/x86_64 Up - /tidb/tidb1
xxx.xxx.xxx.xxx:5002 tidb xxx.xxx.xxx.xxx 5002/10082 linux/x86_64 Up - /tidb/tidb2
xxx.xxx.xxx.xxx:9000 tiflash xxx.xxx.xxx.xxx 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /data01/tiflash,/data02/tiflash,/data03/tiflash /tidb/tiflash-9000
xxx.xxx.xxx.xxx:9000 tiflash xxx.xxx.xxx.xxx 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /data01/tiflash,/data02/tiflash,/data03/tiflash /tidb/tiflash-9000
xxx.xxx.xxx.xxx:9000 tiflash xxx.xxx.xxx.xxx 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /data01/tiflash,/data02/tiflash,/data03/tiflash /tidb/tiflash-9000
xxx.xxx.xxx.xxx:20171 tikv xxx.xxx.xxx.xxx 20171/20181 linux/x86_64 Up /data01/tikv /tidb/tikv1
xxx.xxx.xxx.xxx:20172 tikv xxx.xxx.xxx.xxx 20172/20182 linux/x86_64 Up /data02/tikv /tidb/tikv2
xxx.xxx.xxx.xxx:20173 tikv xxx.xxx.xxx.xxx 20173/20183 linux/x86_64 Up /data03/tikv /tidb/tikv3
xxx.xxx.xxx.xxx:20171 tikv xxx.xxx.xxx.xxx 20171/20181 linux/x86_64 Up /data01/tikv /tidb/tikv1
xxx.xxx.xxx.xxx:20172 tikv xxx.xxx.xxx.xxx 20172/20182 linux/x86_64 Up /data02/tikv /tidb/tikv2
xxx.xxx.xxx.xxx:20173 tikv xxx.xxx.xxx.xxx 20173/20183 linux/x86_64 Up /data03/tikv /tidb/tikv3
xxx.xxx.xxx.xxx:20171 tikv xxx.xxx.xxx.xxx 20171/20181 linux/x86_64 Up /data01/tikv /tidb/tikv1
xxx.xxx.xxx.xxx:20172 tikv xxx.xxx.xxx.xxx 20172/20182 linux/x86_64 Up /data02/tikv /tidb/tikv2
xxx.xxx.xxx.xxx:20173 tikv xxx.xxx.xxx.xxx 20173/20183 linux/x86_64 Up /data03/tikv /tidb/tikv3
xxx.xxx.xxx.xxx:20171 tikv xxx.xxx.xxx.xxx 20171/20181 linux/x86_64 Up /data01/tikv /tidb/tikv1
xxx.xxx.xxx.xxx:20172 tikv xxx.xxx.xxx.xxx 20172/20182 linux/x86_64 Up /data02/tikv /tidb/tikv2
xxx.xxx.xxx.xxx:20173 tikv xxx.xxx.xxx.xxx 20173/20183 linux/x86_64 Up /data03/tikv /tidb/tikv3
xxx.xxx.xxx.xxx:20171 tikv xxx.xxx.xxx.xxx 20171/20181 linux/x86_64 Up /data01/tikv /tidb/tikv1
xxx.xxx.xxx.xxx:20172 tikv xxx.xxx.xxx.xxx 20172/20182 linux/x86_64 Up /data02/tikv /tidb/tikv2
xxx.xxx.xxx.xxx:20173 tikv xxx.xxx.xxx.xxx 20173/20183 linux/x86_64 Up /data03/tikv /tidb/tikv3

[Issue Manifestation]
Failed to collect statistics for large table:
xxxxx xxxxxxx auto analyze table all columns with 256 buckets, 500 topn, 4.4275116274756925e-05 samplerate 2027279994 2023-04-02 18:18:27 2023-04-03 06:18:27 failed [tikv:1317]Query execution was interrupted 172.17.3.111:5002
Data volume in the table:
24 8795 7795
Database GC parameters: tidb_gc_life_time 24 hours.

tidb_enable_gc_aware_memory_track OFF
tidb_enable_gogc_tuner ON
tidb_gc_concurrency -1
tidb_gc_enable ON
tidb_gc_life_time 24h0m0s
tidb_gc_max_wait_time 86400
tidb_gc_run_interval 10m0s
tidb_gc_scan_lock_mode LEGACY
tidb_gogc_tuner_threshold 0.6
tidb_server_memory_limit_gc_trigger 0.7

Is there any good optimization method for collecting statistics on large tables?

2
| username: 裤衩儿飞上天 | Original post link

Scheduled manual analysis

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

It is recommended to collect manually. You can refer to the database’s busy level and choose less busy periods, and increase concurrency for collection. https://docs.pingcap.com/zh/tidb/stable/statistics#控制-analyze-并发度

Additionally, you don’t have to collect the entire table. You can set an appropriate sampling rate for collection. WITH FLOAT_NUM SAMPLERATE is used to specify the sampling rate. https://docs.pingcap.com/zh/tidb/stable/statistics#全量收集

4
| username: xingzhenxiang | Original post link

Stop automatic collection

set global tidb_auto_analyze_end_time ='01:00 +0000'; // Takes effect immediately
set global tidb_max_auto_analyze_time = 600; // Takes effect immediately, any process exceeding this time before setting will also be killed;

Set concurrency parameters

set global tidb_build_stats_concurrency = 8; // This variable is used to set the concurrency level when executing the ANALYZE statement.

Perform manual collection

analyze table `Table_schema`.`Table_name`; // Manual speed is much faster than automatic, in 15 minutes it collected more data than the automatic process did in 3 hours.
5
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.