percona toolkit工具使用

percona-toolkit是一组命令行工具的集合,用来管理MySQL,使用命令 yum install percona-toolkit 安装,percona-toolkit有以下类别:

开发类工具

pt-duplicate-key-checker

从MySQL表中找出重复的重复的索引和外键,将重复的索引和外键都列出来,并生成删除重复索引的语句。

pt-online-schema-change

在alter更改表结构的时候不用锁定表,也就是在执行alter的时候不会阻塞写和读取操作。

原理步骤如下:
1. 首先创建一个和你要执行的alter操作的表一样的空的表结构,后缀默认是new。
2. 在新表执行alter table语句,因为是空表,所以执行速度很快。
3. 在原表创建3个触发器分别对应insert/update/delete操作。
4. 以一定块大小从原表拷贝数据到新表,拷贝过程中由于触发器的存在,任何在原表进行的写操作都会更新到新建的临时表。
5. 在拷贝完数据之后,使用rename table命令将原表名(tablename)修改为(tablename.old),将(tablename.new)修改为(tablename)。
6. 修改完成之后,将原表删除。

参数

–alter
用于指定该表的操作,可以同时指定多个操作。

# 添加字段
# pt-online-schema-change -u root -p "" --alter "ADD COLUMN domain_id INT" D=employees,t=dept_manager --execute

注意:
(1)改表名时不可用RENAME
(2)修改列名时不可以通过drop该列然后重新add该列来完成,这样会导致无法拷贝原始列的数据出来
(3)如果add一个列没设默认值且设置为‘not null’,工具会拒绝执行,因为工具不会设置默认值
(4)drop外键时,外键名字前需要加 ‘_’ 而不是单纯的就一个外键名字,如drop下面这个外键时:
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
必须这样指定:–alter “DROP FOREIGN KEY _fk_foo”

–charset
默认字符类型,例如如果值为utf8,就将输出的字符设置为utf8格式,将mysql_enable_utf8传递给DBD::mysql,然后连接MySQL后运行SET NAMES UTF8命令

–[no]check-alter
用于改表安全警告,目前支持两种报警:
(1)Column renames
该工具以前的版本中,用CHANGE COLUMN name new_name来改变列名时会导致该列的数据丢失,后来的版本中修补了这个bug,但是这段代码仍然不够万无一失,所以在操作前最好用–dry-run 和 –print去检测一下确定可以正确的rename column
(2)DROP PRIMARY KEY
除非指定–dry-run,否则一旦执行DROP PRIMARY KEY就会发出警告。该工具的触发器,尤其是delete trigger,主要是l利用主键来执行触发的,所以会受到很大的影响。所以最好先用–dry-run 和 –print去检测一下确保trigger可以正确执行

–[no]check-replication-filters
如果主从复制中有复制过滤就报错退出,即如果检测到有binlog_ignore_db、replicate_do_db等过滤手段,就报错退出。

–check-slave-lag
如果复制延迟大于–max-lag就停止数据拷贝,设置该参数后会监控所有的复制进程,一旦发现任何大于–max-lag时间的就停止数据拷贝,如果不想监控所有的复制进程,可以通过–recursion-method来指定想要监控的对象。

–dry-run
create并alter新表,但不创建trigger,不拷贝数据以及替换原始表。

–execute
该参数用于执行alter操作,如果不加的话,只会做一些安全检查后退出。一定要确保知道如何使用该工具并有合适的备份后,再添加该参数。使用该参数时,除了对对象表所需的权限外,还需要SUPER, REPLICATION SLAVE两种权限。

示例

# 更改表的引擎
# pt-online-schema-change -u root -p "" --alter "ENGINE=InnoDB" D=employees,t=dept_manager --execute

下面是对该命令原理步骤的一个模拟。

假设原命令为

# pt-online-schema-change -u root -p "" --alter 'add column c1 int' D=employees,t=dept_manager --execute

1. 检查表的情况,各种show,各种set,主要就是对权限的检查,超时时间的设定,当前系统的繁忙程度;然后就是对表的检查,如是否有触发器的存在。

>SHOW CREATE TABLE `testrep`.`t`\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

2. 新建表

