Analyze Table xxx Index Failed

Translated
1

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

Original topic: analyze table xxx index failed

| username: 特雷西-迈克-格雷迪

【TiDB Usage Environment】Production Environment
【TiDB Version】6.1.2
【Reproduction Path】Created an index for a large table, and an error was prompted in the log, analyze table failed
【Encountered Problem: Phenomenon and Impact】

2022-12-15 00:17:56 (GMT+8)
TiDB 12.0.0.88:4000
[update.go:1218] [“[stats] auto analyze failed”] [sql=“analyze table ssss.tab_xxxx index idx_custCode”] [cost_time=19m58.677442321s] [error=“[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-12-14 23:57:58.2 +0800 CST, GC safe point is 2022-12-15 00:07:40.851 +0800 CST”]

Isn’t this parameter available in version 6.1: tidb_gc_max_wait_time: This variable is used to specify the maximum time that active transactions can block the advancement of the GC safe point.
Why is there still an error?
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

2
| username: Hacker_小峰 | Original post link

You can try adjusting the value of tikv_gc_life_time in the mysql.tidb table in TiDB to increase the MVCC version retention time.
For more details, see the official documentation: TiDB 中的各种超时 | PingCAP 文档中心

image
image1380×650 154 KB

I don’t know why the new variable didn’t take effect either…

3
| username: jansu-dev | Original post link

This variable is used to specify the maximum time that active transactions can block the advancement of the GC safe point. The operation performed is to kill the transactions that exceed the specified time.

4
| username: Raymond | Original post link

What value did you set for tidb_gc_max_wait_time? According to the logs, the statement only executed for 19 minutes.

5
| username: gcworkerishungry | Original post link

Refer to: 持续执行ANALYZE TABLE好几天 - #21，来自 Raymond - TiDB 的问答社区.

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

The parameter tidb_gc_max_wait_time is the maximum time the system can tolerate waiting for GC. Your error is actually due to insufficient GC activity time. You should increase the tidb_gc_life_time parameter. It seems that your table data is changing too quickly, and the index is constantly changing. The action of collecting statistics has not been completed and has already exceeded the tidb_gc_life_time. It is recommended to increase it and try again.

7
| username: weixiaobing | Original post link

For large tables, it is recommended to manually collect using scripts. The concurrency of automatic collection cannot be adjusted, and the speed is relatively slow.

8
| username: weixiaobing | Original post link

Please take a look at this parameter

tidb_max_auto_analyze_time Introduced from version v6.1.0

  • Scope: GLOBAL
  • Persisted to cluster: Yes
  • Default value: 43200
  • Range: [0, 2147483647]
  • Unit: seconds
  • This variable is used to specify the maximum execution time for automatic ANALYZE. When the execution time exceeds the specified time, the automatic ANALYZE will be terminated. When the value of this variable is 0, there is no maximum execution time limit for automatic ANALYZE.
9
| username: system | Original post link

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