ANALYZE_STATUS

ANALYZE_STATUS 表提供正在执行的收集统计信息的任务以及有限条历史任务记录。

从 TiDB v6.1.0 起,ANALYZE_STATUS 表显示集群级别的任务,且 TiDB 重启后仍能看到重启之前的任务记录。在 TiDB v6.1.0 之前,ANALYZE_STATUS 仅显示实例级别的任务,且 TiDB 重启后任务记录会被清空。

从 TiDB v6.1.0 起,可以通过系统表 mysql.analyze_jobs 查看过去 7 天内的历史任务记录。

USE information_schema; DESC analyze_status;
+----------------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------------+------+------+---------+-------+ | TABLE_SCHEMA | varchar(64) | YES | | NULL | | | TABLE_NAME | varchar(64) | YES | | NULL | | | PARTITION_NAME | varchar(64) | YES | | NULL | | | JOB_INFO | longtext | YES | | NULL | | | PROCESSED_ROWS | bigint(64) unsigned | YES | | NULL | | | START_TIME | datetime | YES | | NULL | | | END_TIME | datetime | YES | | NULL | | | STATE | varchar(64) | YES | | NULL | | | FAIL_REASON | longtext | YES | | NULL | | | INSTANCE | varchar(512) | YES | | NULL | | | PROCESS_ID | bigint(64) unsigned | YES | | NULL | | | REMAINING_SECONDS | bigint(64) unsigned | YES | | NULL | | | PROGRESS | varchar(20) | YES | | NULL | | | ESTIMATED_TOTAL_ROWS | bigint(64) unsigned | YES | | NULL | | +----------------------+---------------------+------+------+---------+-------+ 14 rows in set (0.00 sec)
SELECT * FROM information_schema.analyze_status;
+--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+----------------------+----------+-----------------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | JOB_INFO | PROCESSED_ROWS | START_TIME | END_TIME | STATE | FAIL_REASON | INSTANCE | PROCESS_ID | REMAINING_SECONDS | PROGRESS | ESTIMATED_TOTAL_ROWS | +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+----------------------+----------+-----------------------+ | test | t | p1 | analyze table all columns with 256 buckets, 500 topn, 1 samplerate | 0 | 2022-05-27 11:30:12 | 2022-05-27 11:30:12 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p0 | analyze table all columns with 256 buckets, 500 topn, 1 samplerate | 0 | 2022-05-27 11:30:12 | 2022-05-27 11:30:12 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p1 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p0 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p1 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p0 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL | | test | t | p1 | analyze table all columns with 256 buckets, 500 topn, 1 samplerate | 1000000 | 2022-05-27 11:30:12 | 2022-05-27 11:40:12 | running | NULL | 127.0.0.1:4000 | 690208308 | 600s | 0.25 | 4000000 | +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+----------------------+----------+-----------------------+ 6 rows in set (0.00 sec)

ANALYZE_STATUS 表中列的含义如下:

  • TABLE_SCHEMA:表所属的数据库的名称。
  • TABLE_NAME:表的名称。
  • PARTITION_NAME:分区表的名称。
  • JOB_INFOANALYZE 任务的信息。如果分析索引,该信息会包含索引名。当 tidb_analyze_version = 2 时,该信息会包含采样率等配置项。
  • PROCESSED_ROWS:已经处理的行数。
  • START_TIMEANALYZE 任务的开始时间。
  • END_TIMEANALYZE 任务的结束时间。
  • STATEANALYZE 任务的执行状态。其值可以是 pendingrunningfinished 或者 failed
  • FAIL_REASON:任务失败的原因。如果执行成功则为 NULL
  • INSTANCE:执行任务的 TiDB 实例。
  • PROCESS_ID:执行任务的 process ID。
  • REMAINING_SECONDS:执行任务预计还需要多少秒才能完成。
  • PROGRESS:执行任务的进度。
  • ESTIMATED_TOTAL_ROWS:执行任务需要分析的总行数。