一、PLAN REPLAYER 功能
PLAN REPLAYER 主要功能如下：
- 导出排查现场 TiDB 集群的相关信息，导出为 ZIP 格式的文件用于保存。
- 在任意 TiDB 集群上导入另一 TiDB 集群现场信息的 ZIP 文件。
PLAN REPLAYER 收集文件信息如下：
- TiDB 版本信息
- TiDB 配置信息
- TiDB Session 系统变量
- TiDB 执行计划绑定信息（SQL Binding）
sql-statement中所包含的表结构sql-statement中所包含表的统计信息EXPLAIN [ANALYZE] sql-statement的结果- 优化器进行查询优化的一些内部步骤的记录
二、PLAN REPLAYER 使用实践
导出示例
单条语句
[tidb@tidb53 paul]$ mysql -h 172.20.12.53 -P8100 -u root -proot -c
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 725
Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> plan replayer dump explain select * from t1 where id = 1;
+-----------------------------------------------------------+
| File_token |
+-----------------------------------------------------------+
| replayer_Wy-FoPDy-CC5Jcg9j0XjJQ==_1709172046521026575.zip |
+-----------------------------------------------------------+
1 row in set (0.06 sec)
多条语句
//编辑SQL文件，多条语句用";"隔开
[tidb@tidb53 paul]$ cat sqls.txt
select count(*) from t2;
select * from t2;
//连接数据库，同时指定local-infile
[tidb@tidb53 paul]$ mysql -h 172.20.12.53 -P8100 -u root -proot -c --local-infile=1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 747
Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> plan replayer dump explain '/home/tidb/paul/sqls.txt';
Query OK, 1 row affected (0.07 sec)
mysql>
mysql> SELECT @@tidb_last_plan_replayer_token;
+-----------------------------------------------------------+
| @@tidb_last_plan_replayer_token |
+-----------------------------------------------------------+
| replayer_2QmeXhRZorD7qZwvqbbqNw==_1709176694041070533.zip |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
下载示例
通过TiDB HTTP 接口和文件标识下载文件，建议在指定tidb-server执行plan replayer dump，就是指定当前的tidb-server导出即可。
语法：http://${tidb-server-ip}:${tidb-server-status-port}/plan_replayer/dump/${file_token}
curl http://172.20.12.53:18180/plan_replayer/dump/replayer_Wy-FoPDy-CC5Jcg9j0XjJQ==_1709172046521026575.zip > plan_replayer.zip
导入示例
//连接数据库，同时指定local-infile
[tidb@tidb53 paul]$ mysql -h 172.20.12.53 -P8100 -u root -proot -c --local-infile=1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 749
Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> PLAN REPLAYER LOAD '/home/tidb/paul/plan_replayer.zip';
Query OK, 0 rows affected (0.67 sec)
查看执行计划
mysql> explain select * from t1 where id = 1;
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_1 | 1.00 | root | table:t1 | handle:1 |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)
mysql> desc t1;
+-------+---------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| pad1 | blob | YES | | NULL | |
| pad2 | blob | YES | | NULL | |
| pad3 | blob | YES | | NULL | |
+-------+---------+------+------+---------+----------------+
4 rows in set (0.00 sec)
其他使用技巧
//查看上一个token
mysql> SELECT @@tidb_last_plan_replayer_token;
+-----------------------------------------------------------+
| @@tidb_last_plan_replayer_token |
+-----------------------------------------------------------+
| replayer_2QmeXhRZorD7qZwvqbbqNw==_1709176694041070533.zip |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
常见错误
1. 连接数据库指定local-infile参数
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
2. 建表语句报错，可能有建表语句里面字符与现有分隔符冲突导致
ERROR 1105 (HY000): plan replayer: create schema and tables failed
3. 下载的包可能有问题，建议在指定执行dump的tidb-server进行下载
ERROR 1105 (HY000): zip :not a valid zip