[stats] Auto Analyze Failed

translator_bot · June 22, 2024, 4:59pm

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

Original topic: [stats] auto analyze failed

| username: xie123

[TiDB Usage Environment] Production Environment
[TiDB Version]
v5.4.0
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
TiDB logs keep showing
[stats] auto analyze failed"] [sql=“analyze table DB.TABLE”] [cost_time=17.282654928s] [error=“[kv:8004]Transaction is too large, size: 105054135”]

Currently, the table no longer exists, and referring to the following link, drop stat didn’t work. What impact does setting the stats-lease parameter to 0 have? Is there any other way to prevent it from logging errors?

github.com/pingcap/tidb

analyze large table failed，and tidb-server will do analysis until success，when gc life time is short

opened 07:29AM - 21 Mar 22 UTC

closed 12:03PM - 20 Apr 22 UTC

lddlww

type/bug sig/planner severity/major may-affects-4.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-5.0

## Bug Report tidb-server analyze one table which size is large，and analyze o…p will failed，then tidb-server will do analysis until success，this will result in large receive traffic in tidb-server ### 1. Minimal reproduce step (Required) no ### 2. What did you expect to see? (Required) tidb-server always do analysis until success ### 3. What did you see instead (Required) tidb-server will auto adjust gc life time to large table when analyze table ### 4. What is your TiDB version? (Required) v5.3.0 tidb-server.log: ``` [2022/03/12 09:12:53.878 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5079512/1.015878e+07>0.5)"] [2022/03/12 09:24:55.637 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 09:12:53.878 +08:00] ["end time"=2022/03/12 09:24:55.637 +08:00] [cost=12m1.758415741s] [2022/03/12 09:24:56.292 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 09:24:55.636 +08:00] ["end time"=2022/03/12 09:24:56.292 +08:00] [cost=656.073854ms] [2022/03/12 09:24:56.503 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 09:24:56.292 +08:00] ["end time"=2022/03/12 09:24:56.503 +08:00] [cost=210.846921ms] [2022/03/12 09:24:57.650 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 09:24:56.503 +08:00] ["end time"=2022/03/12 09:24:57.650 +08:00] [cost=1.146822065s] [2022/03/12 09:24:57.650 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=12m3.772639707s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 09:12:53.866 +0800 CST, GC safe point is 2022-03-12 09:14:47.866 +0800 CST"] [2022/03/12 09:24:57.654 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5079512/1.015878e+07>0.5)"] [2022/03/12 09:44:57.647 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 09:24:57.654 +08:00] ["end time"=2022/03/12 09:44:57.647 +08:00] [cost=19m59.992030597s] [2022/03/12 09:44:58.287 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 09:44:57.646 +08:00] ["end time"=2022/03/12 09:44:58.287 +08:00] [cost=640.718599ms] [2022/03/12 09:44:58.490 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 09:44:58.287 +08:00] ["end time"=2022/03/12 09:44:58.490 +08:00] [cost=203.045258ms] [2022/03/12 09:44:59.623 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 09:44:58.490 +08:00] ["end time"=2022/03/12 09:44:59.623 +08:00] [cost=1.132833191s] [2022/03/12 09:44:59.623 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=20m1.969094961s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 09:24:57.615 +0800 CST, GC safe point is 2022-03-12 09:34:47.866 +0800 CST"] [2022/03/12 09:44:59.627 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5081252/1.015878e+07>0.5)"] [2022/03/12 10:04:59.013 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 09:44:59.627 +08:00] ["end time"=2022/03/12 10:04:59.013 +08:00] [cost=19m59.385525484s] [2022/03/12 10:04:59.673 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 10:04:59.012 +08:00] ["end time"=2022/03/12 10:04:59.673 +08:00] [cost=660.161927ms] [2022/03/12 10:04:59.876 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 10:04:59.673 +08:00] ["end time"=2022/03/12 10:04:59.876 +08:00] [cost=203.35878ms] [2022/03/12 10:05:01.034 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 10:04:59.876 +08:00] ["end time"=2022/03/12 10:05:01.034 +08:00] [cost=1.157870254s] [2022/03/12 10:05:01.034 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=20m1.40748911s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 09:44:59.616 +0800 CST, GC safe point is 2022-03-12 09:54:47.865 +0800 CST"] [2022/03/12 10:05:01.038 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5082988/1.015878e+07>0.5)"] [2022/03/12 10:25:00.350 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 10:05:01.039 +08:00] ["end time"=2022/03/12 10:25:00.350 +08:00] [cost=19m59.311183017s] [2022/03/12 10:25:01.075 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 10:25:00.350 +08:00] ["end time"=2022/03/12 10:25:01.075 +08:00] [cost=724.820285ms] [2022/03/12 10:25:01.270 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 10:25:01.075 +08:00] ["end time"=2022/03/12 10:25:01.270 +08:00] [cost=195.312692ms] [2022/03/12 10:25:02.456 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 10:25:01.270 +08:00] ["end time"=2022/03/12 10:25:02.456 +08:00] [cost=1.186300445s] [2022/03/12 10:25:02.457 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=20m1.418291177s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 10:05:01.015 +0800 CST, GC safe point is 2022-03-12 10:14:47.866 +0800 CST"] [2022/03/12 10:25:02.461 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5082988/1.015878e+07>0.5)"] [2022/03/12 10:45:01.950 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 10:25:02.462 +08:00] ["end time"=2022/03/12 10:45:01.950 +08:00] [cost=19m59.487746958s] [2022/03/12 10:45:02.631 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 10:45:01.950 +08:00] ["end time"=2022/03/12 10:45:02.631 +08:00] [cost=681.3097ms] [2022/03/12 10:45:02.837 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 10:45:02.631 +08:00] ["end time"=2022/03/12 10:45:02.837 +08:00] [cost=205.913852ms] [2022/03/12 10:45:03.966 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 10:45:02.837 +08:00] ["end time"=2022/03/12 10:45:03.966 +08:00] [cost=1.129559762s] [2022/03/12 10:45:03.966 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=20m1.505077085s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 10:25:02.415 +0800 CST, GC safe point is 2022-03-12 10:34:47.866 +0800 CST"] [2022/03/12 10:45:03.971 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5082988/1.015878e+07>0.5)"] [2022/03/12 11:05:03.416 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 10:45:03.971 +08:00] ["end time"=2022/03/12 11:05:03.416 +08:00] [cost=19m59.444767033s] [2022/03/12 11:05:04.132 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 11:05:03.416 +08:00] ["end time"=2022/03/12 11:05:04.132 +08:00] [cost=716.319883ms] [2022/03/12 11:05:04.319 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 11:05:04.132 +08:00] ["end time"=2022/03/12 11:05:04.319 +08:00] [cost=186.763278ms] [2022/03/12 11:05:05.457 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 11:05:04.319 +08:00] ["end time"=2022/03/12 11:05:05.457 +08:00] [cost=1.138190954s] [2022/03/12 11:05:05.457 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=20m1.486454878s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 10:45:03.965 +0800 CST, GC safe point is 2022-03-12 10:54:47.866 +0800 CST"] [2022/03/12 11:05:05.877 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5084719/1.015878e+07>0.5)"] [2022/03/12 11:24:55.267 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze columns"] ["start time"=2022/03/12 11:05:05.878 +08:00] ["end time"=2022/03/12 11:24:55.267 +08:00] [cost=19m49.389153925s] [2022/03/12 11:24:55.950 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_order_serial_number"] ["start time"=2022/03/12 11:24:55.267 +08:00] ["end time"=2022/03/12 11:24:55.950 +08:00] [cost=683.710463ms] [2022/03/12 11:24:56.167 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5"] ["start time"=2022/03/12 11:24:55.950 +08:00] ["end time"=2022/03/12 11:24:56.167 +08:00] [cost=216.737852ms] [2022/03/12 11:24:57.338 +08:00] [INFO] [analyze.go:129] ["analyze table `db`.`detail` has failed"] [partition=] ["job info"="auto analyze index idx_md5_create"] ["start time"=2022/03/12 11:24:56.167 +08:00] ["end time"=2022/03/12 11:24:57.338 +08:00] [cost=1.170859545s] [2022/03/12 11:24:57.338 +08:00] [ERROR] [update.go:1091] ["[stats] auto analyze failed"] [sql="analyze table `db`.`detail`] [cost_time=19m51.460990431s] [error="[tikv:9006]GC life time is shorter than transaction duration, transaction starts at 2022-03-12 11:05:05.866 +0800 CST, GC safe point is 2022-03-12 11:14:47.866 +0800 CST"] [2022/03/12 11:24:57.343 +08:00] [INFO] [update.go:992] ["[stats] auto analyze triggered"] [sql="analyze table `db`.`detail`] [reason="too many modifications(5086443/1.015878e+07>0.5)"] ```

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

