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

SHOW ANALYZE STATUS - 图1

ShowLikeOrWhereOpt

SHOW ANALYZE STATUS - 图2

  1. ShowAnalyzeStatusStmt ::= 'SHOW' 'ANALYZE' 'STATUS' ShowLikeOrWhereOpt
  3. ShowLikeOrWhereOpt ::= 'LIKE' SimpleExpr | 'WHERE' Expression

Examples

  1. mysql> create table t(x int, index idx(x)) partition by hash(x) partitions 2;
  2. Query OK, 0 rows affected (0.69 sec)
  4. mysql> set @@tidb_analyze_version = 1;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> analyze table t;
  8. Query OK, 0 rows affected (0.20 sec)
  10. mysql> show analyze status;
  11. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
  12. | 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|
  13. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
  14. | 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                |
  15. | 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                |
  16. | 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                |
  17. | 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                |
  18. | 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            |
  19. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
  20. 4 rows in set (0.01 sec)
  22. mysql> set @@tidb_analyze_version = 2;
  23. Query OK, 0 rows affected (0.00 sec)
  25. mysql> analyze table t;
  26. Query OK, 0 rows affected, 2 warnings (0.03 sec)
  28. mysql> show analyze status;
  29. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
  30. | 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 |
  31. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
  32. | 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                 |
  33. | 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                 |
  34. | 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                 |
  35. | 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                 |
  36. | 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                 |
  37. | 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                 |
  38. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
  39. 6 rows in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also