Use PLAN REPLAYER to Save and Restore the On-Site Information of a Cluster

When you locate and troubleshoot the issues of a TiDB cluster, you often need to provide information on the system and the execution plan. To help you get the information and troubleshoot cluster issues in a more convenient and efficient way, the PLAN REPLAYER command is introduced in TiDB v5.3.0. This command enables you to easily save and restore the on-site information of a cluster, improves the efficiency of troubleshooting, and helps you more easily archive the issue for management.

The features of PLAN REPLAYER are as follows:

• Exports the information of a TiDB cluster at an on-site troubleshooting to a ZIP-formatted file for storage.
• Imports into a cluster the ZIP-formatted file exported from another TiDB cluster. This file contains the information of the latter TiDB cluster at an on-site troubleshooting.

Use PLAN REPLAYER to export cluster information

You can use PLAN REPLAYER to save the on-site information of a TiDB cluster. The export interface is as follows:

PLAN REPLAYER DUMP EXPLAIN [ANALYZE] sql-statement;

Based on sql-statement, TiDB sorts out and exports the following on-site information:

• TiDB version
• TiDB configuration
• TiDB session variables
• TiDB SQL bindings
• The table schema in sql-statement
• The statistics of the table in sql-statement
• The result of EXPLAIN [ANALYZE] sql-statement

Examples of exporting cluster information

use test;
create table t(a int, b int);
insert into t values(1,1), (2, 2), (3, 3);
analyze table t;

plan replayer dump explain select * from t;

PLAN REPLAYER DUMP packages the table information above into a ZIP file and returns the file identifier as the execution result. This file is a one-time file. After the file is downloaded, TiDB will delete it.

MySQL [test]> plan replayer dump explain select * from t;
+------------------------------------------------------------------+
| Dump_link                                                        |
+------------------------------------------------------------------+
| replayer_single_JOGvpu4t7dssySqJfTtS4A==_1635750890568691080.zip |
+------------------------------------------------------------------+
1 row in set (0.015 sec)

Because the file cannot be downloaded on MySQL Client, you need to use the TiDB HTTP interface and the file identifier to download the file:

http://${tidb-server-ip}:${tidb-server-status-port}/plan_replayer/dump/${file_token}

${tidb-server-ip}:${tidb-server-status-port} is the address of any TiDB server in the cluster. For example:

curl http://127.0.0.1:10080/plan_replayer/dump/replayer_single_JOGvpu4t7dssySqJfTtS4A==_1635750890568691080.zip > plan_replayer.zip

Use PLAN REPLAYER to import cluster information

With an existing ZIP file exported using PLAN REPLAYER, you can use the PLAN REPLAYER import interface to restore the on-site information of a cluster to any other TiDB cluster. The syntax is as follows:

PLAN REPLAYER LOAD 'file_name';

In the statement above, file_name is the name of the ZIP file to be exported.

For example:

PLAN REPLAYER LOAD 'plan_replayer.zip';

After the cluster information is imported, the TiDB cluster is loaded with the required table schema, statistics and other information that affects the construction of the execution plan. You can view the execution plan and verify statistics in the following way:

mysql> desc t;
+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| a     | int(11) | YES  |      | NULL    |       |
| b     | int(11) | YES  |      | NULL    |       |
+-------+---------+------+------+---------+-------+
2 rows in set (0.01 sec)

mysql> explain select * from t where a = 1 or b =1;
+-------------------------+---------+-----------+---------------+--------------------------------------+
| id                      | estRows | task      | access object | operator info                        |
+-------------------------+---------+-----------+---------------+--------------------------------------+
| TableReader_7           | 0.01    | root      |               | data:Selection_6                     |
| └─Selection_6           | 0.01    | cop[tikv] |               | or(eq(test.t.a, 1), eq(test.t.b, 1)) |
|   └─TableFullScan_5     | 6.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo       |
+-------------------------+---------+-----------+---------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          |                | 2022-08-26 15:52:07 |            3 |         6 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.04 sec)

After the scene is loaded and restored, you can diagnose and improve the execution plan for the cluster.