percona-toolkit运维工具

参考MySQL基础运维——percona-toolkit运维工具_ITPUB博客

一、percona-toolkit工具

percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的 mysql 任务和系统任务，这些任务包括：

检查 master 和 slave 数据的一致性
有效地对记录进行归档
查找重复的索引
对服务器信息进行汇总
分析来自日志和 tcpdump 的查询
当系统出问题的时候收集重要的系统信息

percona-toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 mysql 的最有名的工具，现在 Maatkit 工具已经不维护了，请大家还是使用 percona-toolkit 吧！这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类，作为一个优秀的 DBA，里面有的工具非常有用，如果能掌握并加以灵活应用，将能极大的提高工作效率。

1、percona-toolkit软件包下载

2、 percona-toolkit软件安装

软件安装存在三种安装方式，分别为RPM包、二进制包、源码安装。

需安装依赖包

1	`yum` `install` `perl-DBI perl-DBD-MySQL`

1.RPM包安装方式

1	`rpm –ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm`

2.二进制包安装

tar –xvf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

下面介绍它自带的一些工具

2、pt-kill工具

1、工具介绍

pt-kill 是一个简单而且很实用的查杀mysql线程和查询的工具，主要是为了防止一些大/复杂/长时间查询占用数据库及系统资源，而对线上业务造成影响的情况。

2、常用参数

常用参数	含义
--user	用户
--password	密码
--port	端口
--host	主机
--socket	本地套接字
--match-command	匹配状态
--match-info	匹配信息
--match-state	匹配声明
--ignore-host/--match-host	匹配主机
--ignore-db/--match-db	匹配数据库
--ignore-user/--match-user	匹配用户
--kill	杀掉连接并且退出
--kill-query	只杀掉连接执行的语句，但是线程不会被终止
--print	打印满足条件的语句
--busy-time	SQL运行时间的线程
--idle-time	sleep时间的连接线程，必须在--match-command sleep时才有效
--interval	query的间隔
--victim	oldest\|all\|all-but-oldest 针对范围
--daemonize	是否放到后台执行
--interval	执行频率(s=seconds, m=minutes, h=hours, d=days)
--log-dsn D=test,t=pk_log	记录信息到表中

3、实际案例

以下均省略连接参数！

3.1 杀运行时间超过10s的SQL语句但保留线程

1	`pt-kill` `--busy-time=10 --victims all --print --kill-query`

3.2 查某IP来源且运行时间超过20s的会话

1	`pt-kill` `--match-host` `"10.100.50.29"` `--print --victims all --busy-time=20s`

3.3 每10秒检查一次，状态为sleep的进程就给干掉

1	`pt-kill` `--match-command` `Sleep --victims all --interval 10 --kill` `--print`

3.4 查杀select大于10s的会话

1	`pt-kill` `--match-info` `"select\|Select\|SELECT"` `--print --victims all --busy-time` `20s`

3.5 查状态为sleep的进程且时间超过1h

1	`pt-kill` `--match-command` `Sleep --victims all --idle-time=1h --print`

3.6 查杀访问某用户的会话

1	`pt-kill` `--match-user` `"test"` `--victims all --print --kill`

3.7 将执行记录记录到表中

1	`pt-kill` `--log-dsn D=test,t=pk_log --create-log-table --host=192.168.186.11 --user=root --password=mysql --port=3306 --busy-time=10 --print --kill-query`

注：

指定库必须存在否则会报错！

test库中pk_log表，若不存在先创建表--create-log-table

3.8 杀掉正在进行filesort的sql

1	`pt-kill` `--match-command` `Query --match-state “Sorting result” --busy-time` `10 --interval 10 --run-time` `1 --print --kill` `--victims all`

3.9 后台执行杀掉正在进行filesort的sql

1	`pt-kill` `--match-command` `Query --match-state “Sorting result” --busy-time` `10 --interval 10 --run-time` `1 --print --kill` `--victims all --daemonize`

注：

--daemonize 会将该命令放到后台不断执行。

3.10 筛选文件中processlist

1 2	`mysql -e` `"SHOW PROCESSLIST"` `> proclist.txt` `pt-kill` `--test-matching proclist.txt --busy-time` `60 --print`

3、pt-query-digest工具

1、工具介绍

分析查询执行日志，并产生一个查询报告，为 MySQL、 PostgreSQL、memcached 过滤、重放或者转换语句。

2、常用参数

常用参数	含义
--create-review-table	当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
--create-history-table	当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
--filter	对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit	限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出
--host	mysql服务器地址
--user	mysql用户名
--password	mysql用户密码
--history	将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化
--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可以分析一段时间内的慢查询

3、应用案例

3.1 直接分析慢查询文件

1	`pt-query-digest` `/var/lib/mysql/log/mysql-slow.log`

3.2 分析最近12小时内慢查询

1	`pt-query-digest --since=12h` `/var/lib/mysql/log/mysql-slow.log`

