SHOW ANALYZE STATUS

The SHOW ANALYZE STATUS statement shows the statistics collection tasks being executed by TiDB and a limited number of historical task records.

Starting from TiDB v6.1.0, the SHOW ANALYZE STATUS statement supports showing cluster-level tasks. Even after a TiDB restart, you can still view task records before the restart using this statement. Before TiDB v6.1.0, the SHOW ANALYZE STATUS statement can only show instance-level tasks, and task records are cleared after a TiDB restart.

Starting from TiDB v6.1.0, you can view the history tasks within the last 7 days through the system table mysql.analyze_jobs.

Starting from TiDB v7.3.0, you can view the progress of the current ANALYZE task through the system table mysql.analyze_jobs or SHOW ANALYZE STATUS.

Currently, the SHOW ANALYZE STATUS statement returns the following columns:

Column nameDescription
Table_schemaThe database name
Table_nameThe table name
Partition_nameThe partition name
Job_infoThe task information. If an index is analyzed, this information will include the index name. When tidb_analyze_version =2, this information will include configuration items such as sample rate.
Processed_rowsThe number of rows that have been analyzed
Start_timeThe time at which the task starts
StateThe state of a task, including pending, running, finished, and failed
Fail_reasonThe reason why the task fails. If the execution is successful, the value is NULL.
InstanceThe TiDB instance that executes the task
Process_idThe process ID that executes the task

Synopsis

ShowAnalyzeStatusStmt
SHOWANALYZESTATUSShowLikeOrWhereOpt
ShowLikeOrWhereOpt
LIKESimpleExprWHEREExpression

Examples

mysql> create table t(x int, index idx(x)) partition by hash(x) partitions 2; Query OK, 0 rows affected (0.69 sec) mysql> set @@tidb_analyze_version = 1; Query OK, 0 rows affected (0.00 sec) mysql> analyze table t; Query OK, 0 rows affected (0.20 sec) mysql> show 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 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 | t1 | p0 | analyze columns | 28523259 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | running | NULL | 127.0.0.1:4000 | 690208308 | 0s | 0.9843 | 28978290 | +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+ 4 rows in set (0.01 sec) mysql> set @@tidb_analyze_version = 2; Query OK, 0 rows affected (0.00 sec) mysql> analyze table t; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> show 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 | +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+ 6 rows in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also

Was this page helpful?