mysql PT工具

天道酬勤-明天会更好

已于 2022-08-10 16:24:23 修改

阅读量2.7k

点赞数

文章标签： mysql 数据库 database

于 2022-05-09 13:42:17 首次发布

本文链接：https://blog.csdn.net/u010648194/article/details/124663720

版权

一、安装pt工具

① 下载

下载地址：Download Percona Toolkit

linux下载/windows直接点击下载 percona-toolkit-3.1.0_x86_64.tar.gz　　　　　https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz

② 安装

# tar zxvf percona-toolkit-<version>.tar.gz
# cd percona-toolkit-<version>
# perl Makefile.PL   (安装到非缺省目录 perl Makefile.PL PREFIX=${HOME})
# make
# make test ##如果失败 可以继续安装
# make install

安装成功了就到bin目录里面进行操作了，可以看到有这么多的工具

[root@master-129 bin]# ls
pt-align                  pt-fifo-split       pt-ioprofile             pt-online-schema-change  pt-sift           pt-table-checksum
pt-archiver               pt-find             pt-kill                  pt-pg-summary            pt-slave-delay    pt-table-sync
pt-config-diff            pt-fingerprint      pt-mext                  pt-pmp                   pt-slave-find     pt-table-usage
pt-deadlock-logger        pt-fk-error-logger  pt-mongodb-query-digest  pt-query-digest          pt-slave-restart  pt-upgrade
pt-diskstats              pt-heartbeat        pt-mongodb-summary       pt-secure-collect        pt-stalk          pt-variable-advisor
pt-duplicate-key-checker  pt-index-usage      pt-mysql-summary         pt-show-grants           pt-summary        pt-visual-explain

二、 pt-kill 自我保护工具

## 每10s记录一下超过30s的查询语句 输出到/tmp/pt_select.log文件中
pt-kill --host=127.0.0.1 --port=3306 --user=root --password=123456 --match-db='test_db' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log

## 每10s找出超过30s的查询语句 直接杀掉
pt-kill --host=127.0.0.1 --port=3306 --user=root --password=123456 --match-db='test_db' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log

# 只打印-查杀某IP来源的会话
pt-kill --host=127.0.0.1 --port=3306 --user=root --password=123456 --match-db='test_db' --match-host "192.168.65.129" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log

# 执行杀操作-查杀某IP来源的会话
pt-kill --host=127.0.0.1 --port=3306 --user=root --password=123456 --match-db='test_db' --match-host "192.168.65.129" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log

三、pt-online-schema-change 在线运行

对大表进行ddl操作时，线上会锁表，我们可以使用 pt-online-schema-change 工具在mysql不繁忙的时候进行操作。

我们在进行ddl操作时，最好去查看一下mysql文档中，那些操作是可以在线进行操作的，那些是不允许的，对不能允许的最好使用 pt-online-schema-change 进行操作。

基础参数说明

--nodrop-old-table
默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。

--max-lag
默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠--check-interval指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。

--no-check-replication-filters
表示不需要检查 Master 配置里是否指定了 Filter。 默认会检查，如果配置了 Filter，如 replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db 等，在从库checksum就与遇到表不存在而报错退出，所以官方默认是yes（--check-replication-filters）但我们实际在检测中时指定--databases=，所以就不存在这个问题，干脆不检测。

--max-load="Threads_running=100" 表示线程到100暂停pt操作，默认25

--critical-load="Threads_running=600"  表示线程到600自动退出,默认值50

--chunk-size=100 默认1000
对每次导入行数进行控制，已减少对原表的锁定时间
有了chunk size，pt-osc采用的是 select id from xxx where id >= ? order by id limit (chunk_size -1),2 获取到每次操作chunk的最大id范围，注意这里的id可以是主键，也可以是唯一键。这里得到两个值，第一个值是此次chunk操作的最大值，第二个值则是下一个chunk的最小值，对应下次的where id >=?
得到一个具体的id值，然后采用INSERT LOW_PRIORITY IGNORE xxx FORCE INDEX(PRIMARY) WHERE ((id >= ?)) AND ((id <= ?)) LOCK IN SHARE MODE 来copy数据，