3.3 分析指定时间范围内的慢查询

1	`pt-query-digest` `/var/lib/mysql/log/mysql-slow.log --since` `'2019-11-27 09:30:00'` `--until` `'2019-11-27 10:00:00'`

3.4 分析指含有select语句的慢查询

1	`pt-query-digest --filter` `'$event->{fingerprint} =~ m/^select/i'` `/var/lib/mysql/log/mysql-slow.log`

3.5 针对某个用户的慢查询

1	`pt-query-digest --filter` `'($event->{user} \|\| "") =~ m/^root/i'` `/var/lib/mysql/log/mysql-slow.log`

3.6 查询所有所有的全表扫描或full join的慢查询

1	`pt-query-digest --filter` `'(($event->{Full_scan} \|\| "") eq "yes") \|\|(($event->{Full_join} \|\| "") eq "yes")'` `/var/lib/mysql/log/mysql-slow.log`

3.7 把查询保存到query_review表

1	`pt-query-digest --filter` `'(($event->{Full_scan} \|\| "") eq "yes") \|\|(($event->{Full_join} \|\| "") eq "yes")'` `/var/lib/mysql/log/mysql-slow.log`

3.8 把查询保存到query_history表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table /var/lib/mysql/log/mysql-slow.log_0001

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table /var/lib/mysql/log/mysql-slow.log_0002

3.9 通过tcpdump抓取mysql的tcp协议数据，然后再分析

1 2	`tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt` `pt-query-digest --type` `tcpdump mysql.tcp.txt`

3.10 分析binlog

1 2	`mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql` `pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log`

3.11 分析general log

1	`pt-query-digest --type=genlog localhost.log`

4、结果解析

总体统计结果

#该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
#310ms user time, 20ms system time, 25.21M rss, 207.20M vsz
#工具执行时间
#Current date: Wed Nov 27 10:58:44 2019
#运行分析工具的主机名
#Hostname: test
#被分析的文件名
#Files: /var/lib/mysql/log/mysql-slow.log
#语句总数量，唯一的语句数量，QPS，并发数
#Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
#日志记录的时间范围
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute          total     min     max     avg     95%  stddev  median
#============     ======= ======= ======= ======= ======= ======= =======
#语句执行时间
#Exec time             3s      3s      3s      3s      3s       0      3s
#锁占用时间
#Lock time              0       0       0       0       0       0       0
#发送到客户端的行数
#Rows sent              1       1       1       1       1       0       1
#select语句扫描行数
#Rows examine           0       0       0       0       0       0       0
#查询的字符数
#Query size            15      15      15      15      15       0      15
查询分组统计结果
#Profile
#Rank Query ID                           Response time Calls R/Call V/M  
#==== ================================== ============= ===== ====== =====
#1 0x59A74D08D407B5EDF9A57DD5A41825CA 3.0004 100.0%     1 3.0004  0.00 SELECT
Rank      所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID  语句的ID，（去掉多余空格和文本字符，计算hash值）
Response  总的响应时间
time      该查询在本次分析中总的时间占比
calls     执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call    平均每次执行的响应时间
V/M       响应时间Variance-to-mean的比率
Item      查询对象
每一种查询的详细统计结果
#Query 1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 3414
#This item is included in the report because it matches --limit.
#Scores: V/M = 0.00
#Time range: all events occurred at 2019-11-27 10:58:42
#Attribute    pct   total     min     max     avg     95%  stddev  median
#============ === ======= ======= ======= ======= ======= ======= =======
#Count        100       1
#Exec time    100      3s      3s      3s      3s      3s       0      3s
#Lock time      0       0       0       0       0       0       0       0
#Rows sent    100       1       1       1       1       1       0       1
#Rows examine   0       0       0       0       0       0       0       0
#Query size   100      15      15      15      15      15       0      15
#String:
#Hosts        localhost
#Users        root
#Query_time distribution
#1us
#10us
#100us
#1ms
#10ms
#100ms
#1s  ################################################################
#10s+
#EXPLAIN /*!50100 PARTITIONS*/
select sleep(3)\G
ID          查询的ID号，和上图的Query ID对应
Databases   数据库名
Users       各个用户执行的次数（占比）
Query_time distribution
          查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables      查询中涉及到的表
Explain     SQL语句

4、pt-archiver工具

1、简单介绍

pt-archiver 是将MySQL数据库中的表数据归档到另外一个表或者文件，也可以直接进行记录的删除操作。

归档表必须存在主键。

2、pt-archiver使用场景

1、清理线上过期数据
2、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器
3、两张表之间的数据不完全相同，希望合并。此时加上–ignore或–replace选项，可以轻松实现
4、导出线上数据，到线下数据作处理

3、常用参数

