TiDB PLAN REPLAYER 功能使用实践
作者：TiDB 社区干货传送门
- 2024-03-01 北京
本文字数：2910 字
阅读完需：约 10 分钟
作者： paulli 原文来源：https://tidb.net/blog/7df0962f
一、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 -cmysql: [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 725Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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=1mysql: [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 747Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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=1mysql: [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 749Server version: 5.7.25-TiDB-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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)
复制代码
其他使用技巧
//查看上一个tokenmysql> 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 failed3. 下载的包可能有问题，建议在指定执行dump的tidb-server进行下载ERROR 1105 (HY000): zip :not a valid zip
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/0da03425c44341d9579e41910】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论