①添加一个新的字段

pt-online-schema-change --alter="add age varchar(10) NOT NUll DEFAULT '' COMMENT '性别' ;"  --execute --print --max-lag=5 D=test,t=x,u=root,p=123456,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100

--alter就是我们要添加字段的sql命令

执行的结果

[root@master-129 bin]# pt-online-schema-change --alter="add age varchar(10) NOT NUll DEFAULT '' COMMENT '性别' ;"  --execute --print --max-lag=5 D=test,t=x,u=root,p=123456,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100
No slaves found.  See --recursion-method if host master-129 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`x`...
Creating new table...
CREATE TABLE `test`.`_x_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1949804 DEFAULT CHARSET=latin1
Created new table test._x_new OK.
Altering new table...
ALTER TABLE `test`.`_x_new` add age varchar(10) NOT NUll DEFAULT '' COMMENT 'æ§å«' ;
Altered `test`.`_x_new` OK.
2022-05-08T14:20:20 Creating triggers...
2022-05-08T14:20:20 Created triggers OK.
2022-05-08T14:20:20 Copying approximately 1797525 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_x_new` (`id`) SELECT `id` FROM `test`.`x` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 7291 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`x` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `test`.`x`:  86% 00:04 remain
2022-05-08T14:21:00 Copied rows OK.
2022-05-08T14:21:00 Analyzing new table...
2022-05-08T14:21:00 Swapping tables...
RENAME TABLE `test`.`x` TO `test`.`_x_old`, `test`.`_x_new` TO `test`.`x`
2022-05-08T14:21:00 Swapped original and new tables OK.
2022-05-08T14:21:00 Dropping old table...
DROP TABLE IF EXISTS `test`.`_x_old`
2022-05-08T14:21:04 Dropped old table `test`.`_x_old` OK.
2022-05-08T14:21:04 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_x_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_x_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_x_ins`
2022-05-08T14:21:04 Dropped triggers OK.
Successfully altered `test`.`x`.

② 其他的操作

 ## 修改字段
 pt-online-schema-change  --alter="modify column address varchar(200) default '' comment '家庭住址';"  --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100

 ## 添加索引
 pt-online-schema-change --alter="add index index_age(age);"  --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100

## 删除索引
pt-online-schema-change --alter="drop index index_age;"  --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100

## 修改列名，并添加索引
pt-online-schema-change --alter="change column age ages varchar(5) after address,add index index_ages(ages);"  --execute --print --max-lag=5 D=test,t=users,u=root,p=123,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100 --no-check-alter

四、pt-query-digest -----慢查询Log的分析

① 慢查询可以通过mysqldumpslow来分析

##找出慢查询日志中使用最多的10条sql

mysqldumpslow -s c -t 10 /usr/local/mysql/data/mysql57-slow.log

② pt-query-digest 分析

pt-query-digest /mdata/mysql_data/master-129-slow.log

[root@master-129 bin]# pt-query-digest /mdata/mysql_data/master-129-slow.log

# 80ms user time, 10ms system time, 25.68M rss, 218.29M vsz
# Current date: Sun May  8 14:47:13 2022
# Hostname: master-129
# Files: /mdata/mysql_data/master-129-slow.log
# Overall: 3 total, 1 unique, 0.02 QPS, 0.52x concurrency ________________
# Time range: 2022-05-08T06:38:01 to 2022-05-08T06:40:26
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            75s     18s     37s     25s     37s      9s     19s
# Lock time          337us    83us   158us   112us   152us    31us    93us
# Rows sent              3       1       1       1       1       0       1
# Rows examine           3       1       1       1       1       0       1
# Query size            93      31      31      31      31       0      31

# Profile
# Rank Query ID                           Response time  Calls R/Call  V/M
# ==== ================================== ============== ===== ======= ===
#    1 0x70B2C25B5EDFED54E8E058E6E94350E2 75.3003 100.0%     3 25.1001  2.93 SELECT t

# Query 1: 0.02 QPS, 0.52x concurrency, ID 0x70B2C25B5EDFED54E8E058E6E94350E2 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.93
# Time range: 2022-05-08T06:38:01 to 2022-05-08T06:40:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       3
# Exec time    100     75s     18s     37s     25s     37s      9s     19s
# Lock time    100   337us    83us   158us   112us   152us    31us    93us
# Rows sent    100       3       1       1       1       1       0       1
# Rows examine 100       3       1       1       1       1       0       1
# Query size   100      93      31      31      31      31       0      31
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 't'\G
#    SHOW CREATE TABLE `test`.`t`\G
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(50) from t limit 1\G

五、pt-deadlock-logger 死锁监控

① 在数据库中创建一个表存储死锁的相关信息，并监控

pt-deadlock-logger --create-dest-table --dest D=test,t=dest u=root,P=3306,h=127.0.0.1,p=123456 --ask-pass --charset=utf8 &

## --create-dest-table创建表

创建的dest表。

(root@localhost) [test]> show create table dest\G
*************************** 1. row ***************************
       Table: dest
Create Table: CREATE TABLE `dest` (
  `server` char(20) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `thread` int(10) unsigned NOT NULL,
  `txn_id` bigint(20) unsigned NOT NULL,
  `txn_time` smallint(5) unsigned NOT NULL,
  `user` char(16) NOT NULL,
  `hostname` char(20) NOT NULL,
  `ip` char(15) NOT NULL,
  `db` char(64) NOT NULL,
  `tbl` char(64) NOT NULL,
  `idx` char(64) NOT NULL,
  `lock_type` char(16) NOT NULL,
  `lock_mode` char(1) NOT NULL,
  `wait_hold` char(1) NOT NULL,
  `victim` tinyint(3) unsigned NOT NULL,
  `query` text NOT NULL,
  PRIMARY KEY (`server`,`ts`,`thread`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

② 触发死锁后在表就会有记录

可以看出query中有产生死锁的sql语句。

(root@localhost) [test]> select * from dest\G
*************************** 1. row ***************************
   server: 127.0.0.1
       ts: 2022-05-08 15:05:07
   thread: 11
   txn_id: 0
 txn_time: 43
     user: root
 hostname: localhost
       ip: 
       db: test
      tbl: t
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: update t set c = 6 where id = 15
*************************** 2. row ***************************
   server: 127.0.0.1
       ts: 2022-05-08 15:05:07
   thread: 20
   txn_id: 0
 txn_time: 16
     user: root
 hostname: localhost
       ip: 
       db: test
      tbl: t
      idx: PRIMARY
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: update t set c = 6 where id = 10
2 rows in set (0.00 sec)

六、其他重要的工具

1. pt-slave-delay ---就是指定从库比主库延迟多长时间

2. pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理这两个一搬是搭配使用

3. pt-find ---找出几天之前建立的表

4. pt-slave-restart -----主从报错，跳过报错

5. pt-summary ---整个系统的的概述

6. pt-mysql-summary ---MySQL的表述，包括配置文件的描述

7. pt-duplicate-key-checker ---检查数据库重复索引

其他重要的工具，后面在慢慢写一下

pt-upgrade 
    #该命令主要用于对比不同mysql版本下SQL执行的差异，通常用于升级前进行对比。
    #会生成SQL文件或单独的SQL语句在每个服务器上执行的结果、错误和警告信息等。 
  
  pt-online-schema-change
    #功能为支持在线变更表构，且不锁定原表，不阻塞原表的DML操作。
    #该特性与Oracle的dbms_redefinition在线重定义表原理基本类似。
    
  pt-mysql-summary
    #对连接的mysql服务器生成一份详细的配置情况以及sataus信息
    #在尾部也提供当前实例的的配置文件的信息
    
  pt-mext
    #并行查看SHOW GLOBAL STATUS的多个样本的信息。
    #pt-mext会执行你指定的COMMAND，并每次读取一行结果，把空行分割的内容保存到一个一个的临时文件中，最后结合这些临时文件并行查看结果。
  
  pt-kill
    #Kill掉符合指定条件mysql语句
  
  pt-ioprofile
    #pt-ioprofile的原理是对某个pid附加一个strace进程进行IO分析
    
  pt-fingerprint
    #用于生成查询指纹。主要将将sql查询生成queryID，pt-query-digest中的ID即是通过此工具来完成的。
    #类似于Oracle中的SQL_ID，涉及绑定变量，字面量等
  
  pt-find
    #用与查找mysql表并执行指定的命令，类似于find命令
  
  pt-fifo-split
    #模拟切割文件并通过管道传递给先入先出队列而不用真正的切割文件
    
  pt-deadlock-logger
    #用于监控mysql服务器上死锁并输出到日志文件，日志包含发生死锁的时间、死锁线程id、死锁的事务id、发生死锁时事务执行时间等详细信息。
  
  pt-archiver
    #将mysql数据库中表的记录归档到另外一个表或者文件
    #该工具具只是归档旧的数据，对线上数据的OLTP查询几乎没有影响。
    #可以将数据插入另外一台服务器的其他表中，也可以写入到一个文件中，方便使用load data infile命令导入数据。
  
  pt-agent
    #基于Percona Cloud的一个客户端代理工具
  
  pt-visual-explain
    #用于格式化explain的输出
  
  pt-variable-advisor
    #用于分析mysql系统变量可能存在的一些问题，可以据此评估有关参数的设置正确与否。
  
  pt-stalk
    #用于收集mysql数据库故障时的相关信息便于后续诊断处理。
    
  pt-slave-delay
    #用于设定从服务器落后于主服务器的时间间隔。
    #该命令行通过启动和停止复制sql线程来设置从落后于主指定时间。

  pt-sift
    #用于浏览pt-stalk生成的文件。
    
  pt-show-grants
    #将当前实例的用户权限全部输出，可以用于迁移数据库过程中重建用户。
    
  pt-query-digest
    #用于分析mysql服务器的慢查询日志，并格式化输出以便于查看和分析。
  
  pt-pmp
    #为查询程序执行聚合的GDB堆栈跟踪，先进性堆栈跟踪，然后将跟踪信息汇总。 
    
  pt-index-usage
    #从log文件中读取查询语句，并用分析当前索引如何被使用。
    #完成分析之后会生成一份关于索引没有被查询使用过的报告，可以用于分析报告考虑剔除无用的索引。
    
  pt-heartbeat
    #用于监控mysql复制架构的延迟。
    #主要是通过在主库上的--update线程持续更新指定表上的一个时间戳，从库上--monitor线程或者--check线程检查主库更新的时间戳并与当前系统时间对比，得到延迟值。
  
  pt-fk-error-logger
    #将外键相关的错误信息记录到日志或表。
  
  pt-duplicate-key-checker 
     #功能为从mysql表中找出重复的索引和外键，这个工具会将重复的索引和外键都列出来
     #同时也可以生成相应的drop index的语句
     
  pt-diskstats
    #类似于iostat，打印磁盘io统计信息，但是这个工具是交互式并且比iostat更详细。可以分析从远程机器收集的数据。
  
  pt-config-diff
    #用于比较mysql配置文件和服务器变量
    #至少2个配置源需要指定，可以用于迁移或升级前后配置文件进行对比
    
  pt-align
    #格式化输出
    
  pt-slave-find
    #连接mysql主服务器并查找其所有的从，然后打印出所有从服务器的层级关系。
    
  pt-table-checksum
    #用于校验mysql复制的一致性。
    #该工具主要是高效的查找数据差异，如果存在差异性，可以通过pt-table-sync来解决。

  pt-query-digest

　#用户慢查询分析

   #根据执行时间，锁时间，执行次数，等等综合分析

参考博文

mysql中的pt工具集 - 郭大侠1 - 博客园

天道酬勤-明天会更好

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
mysql PT工具

一、安装pt工具① 下载下载地址：Download Percona Toolkitlinux下载/windows直接点击下载percona-toolkit-3.1.0_x86_64.tar.gz　　　　　https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz② 安装# tar zxvf percona-toolkit-&l...
复制链接

扫一扫