Percona Toolkit部分常用工具的使用

蔚落 2022-12-17 07:13 150阅读 0赞

### pt-query-digest ###

> **TIPS**  
> 官方文档：[pt-query-digest][]

*  作用

分析日志（包括binlog、General log、slowlog）、processlist以及tcpdump中的查询

*  语法

pt-query-digest [OPTIONS] [FILES] [DSN]

*  常用OPTIONS

--create-review-table			当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
    --create-history-table		当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
    --filter									输出符合条件的内容
    --limit    								限制输出的百分比或数量。可指定百分比或数字，例如90%表示按响应时间从小到大排序，输出90%的结果；20表示输出最慢的20条
    --host  									指定MySQL地址，也可用-h指定
    --port										指定MySQL端口
    --user  									指定MySQL用户名，也可用-u指定
    --password  							指定MySQL密码，也可用-p指定
    --history 								将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，可通过查询同一CHECKSUM来比较某类型查询的历史变化
    --review 									将分析结果保存到表中，从而方便未来review。这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中
    --output 									指定将结果输出输出到哪里，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读
    --since 									指定分析的起始时间，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计
    --until 									指定分析的截止时间，配合--since可以分析一段时间内的慢查询

*  常用DSN

A    指定字符集
      D    指定连接的数据库
      P    连接数据库端口
      S    连接Socket file
      h    连接数据库主机名
      p    连接数据库的密码
      t    使用--review或--history时把数据存储到哪张表里
      u    连接数据库用户名

DSN使用key=value的形式配置；多个DSN使用,分隔

*  使用示例

# 展示slow.log中最慢的查询的报表
    pt-query-digest slow.log
    
    # 分析最近12小时内的查询
    pt-query-digest --since=12h slow.log
    
    # 分析指定范围内的查询
    pt-query-digest slow.log --since '2020-06-20 00:00:00' --until '2020-06-25 00:00:00'
    
    # 把slow.log中查询保存到query_history表
    pt-query-digest --user=root --password=root123 --review h=localhost,D=test,t=query_history --create-review-table slow.log
    
    # 连上localhost，并读取processlist，输出到slowlog
    pt-query-digest --processlist h=localhost --user=root --password=root123 --interval=0.01 --output slowlog
    
    # 利用tcpdump获取MySQL协议数据，然后产生最慢查询的报表
    # tcpdump使用说明：https://blog.csdn.net/chinaltx/article/details/87469933
    tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
    pt-query-digest --type tcpdump mysql.tcp.txt
    
    # 分析binlog
    mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
    pt-query-digest  --type=binlog mysql-bin000093.sql
    
    # 分析general log
    pt-query-digest  --type=genlog  localhost.log

*  结果可视化：