translator_bot · June 22, 2024, 4:59pm

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

Before v6.1.0, this switch was configured through the TiDB configuration file (performance.run-auto-analyze) to determine whether TiDB automatically updates table statistics as a background operation. It is recommended to disable automatic statistics collection, or disable it by setting the parameter SET GLOBAL tidb_auto_analyze_ratio = 0;, and then manually deploy tasks to collect statistics for the tables that need it.

translator_bot · June 22, 2024, 4:59pm

| username: xie123 | Original post link

What issues might arise from disabling this statistic?

When insert, delete, and update statements occur, TiDB automatically updates the total number of rows in the table and the number of modified rows. This information is periodically persisted, with an update cycle of 20 * stats-lease. The default value of stats-lease is 3 seconds. If it is set to 0, it will not be automatically updated.

translator_bot · June 22, 2024, 4:59pm

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

When stats-lease is set to 0s, TiDB will periodically read the statistics from the system tables and update the cached statistics in memory at 3-second intervals. However, it will not automatically modify the related system tables for statistics. Specifically, TiDB will no longer automatically modify these tables:

mysql.stats_meta: TiDB will no longer automatically record the number of rows modified in a table during a transaction, nor will it update this system table.
mysql.stats_histograms/mysql.stats_buckets and mysql.stats_top_n: TiDB will no longer automatically analyze and proactively update statistics.
mysql.stats_feedback: TiDB will no longer update the statistics of tables and indexes based on the partial statistics feedback from queried data.

translator_bot · June 22, 2024, 4:59pm

| username: dba-kit | Original post link

This should be a bug in TiDB. However, if the table doesn’t exist and you just want to stop refreshing this table to avoid errors, you might try restarting the tidb-server that keeps reporting errors to clear the execution queue in memory.