常用参数	含义
--source/--dest	源端/目标端
h/D/t/u/p	主机IP/数据库名/表名/用户/密码
--where	操作条件
--(no-)check-charset	检查连接的字符集与表的字符集是否一致.
--limit X	每次取X行数据用pt-archive处理
--txn-size X	设置X行为一个事务提交一次
--progress X	每处理X行输出一次处理信息
--statistics	输出执行过程及最后的操作统计
--bulk-delete	批量删除source上的旧数据
--bulk-insert	批量插入数据到dest主机(实际LOAD DATA插入)
--replace	将insert into 语句改成replace写入到dest库
--purge	删除source数据库的相关匹配记录
--file	输出为本地文件 %d Day of the month, numeric (01..31) %H Hour (00..23) %i Minutes, numeric (00..59) %m Month, numeric (01..12) %s Seconds (00..59) %Y Year, numeric, four digits %D Database name %t Table name
--header	本地文件头部加入列名

注：

1. 归档表必须存在主键

2. 需要配置client字符集为utf-8,如果你用了utf-8的编码,防止归档数据为乱码

[client]

default-character-set=utf8

4、应用案例

pt-archiver [OPTIONS] --source DSN --where WHERE

1.--dest, --file, --purge 必须指定其一

2.--ignore | --replace 只能选一

3.--txn-size | --commit-each 只能选一

4.--low-priority-insert | --delayed-insert 只能选一

5.--share-lock | --for-update 只能选一

6.--analyze | --optimize 只能选一

7.--no-ascend | --no-delete 只能选一

4.1 导出到外部文件，但是不删除源表里的数据

1	`pt-archiver --source` `h=127.0.0.1,D=test,t=table1,u=root,p=123456 --where` `'1=1'` `--no-check-charset --no-delete --file="/tmp/archiver.dat"`

4.2 删除，不导出和迁移

1	`pt-archiver --source` `h=127.0.0.1,D=test,t=table1,u=root,p=123456,A=UTF8 --charset=UTF8 --where` `"1=1"` `--limit 1000 --purge --commit-each --no-safe-auto-increment --progress=5 --statistics`

4.3 全表归档到其他库

1	`pt-archiver --source` `h=127.0.0.1,D=test,t=table1,u=root,p=123456 --dest h=127.0.0.1,D=test,t=table2,u=root,p=123456 --where` `'id<10000'` `--no-check-charset --no-delete --limit=1000 --commit-each --progress 2000 --statistics`

注：

目标库需要开启load data local功能

SET GLOBAL local_infile = 1;

4.4 根据条件归档到其他库

pt-archiver \
--source h=72.0.0.111,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--dest h=192.168.210.126,P=3306,u=backup,p='mysql',D=zj20_sunft,t=trans_transreq \
--charset=UTF8 --where " id < 1043835027 " --progress 10000 --limit=1000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge

参数解析含义

--charset=UTF8 指定字符集为UTF8

--where 'id < 1043835027' 设置操作条件

--progress 10000 每处理10000行输出一次处理信息

--limit=1000 每次取1000行数据给pt-archive处理

--txn-size 10000 设置10000行为一个事务提交一次

--bulk-insert 批量插入数据到dest主机

--bulk-delete 批量删除source上的旧数据

--statistics 输出统计信息

--purge 删除source数据库的相关匹配记录

posted on 2023-01-11 18:45 JennyYu 阅读(354) 评论(0) 编辑收藏举报

percona-toolkit运维工具

一、percona-toolkit工具

1、percona-toolkit软件包下载

2、 percona-toolkit软件安装

2、pt-kill工具

1、工具介绍

2、常用参数

常用参数

含义

--user

用户

--password

密码

--port

端口

--host

主机

--socket

本地套接字

--match-command

匹配状态

--match-info

匹配信息

--match-state

匹配声明

--ignore-host/--match-host

匹配主机

--ignore-db/--match-db

匹配数据库

--ignore-user/--match-user

匹配用户

--kill

杀掉连接并且退出

--kill-query

只杀掉连接执行的语句，但是线程不会被终止

--print

打印满足条件的语句

--busy-time

SQL运行时间的线程

--idle-time

sleep时间的连接线程，必须在--match-command sleep时才有效

--interval

query的间隔

--victim

oldest|all|all-but-oldest 针对范围

--daemonize

是否放到后台执行

--interval

执行频率(s=seconds, m=minutes, h=hours, d=days)

--log-dsn D=test,t=pk_log

记录信息到表中

3、实际案例

注：

指定库必须存在否则会报错！

test库中pk_log表，若不存在先创建表--create-log-table

注：

--daemonize 会将该命令放到后台不断执行。

3、pt-query-digest工具

1、工具介绍

2、常用参数

常用参数

含义

--create-review-table

当使用--review参数把分析结果输出到表中时，如果没有表就自动创建

--create-history-table

当使用--history参数把分析结果输出到表中时，如果没有表就自动创建

--filter

对输入的慢查询按指定的字符串进行匹配过滤后再进行分析

--limit

限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出

--host

mysql服务器地址

--user

mysql用户名

--password

mysql用户密码

--history

将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化

--review

将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中