-- 新建表
>CREATE TABLE `_t_new` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   KEY `a` (`a`),
    ->   KEY `b` (`b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

-- 在这个控表上增加表字段
>ALTER TABLE `_t_new` add column c1 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 再次查看表结构
>SHOW CREATE TABLE `_t_new`\G
*************************** 1. row ***************************
       Table: _t_new
Create Table: CREATE TABLE `_t_new` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

从上述结果可以看出,已成功添加了字段c1。

3. 建立触发器

>CREATE TRIGGER `pt_osc_test_a_del` AFTER DELETE ON `t` FOR EACH ROW DELETE IGNORE FROM `_t_new` WHERE `_t_new`.`id` <=> t.`id`;
 
>CREATE TRIGGER `pt_osc_test_a_upd` AFTER UPDATE ON `t` FOR EACH ROW REPLACE INTO `_t_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`);
 
>CREATE TRIGGER `pt_osc_test_a_ins` AFTER INSERT ON `t` FOR EACH ROW REPLACE INTO `_t_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`);

4.

pt-online-schema-change解读

pt-query-advisor

根据一些规则分析查询语句,对可能的问题提出建议。

pt-show-grants

规范化和打印SQL权限,使在复制、比较MySQL权限以及进行版本控制的时候更有效率。

pt-upgrade

比较多台服务器上执行的查询的不同。

性能类工具

pt-index-usage

从log文件中读取查询语句,并用explain分析它们是如何利用索引,分析完成之后会生成一份关于索引没有被查询使用过的报告。

pt-pmp

跟踪聚合查询的的GBD堆栈,并将跟踪信息汇总。

pt-visual-explain

格式化explain出来的执行计划,按照tree方式输出,方便阅读。

配置类工具

pt-config-diff

比较MySQL配置文件和服务器参数。

pt-mysql-summary

精细地对MySQL的配置和status信息进行汇总。

pt-variable-advisor

分析MySQL的参数变量,并对可能存在的问题提出建议。

监控类工具

pt-deadlock-logger

提取和记录MySQL死锁的相关信息。

pt-fk-error-logger

提取和记录MySQL外键错误信息

pt-mext

并行查看show global status的多个样本的信息

pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具,它也可以分析binlog、General log、slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,然后就可以借助分析结果找出问题进行优化。

参数

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

–create-review-table
使用–review参数把分析结果输出到表中时,如果没有表就自动创建。

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

–create-history-table
使用–history参数把分析结果输出到表中时,如果没有表就自动创建。

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

–limit
限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是95%则按总响应时间占比从大到小排序,输出到总和达到95%位置截止。

–log=s
指定输出的日志文件。

–output
分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于阅读。

–since
从该指定日期开始分析。

–until
截止时间,配合—since可以分析一段时间内的慢查询。

示例

# 分析本地的慢查询文件
# pt-query-digest --report /opt/tmp/mysql.slow

# 3.5s user time, 150ms system time, 52.87M rss, 230.81M vsz
# Current date: Sat Aug 18 18:59:05 2018
# Hostname: gh-mysql-jp-test01.gh.sankuai.com
# Files: /opt/tmp/mysql.slow
# Overall: 640 total, 40 unique, 0 QPS, 0x concurrency ___________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        373042s   103ms 227251s    583s   580ms   9269s   219ms
# Lock time            16s       0    93ms    26ms    56ms    25ms    40ms
# Rows sent          9.06M       0   2.71M  14.49k    1.96 155.91k       0
# Rows examine     134.32M       0   5.42M 214.91k 462.39k 417.90k       0
# Rows affecte      15.33M       0   2.71M  24.52k  24.75k 153.29k  20.37k
# Bytes sent         1.39G       0 163.93M   2.22M   1.96k  16.50M   54.21
# Merge passes           4       0       1    0.01       0    0.08       0
# Tmp tables            21       0       1    0.03       0    0.18       0
# Tmp disk tbl          21       0       1    0.03       0    0.18       0
# Tmp tbl size     336.00k       0  16.00k  537.60       0   2.84k       0
# Query size       314.48M      15 1005.72k 503.17k 961.27k 478.78k 717.31k
# InnoDB:
# IO r bytes       944.00k       0 944.00k   1.62k       0  37.88k       0
# IO r ops              59       0      59    0.10       0    2.36       0
# IO r wait              0       0       0       0       0       0       0
# pages distin     104.82k       0   6.50k  184.12 1012.63  463.97       0
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       0% yes,  99% no
# Filesort on    0% yes,  99% no
# Full scan     12% yes,  87% no
# Tmp table      3% yes,  96% no
# Tmp table on   3% yes,  96% no

# Profile
# Rank Query ID           Response time     Calls R/Call     V/M   Item
# ==== ================== ================= ===== ========== ===== =======
#    1 0xCBC895EB5A636382 372803.7252 99.9%    13 28677.2096 12... ADMIN BINLOG DUMP
# MISC 0xMISC                238.1040  0.1%   627     0.3798   0.0 <39 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0xCBC895EB5A636382 at byte 152803249
# This item is included in the report because it matches --limit.
# Scores: V/M = 121460.68
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      13
# Exec time     99 372804s    148s 227251s  28677s  85838s  59018s   5411s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     5  75.59M     504  75.02M   5.81M 362.29k  19.22M   9.16k
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0     442      34      34      34      34       0      34
# String:
# Databases    employees
# Hosts
# Time         2018-07-20... (1/7%), 2018-07-20... (1/7%)... 11 more
# Users        replication
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
administrator command: Binlog Dump\G

上述结果总共分为三部分

第一部分:总体统计结果
Overall:总共有多少条查询
Time range:查询执行的时间范围
unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询
total:总计 min:最小 max:最大 avg:平均
95%:把所有值从小到大排列,位置位于95%的那个数,这个数一般最具有参考价值
median:中位数,把所有值从小到大排列,位置位于中间那个数

# 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
# 3.5s user time, 150ms system time, 52.87M rss, 230.81M vsz
# 工具执行时间
# Current date: Sat Aug 18 18:59:05 2018
# 运行分析工具的主机名
# Hostname: test01.gh.com
# 被分析的文件名
# Files: /opt/tmp/mysql.slow
# 语句总数量,唯一的语句数量,QPS,并发数
# Overall: 640 total, 40 unique, 0 QPS, 0x concurrency ___________________
# 属性 总计 最小 最大 平均 95% 标准 中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        373042s   103ms 227251s    583s   580ms   9269s   219ms
# Lock time            16s       0    93ms    26ms    56ms    25ms    40ms
# Rows sent          9.06M       0   2.71M  14.49k    1.96 155.91k       0
# Rows examine     134.32M       0   5.42M 214.91k 462.39k 417.90k       0
# Rows affecte      15.33M       0   2.71M  24.52k  24.75k 153.29k  20.37k
# Bytes sent         1.39G       0 163.93M   2.22M   1.96k  16.50M   54.21
# Merge passes           4       0       1    0.01       0    0.08       0
# Tmp tables            21       0       1    0.03       0    0.18       0
# Tmp disk tbl          21       0       1    0.03       0    0.18       0
# Tmp tbl size     336.00k       0  16.00k  537.60       0   2.84k       0
# Query size       314.48M      15 1005.72k 503.17k 961.27k 478.78k 717.31k
# InnoDB:
# IO r bytes       944.00k       0 944.00k   1.62k       0  37.88k       0
# IO r ops              59       0      59    0.10       0    2.36       0
# IO r wait              0       0       0       0       0       0       0
# pages distin     104.82k       0   6.50k  184.12 1012.63  463.97       0
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       0% yes,  99% no
# Filesort on    0% yes,  99% no
# Full scan     12% yes,  87% no
# Tmp table      3% yes,  96% no
# Tmp table on   3% yes,  96% no

第二部分:查询分组统计结果

Rank:所有语句的排名,默认按查询时间降序排列,通过–order-by指定
Query ID:语句的ID,(去掉多余空格和文本字符,计算hash值)
Response:总的响应时间
time:该查询在本次分析中总的时间占比
calls:执行次数,即本次分析总共有多少条这种类型的查询语句
R/Call:平均每次执行的响应时间
V/M:响应时间Variance-to-mean的比率
Item:查询对象

# Profile
# Rank Query ID           Response time     Calls R/Call     V/M   Item
# ==== ================== ================= ===== ========== ===== =======
#    1 0xCBC895EB5A636382 372803.7252 99.9%    13 28677.2096 12... ADMIN BINLOG DUMP
# MISC 0xMISC                238.1040  0.1%   627     0.3798   0.0 <39 ITEMS>

第三部分:每一种查询的详细统计结果
由下面查询的详细统计结果,最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID:查询的ID号,和上图的Query ID对应
Databases:数据库名
Users:各个用户执行的次数(占比)
Query_time distribution :查询时间分布, 长短体现区间占比,本例中1s-10s之间查询数量是10s以上的两倍。
Tables:查询中涉及到的表
Explain:SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xCBC895EB5A636382 at byte 152803249
# This item is included in the report because it matches --limit.
# Scores: V/M = 121460.68
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      13
# Exec time     99 372804s    148s 227251s  28677s  85838s  59018s   5411s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     5  75.59M     504  75.02M   5.81M 362.29k  19.22M   9.16k
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0     442      34      34      34      34       0      34
# String:
# Databases    employees
# Hosts
# Time         2018-07-20... (1/7%), 2018-07-20... (1/7%)... 11 more
# Users        replication
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
administrator command: Binlog Dump\G
# 报告最近半个小时的慢查询:
# pt-query-digest --report --since 1800s /opt/tmp/mysql.slow

# 报告一个时间段的慢查询:
# pt-query-digest --report --since '2018-08-10 21:00:00' --until '2018-08-11 01:30:50' /opt/tmp/mysql.slow

# 报告只含select语句的慢查询
# pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /opt/tmp/mysql.slow

#报告针对某个用户的慢查询
# pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /opt/tmp/mysql.slow

# 报告所有的全表扫描或full join的慢查询
# pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /opt/tmp/mysql.slow

# 重新回顾慢查询日志,并将结果保存到query_review
# 1. 创建表
> CREATE TABLE query_review (
    checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    fingerprint TEXT NOT NULL,
    sample TEXT NOT NULL,
    first_seen DATETIME,
    last_seen DATETIME,
    reviewed_by VARCHAR(20),
    reviewed_on DATETIME,
    comments TEXT
);
# 2. 执行命令
# pt-query-digest -u root -p "" --review D=employees,t=query_review /opt/tmp/mysql.slow

# 把查询保存到query_history表
# pt-query-digest -u root -p "" --history D=employees,t=query_history --create-history-table /opt/tmp/mysql.slow

# 分析binlog
# mysqlbinlog mysql-bin.000001 > mysql-bin000001.sql
# pt-query-digest --type=binlog mysql-bin000001.sql > slow_report.log
# cat slow_report.log

# 150ms user time, 10ms system time, 24.50M rss, 202.43M vsz
# Current date: Sat Aug 18 18:54:43 2018
# Hostname: test01.gh.com
# Files: mysql-bin000001.sql
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: unknown
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size            44      44      44      44      44       0      44


# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

MySQL慢查询之pt-query-digest分析慢查询日志

pt-trend

基于一组时间序列的数据点做统计。

复制类工具

pt-heartbeat

监控MySQL主从复制延迟。

pt-slave-delay

设置从服务器落后于主服务器指定时间。

pt-slave-find

查找和打印MySQL所有从服务器复制层级关系。

pt-slave-restart

监视MySQL复制错误,并当复制停止的时候尝试重启MySQL复制。

pt-table-checksum

检查MySQL复制一致性。pt-table-checksum 在主上执行检查语句在线检查 mysql 复 制的一致性,生成 replace 语句,然后通过复制传递到从,再通过 update 更新 master_src 的值。通过检测从库上 this_src 和 master_src 的 值从而判断复制是否一致。

# 比较employees数据库同步是否一致,结果显示所有表
# pt-table-checksum --nocheck-replication-filters --databases=employees --replicate=employees.checksums --create-replicate-table --host=10.27 --port 5002 -u root -p ""
# 第一次运行该命令需要添加加--create-replicate-table参数

pt-table-sync

高效同步MySQL表的数据。

系统类工具

pt-diskstats

一个对GNU/LINUX的交互式监控工具。

pt-fifo-split

模拟切割文件并通过管道传递给先入先出队列而不用真正的切割文件。

pt-summary

友好地收集和显示系统细分析概况。

pt-stalk

出现问题的时候收集MySQL的用于诊断的数据。

实用类工具

pt-archiver

将MySQL数据表中的记录归档到另一个表或文件,也可以直接进行记录的删除操作。

pt-find

查找MySQL表并执行指定的命令,和find命令类似。

pt-kill

kill符合指定条件的MySQL语句

-- 执行一个长查询
> select sleep(5);

执行命令

# 显示运行时间超过2秒的语句,--interval 指定每隔2秒检查一次
# pt-kill --busy-time 2 --print --u root -p "" --interval 2;
# 2018-07-26T16:52:09 KILL 16945 (Query 2 sec) select sleep(4)
# 2018-07-26T16:52:11 KILL 16945 (Query 4 sec) select sleep(4)

# 杀死运行时间超过2秒的语句
# pt-kill --busy-time 2 --kill --u root -p "" --interval 2;

percona-toolkit 使用教程

gh-ost

这里再介绍一个与pt-online-schema-change对应的工具 gh-ost。它是一款使用go开发的MySQL在线该表工具,避免了pt-online-schema-change的一些缺点。

gh-ost解析
gh-ost —— Github 的在线 MySQL 数据库移植工具