4
2
3
0
专栏/.../

TiDB PLAN REPLAYER 功能使用实践

 paulli  发表于  2024-02-29

一、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

4
2
3
0

版权声明:本文为 TiDB 社区用户原创文章,遵循 CC BY-NC-SA 4.0 版权协议,转载请附上原文出处链接和本声明。

评论
暂无评论