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_JOGvpu4t7dssySqJfTtS4A==_1635750890568691080.zip |
+------------------------------------------------------------------+
1 row in set (0.015 sec)
Alternatively, you can use the session variable tidb_last_plan_replayer_token to obtain the result of the last PLAN REPLAYER DUMP execution.
SELECT @@tidb_last_plan_replayer_token;
+-----------------------------------------------------------+
| @@tidb_last_plan_replayer_token |
+-----------------------------------------------------------+
| replayer_Fdamsm3C7ZiPJ-LQqgVjkA==_1663304195885090000.zip |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
When there are multiple SQL statements, you can obtain the result of the PLAN REPLAYER DUMP execution using a file. The results of multiple SQL statements are separated by ; in this file.
plan replayer dump explain 'sqls.txt';
Query OK, 0 rows affected (0.03 sec)
SELECT @@tidb_last_plan_replayer_token;
+-----------------------------------------------------------+
| @@tidb_last_plan_replayer_token |
+-----------------------------------------------------------+
| replayer_LEDKg8sb-K0u24QesiH8ig==_1663226556509182000.zip |
+-----------------------------------------------------------+
1 row in set (0.00 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_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.