在Percona官方 [https://www.percona.com/blog/2012/08/31/visualization-tools-for-pt-query-digest-tables/][https_www.percona.com_blog_2012_08_31_visualization-tools-for-pt-query-digest-tables] 有介绍两款工具：

*  [Query Digest UI][]
 *  [Box Anemometer][]
 *  但两款工具都已N多年不维护了，如果感兴趣也可以搭建玩一玩，个人不建议用于生产。此外，Box Anemometer安装教程可参考[https://www.cnblogs.com/lixigang/articles/5011055.html][https_www.cnblogs.com_lixigang_articles_5011055.html]

### pt-index-usage ###

> **TIPS**  
> 官方文档：[pt-index-usage][]

*  作用

通过日志文件分析查询，并分析查询如何使用索引

*  原理
    
     *  清点数据库中所有的表与索引，并将库中现有的索引和日志中的查询所使用的索引进行比较
     *  对日志中的每个查询运行EXPLAIN（这一步使用单独的数据库连接清点表并执行EXPLAIN）
     *  对于无用的索引，展示删除的语句
 *  语法

pt-index-usage [OPTIONS] [FILES]

*  常用OPTIONS

--drop					打印建议删除的索引，取值primary、unique、non-unique、all。默认值non-unique，只会打印未使用的二级索引
    --databases			只分析指定数据库的索引，多个库用,分隔
    --tables				只分析指定表的索引，多张表用,分隔
    --progress			打印执行进度
    --host  				指定MySQL地址，也可用-h指定
    --port					指定MySQL端口
    --user  				指定MySQL用户名，也可用-u指定
    --password  		指定MySQL密码，也可用-p指定

DSN使用key=value的形式配置；多个DSN使用,分隔

*  常用DSN

A    指定字符集
      D    指定连接的数据库
      h    连接数据库主机名
      p    连接数据库的密码
      P    连接数据库端口
      S    连接Socket file
      u    连接数据库用户名

*  使用示例

# 读取slow.log，并连上localhost，去分析有哪些索引是可以删除的
    pt-index-usage slow.log --user=root --password=root123 --host=localhost --port=
    # 读取slow.log，并连上localhost，只分析employees库中，有有哪些索引是可以删除的
    pt-index-usage slow.log --user=root --password=root123 --host=localhost --databases=employees

*  注意点
    
     *  此工具使用MySQL资源比较多，因此，在使用此工具时候：
        
         *  如果有条件，尽量不要直接在生产环境执行，而应在有相同表结构的数据库环境执行；
         *  如果必须在生产环境执行，请避开高峰期，比如在凌晨低谷期执行
     *  此工具分析大文件比较慢，使用时需注意这点，并做一定处理（比如把遗留的超大的慢查询日志先删除，而可以新建一个慢查询日志，并运行一段时间后用pt-index-usage分析）
     *  由于pt-index-usage只会扫描慢查询，而非所有的查询，所以有可能某个索引在慢查询日志中未使用，但其实还是被使用了的（只是使用这个索引的SQL并非慢查询）。因此：
        
         *  正式删除之前，应当先review下，确保可以删除该索引后再操作，避免发生问题
         *  对于MySQL 8.0及更高版本，善用“[不可见索引][Link 1]”，进一步降低风险。

> **TIPS**  
> [pt-duplicate-key-checker][] ：可以帮助我们找到重复的索引或外键，使用方式基本类似。

### pt-variable-advisor ###

> **TIPS**  
> 官方文档：[pt-variable-advisor][]

*  作用

分析MySQL变量，并对可能出现的问题提出建议

*  原理

执行 `SHOW VARIABLES` ，并分析哪些变量的值设置不合理，给出建议

*  语法

pt-variable-advisor [OPTIONS] [DSN]

*  常用OPTIONS

--source-of-variable			指定变量来源，可选mysql/none或者文件
    --user  									指定MySQL用户名，也可用-u指定
    --password  							指定MySQL密码，也可用-p指定

*  常用DSN

DSN使用key=value的形式配置；多个DSN使用,分隔

*  使用示例

# 连接上localhost:3306，并分析变量
    pt-variable-advisor localhost --user=root --password=root123
    pt-variable-advisor P=3306,u=root,p=root123 localhost:3306
    
    # 先将show global variables生成文件，然后用pt-variable-advisor分析文件
    mysql -uroot -proot123 -e'show global variables' > /root/vars.txt
    pt-variable-advisor --source-of-variables /root/vars.txt

### pt-online-schema-change ###

> **TIPS**
> 
>  *  官方文档：[pt-online-schema-change][]
>  *  MySQL从5.6开始，已支持online r功能，pt-online-schema-change越来越弱化了。
>     
>      *  有关Online DDL，可详见 [Online DDL Operations][]
>      *  有关online DDL和pt-online-schema-change之间的对比详见《 [MySQL ONLINE DDL 和PT-ONLINE-SCHEMA-CHANGE对比][MySQL ONLINE DDL _PT-ONLINE-SCHEMA-CHANGE] 》

*  作用

在线修改表结构，无需锁表地ALTER表结构

*  原理
    
     *  创建一张一模一样的表，表名一般是\_new后缀
     *  在新表上执行更改字段操作
     *  在原表上加三个触发器，分别对应于DELETE/UPDATE/INSERT操作，并将原表中要执行的语句也在新表中执行
     *  将原表的数据拷贝到新表中
     *  使用原子的RENAME TABLE操作同时重命名原始表和新表，完成此操作后，删除原始表。
 *  语法

pt-online-schema-change [OPTIONS] DSN

*  常用OPTIONS

--dry-run											创建并修改新表的结构，但不会创建触发器、拷贝旧表数据也不会替换旧表
    --execute											如果指定该选项，则会修改表结构，否则只会做一些安全检查
    --charset											指定编码
    --alter												修改表结构的语句（其实就是你alter table语句，去掉alter table后剩下的部分），多条语句使用,分隔。该选项有一些限制，详见 https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html#cmdoption-pt-online-schema-change-alter 
    --no-version-check						是否检查版本
    --alter-foreign-keys-method		处理带有外键约束的表，以保证它们可以引用到正确的表。取值：auto（自动选择最佳策略）、rebuild_constraints（适用于删除和重新添加引用新表的外键约束）、drop_swap（禁用外键检查，然后在重命名新表之前将其删除）、none（无）

*  常用DSN

A    指定字符集
      D    指定连接的数据库
      h    连接数据库主机名
      p    连接数据库的密码
      P    连接数据库端口
      S    连接Socket file
      t    想要alter的表
      u    连接数据库用户名

DSN使用key=value的形式配置；多个DSN使用,分隔

*  使用示例

# 为employees库的employees表添加字段my_test_column
    pt-online-schema-change -uroot -proot123 --alter='add column my_test_column int' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
    
    # 修改字段
    pt-online-schema-change -uroot -proot123 --alter='modify column my_test_column bigint(25)' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
    
    # 添加索引
    pt-online-schema-change -uroot -proot123 --alter='add key indx_my_test_column(my_test_column)' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees
    
    # 删除索引
    pt-online-schema-change -uroot -proot123 --alter='drop key indx_my_test_column' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees
    
    # 删除字段
    pt-online-schema-change -uroot -proot123 --alter='drop column my_test_column int' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees

*  注意点
    
     *  尽管用pt-online-schema-change 在线修改表结构不会锁表，但是对性能还是有一定的影响的。这是因为在执行过程中会做全表扫描，所以大表应在业务低峰期执行该操作；
     *  对于主从复制架构，考虑到主从的一致性，应在主库上执行pt-online-schema-change操作。

### 其他工具 ###

# 展示系统概要信息
    pt-summary
    
    # 展示MySQL相关的概要信息
    pt-mysql-summary --user=root --password=root123 --host=localhost --port=3306
    
    # 把explain的结果转换成树形展示（在早期MySQL中，没有explain format=tree时，比较有用，可以增强explain的可读性）
    mysql -uroot -proot123 -e "explain select * from employees.employees" > test-explain.sql
    pt-visual-explain --host=localhost --user=root --password=root123 test-explain.sql

### 遇到的问题 ###

无法连接数据库，并报类似如下的异常：

DBI connect(';host=localhost;mysql_read_default_group=client','root',...) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/bin/pt-query-digest line 1001.

这是由于目前Percona Toolkit中的部分工具暂不支持caching\_sha2\_password导致的。

解决方案有两种：

*  方法一、连接数据，并输入类似如下命令：

mysql> ALTER USER 你的用户@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

例如：

ALTER USER root@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';

*  方法二、修改mysql配置文件 my.cnf ，在其中添加如下内容，并重启。

[mysqld] default_authentication_plugin=mysql_native_password

[pt-query-digest]: https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html
[https_www.percona.com_blog_2012_08_31_visualization-tools-for-pt-query-digest-tables]: https://www.percona.com/blog/2012/08/31/visualization-tools-for-pt-query-digest-tables/
[Query Digest UI]: https://github.com/kormoc/Query-Digest-UI
[Box Anemometer]: https://github.com/box/Anemometer
[https_www.cnblogs.com_lixigang_articles_5011055.html]: https://www.cnblogs.com/lixigang/articles/5011055.html
[pt-index-usage]: https://www.percona.com/doc/percona-toolkit/3.0/pt-index-usage.html
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
[pt-duplicate-key-checker]: https://www.percona.com/doc/percona-toolkit/3.0/pt-duplicate-key-checker.html
[pt-variable-advisor]: https://www.percona.com/doc/percona-toolkit/3.0/pt-variable-advisor.html
[pt-online-schema-change]: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html
[Online DDL Operations]: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
[MySQL ONLINE DDL _PT-ONLINE-SCHEMA-CHANGE]: https://blog.csdn.net/cuanzhutang8944/article/details/100491865