MySQL数据库调优————Percona Toolkit使用
皇甫心思
pt-query-digest
- 作用
分析日志（包括binlog、Gener log、slowlog）、processlist以及tcpdump中的查询 - 语法
pt-query-digest [POTIONS] [FILES] [DSN]
- 常用OPTIONS
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
--filter 输出符合条件的内容
--limit 限制输出的百分比或数量。可指定百分比或数字，例如90%表示按响应时间
从小到大排序，输出90%的结果；20表示输出最慢的20条
--host 指定MySQL地址，也可用-h指定
--user 指定MySQL用户名，也可用-u指定
--password 指定MySQL密码，也可用-p指定
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果
存在相同的语句，且查询所在的时间区间和历史表中的不同，可通过查询同一
CHRCKSUM来比较某类型查询的历史变化
--review 将分析结果保存到表中，从而方便未来review。这个分析只是对查询条件进行
参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果
存在相同的语句分析，就不会记录到数据表中
--output 指定将结果输出到哪里，值可以是report（标准分析报告）、slowlog、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 '2023-02-20 00:00:00' --until '2023-02-21 00:00:00'
# 把slow.log中查询保存到query_history表
pt-query-digest --user=root --password=123456 --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=123456 --interval=0.01 --output slowlog
# 利用tcpdump获取MySQL协议数据，然后产生最慢查询的报表
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
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
| 参数 | 含义 |
|:–|:–|
| A | 指定字符集 |
| D | 指定连接的数据库 |
| P | 连接数据可端口 |
| S | 连接Socket file |
| h | 连接数据库主机名 |
| P | 连接数据库的密码 |
| t | 使用–review或–history时把数据存储到哪张表里 |
| u | 连接数据库用户名 | -
使用示例
# 读取slow.log，并连上localhost，去分析有哪些索引是可以删除的
pt-index-usage slow.log --user=root --password=123456 -host=localhost --port=3306
# 读取slow.log，并脸上localhost，只分析employees库中，有哪些索引是可以删除的
pt-index-usage slow.log --user=root --password=123456 -host=localhost --port=3306 --databases=employees
- 注意点
- 此工具使用MySQL资源比较多，因此，在使用此工具的时候：
- 如果有条件，尽量不要直接在生产环境执行，而应在有相同表结构的测试环境执行
- 如果必须在生产环境执行，请避开业务高峰期，比如在凌晨低谷期执行
- 此工具分析大文件比较慢，使用时需注意这点，并做一定处理（比如把遗留的超大的慢查询日志先删除，而再新建一个慢查询日志，并运行一段时间后用pt-index-usage分析）
- 由于pt-index-usage只会扫描慢查询，而非所有的查询，所以有可能某个索引在慢查询日志中未使用，但其实还是被使用了的。因此：
- 正式删除前，应当先review下，确保可以删除该索引后再操作，避免发生问题
- 此工具使用MySQL资源比较多，因此，在使用此工具的时候：
pt-variable-advisor
- 作用
分析MySQL变量，并对可能出现的问题提出建议 - 原理
执行SHOW VARIABLE，并分析哪些变量的值设置不合理，给出建议 - 语法
pt-variable-advisor [OPTIONS] [DNS]
- 常用OPTIONS
--source-of-variable 指定变量来源，可选mysql/none或者文件
--user 指定MySQL用户名，也可用-u指定
--password 指定MySQL密码，也可用-p指定
- 常用DNS
| 参数 | 含义 |
|---|---|
| A | 指定字符集 |
| D | 指定连接的数据库 |
| P | 连接数据可端口 |
| S | 连接Socket file |
| h | 连接数据库主机名 |
| P | 连接数据库的密码 |
| t | 使用–review或–history时把数据存储到哪张表里 |
| u | 连接数据库用户名 |
- 使用示例
# 连接上localhost：3306，并分析变量
pt-variable-advisor localhost --user=root --password=123456
pt-variable-advisor P=3306,u=root,p=123456 localhost
# 先将show global variables生成文件，然后使用pt-variable-advisor分析文件
mysql -uroot -p123456 -e 'show global variables' > /home/value.txt
pt-variable-advisor --source-of-variables /home/value.txt
pt-online-schema-change
- 作用
在线修改表结构，无需锁表的ALTER表结构 - 原理
- 创建一张一摸一样的表，表明一般是_new后缀
- 在新表上执行更改字段操作
- 在原表上加三个触发器，分别对应DELETE/UPDATE/INSERT操作，并将原表中要执行的语句也在新表中执行
- 将原表的数据拷贝到新表中
- 使用原子的RENAME TABLE操作，同时重命名原始表和新表，完成操作后，删除原始表
- 语法
pt-online-schema-change [OPTIONS] [DNS]
- 常用OPTIONS
--dry-run 创建并修改新表的结构，但不会创建触发器、拷贝旧表数据也不会替换旧表
--execute 如果指定该选项，则会修改表结构，否则只会做一些安全检查
--charset 指定编码
--alter 修改表结构的语句（其实就是你alter table语句，去掉alter后剩下的部
分），多条语句使用逗号分隔。
--no-version-check 是否检查版本
--alter-foreign-keys-method 处理带有外键约束的表，以保证它们可以引用到正确的表。取值：auto（自动选择最佳策略）、rebuild_constraints（适用于删除和重新添加引用新表的外键约束）、drop_swap（禁用外键检查，然后在重命名新表之前将其删除）、none（无）
- 常用DNS
| 参数 | 含义 |
|---|---|
| A | 指定字符集 |
| D | 指定连接的数据库 |
| P | 连接数据可端口 |
| S | 连接Socket file |
| h | 连接数据库主机名 |
| P | 连接数据库的密码 |
| t | 使用–review或–history时把数据存储到哪张表里 |
| u | 连接数据库用户名 |
- 使用示例
# 为employees库的employees表添加字段my_test_column
pt-online-schema-change -uroot -p123456 --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 -p123456 --alter='modify column my_test_column bigint(30)' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
# 添加索引
pt-online-schema-change -uroot -p123456 --alter='add key index_my_test_column(my_test_column)' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
# 删除索引
pt-online-schema-change -uroot -p123456 --alter='drop key index_my_test_column' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
# 删除字段
pt-online-schema-change -uroot -p123456 --alter='drop column my_test_column bigint(30)' --alter-foreign-keys-method=rebuild_constraints --execute D=employees,t=employees --charset=utf8mb4
- 注意点
- 尽管用pt-online-schem-change在线修改表结构不会锁表，但是对性能还是有一定的影响的。这是因为在执行过程中会做全表扫描，所以达标应在业务低谷期进行操作
- 对于只从复制架构，考虑到主从的一致性，应在主库上执行pt-online-schema-change操作。
其他工具
- 展示系统概要信息
pt-summary - 展示MySQL相关的概要信息
pt-mysql-summary --user=root --password=123456 --host=localhost --port=3306 - 把explain的结果转换成树形展示
mysql -uroot -p123456 -e ‘explain select * from employees’ > /home/explain.sql
pt-visual-explain --user=root --password=123456 --host=localhost --port=3306 explain.sql
由于工具很多，就不再列举，可以去官网参考文档了解
类似资料：