第47章　Percona Toolkit常用工具详解 - xiaoboluo768/qianjinliangfang GitHub Wiki

47.1.2　实战演示

1．解析慢查询日志

[root@localhost ~]# pt-query-digest /data/mysqldata1/slowlog/slow-query.log > slow_\ report.log
......

2．解析binlog

# 首先使用mysqlbinlog命令解析binlog文件
[root@localhost binlog]# mysqlbinlog -vv mysql-bin.000462 > parse_binlog.txt

# 然后使用pt-query-digest工具分析binlog解析文本
[root@localhost binlog]# pt-query-digest --type=binlog parse_binlog.txt > slow_binlog.\ txt

3．解析tcpdump抓包数据

# 首先使用tcpdump抓包
[root@localhost ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
......

# 然后使用PT工具进行解析
pt-query-digest --type=tcpdump mysql.tcp.txt > slow_tcpdump.txt

4．解析普通查询日志

[root@localhost ~]# pt-query-digest --type=genlog localhost.log > slow_report_genlog.log

5．解析rawlog

 [root@localhost ~]# pt-query-digest --type=rawlog example.sql > slow_report_rawlog.log

6．其他用法示例

[root@localhost ~]# pt-query-digest  --since=12h  slow.log > slow_report12.log
[root@localhost ~]# pt-query-digest slow.log --since '2014-04-17 09:30:00' –until\ '2014-04-17 10:00:00' > slow_report_timerange.log
[root@localhost ~]# pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i'\ slow.log > slow_report_finselect.log
[root@localhost ~]# pt-query-digest--filter '($event->{user} || "") =~ m/^root/i'\ slow.log > slow_report_userroot.log
[root@localhost ~]# pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes")\ ||(($event->{Full_join} || "") eq "yes")' slow.log > slow_report_fullscan_fulljoin.log
# 按照慢查询日志的db属性进行排序
[root@localhost ~]# pt-query-digest /data/mysqldata1/slowlog/slow-query.log --group-\ by=db > slow_report.log

# 按照慢查询日志的host属性进行排序
[root@localhost ~]# pt-query-digest /data/mysqldata1/slowlog/slow-query.log --group-\ by=host > slow_report.log

# 按照慢查询日志的user属性进行排序
[root@localhost ~]# pt-query-digest /data/mysqldata1/slowlog/slow-query.log --group-\ by=user > slow_report.log
[root@localhost ~]# pt-query-digest --user=root --password=password --review h=localhost,\ D=test,t=query_review --create-review-table --no-report slow.log

# review表的表结构，可登录数据库执行语句查看
show create table test.query_review;
[root@localhost ~]# pt-query-digest --user=root --password=password --history h=localhost,\ D=test,t=query_history --create-history-table --no-report  slow.log

# history表的表结构，可登录数据库执行语句查看
mysql>show create table test.query_history;

47.2.2　实战演示

[root@10-10-66-253 ~]# pt-ioprofile --run-time=1800
	......
	
# 收集IOPS
[root@localhost ~]# pt-ioprofile --cell count
......

# 收集I/O吞吐量
[root@localhost ~]# pt-ioprofile --cell sizes
	......
	
# 修改为按照pid分组聚合I/O操作的耗时
[root@localhost ~]# pt-ioprofile --group-by pid
......

# 修改为按照all分组聚合I/O操作的耗时
[root@localhost ~]# pt-ioprofile --group-by all
......

47.3.2　实战演示

[root@localhost ~]# pt-index-usage /data/mysqldata1/slowlog/　slow-query.log -uroot\ -pletsg0
ALTER TABLE `sbtest`.`sbtest2` DROP KEY `k_2`; -- type:non-unique
......

[root@localhost ~]# pt-index-usage /data/mysqldata1/slowlog/ slow-query.log –uroot\ -pletsg0 --drop=all --tables=sbtest2
ALTER TABLE `sbtest`.`sbtest2` DROP KEY `i_u_k`; -- type:unique  # 在默认情况下唯一索引是不列出的，使用--drop=all选项之后就会列出未使用的唯一索引
ALTER TABLE `sbtest`.`sbtest2` DROP KEY `k_2`; -- type:non-unique

# 使用--create-save-results-database选项，当指定的数据库不存在时自动创建数据库；使用--save-results-database选项的DSN子选项，指定需要连接的数据库信息；使用--empty-save-results- tables选项，清理之前表中的旧数据
[root@localhost ~]# pt-index-usage /data/mysqldata1/slowlog/slow-query.log –uroot\ -pletsg0 --drop=all --tables=sbtest2 --create-save-results-database --empty-save-results\ -tables --save-results-database D=pt_test,u=root,p=letsg0,h=127.0.0.1
ALTER TABLE `sbtest`.`sbtest2` DROP KEY `i_u_k`; -- type:unique
ALTER TABLE `sbtest`.`sbtest2` DROP KEY `k_2`; -- type:non-unique

# index_alternatives表，用于记录有相同fingerprints值的查询语句，在执行查询时索引被替换的执行次数（fingerprints值与查询语句的对应关系详见queries表），其中query_id字段表示查询ID，alt_idx字段表示被替换的索引
mysql> select * from index_alternatives limit 1;
......
1 row in set (0.00 sec)

# index_usage表，用于记录每个表有哪些访问语句(查询ID)、使用到了哪些索引以及对应的使用次数
mysql> select * from index_usage limit 1;
......
1 row in set (0.00 sec)

# indexes表，用于记录每个表中有哪些索引以及对应的使用次数
mysql> select * from indexes limit 1;
......
1 row in set (0.00 sec)

# queries表，用于记录在慢查询日志分析结果中有哪些访问语句、被格式化过的语句fingerprint值，以及一个示例语句文本
mysql> select * from queries limit 1;
......
1 row in set (0.00 sec)

# tables表，用于记录在慢查询日志分析结果中涉及哪些表，以及对应的表访问次数
mysql> select * from tables limit 1;
......
1 row in set (0.00 sec)

# 查看使用索引不固定的查询语句（例如：某个查询有时候使用A索引，有时候使用B索引），以及每个索引的选择次数等信息
SELECT iu.query_id, CONCAT_WS('.', iu.db, iu.tbl, iu.idx) AS idx,
   variations, iu.cnt, iu.cnt / total_cnt * 100 AS pct
FROM index_usage AS iu
   INNER JOIN (
      SELECT query_id, db, tbl, SUM(cnt) AS total_cnt,
        COUNT(*) AS variations
      FROM index_usage
      GROUP BY query_id, db, tbl
      HAVING COUNT(*) > 0
   ) AS qv USING(query_id, db, tbl);

# 查看有多个备选索引的查询语句，以及对应的使用索引、备选索引，语句执行次数等信息
SELECT CONCAT_WS('.', db, tbl, idx) AS idx_chosen,
   GROUP_CONCAT(DISTINCT alt_idx) AS alternatives,
   GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
FROM index_alternatives
GROUP BY db, tbl, idx
HAVING COUNT(*) > 1;

# 查看发生索引替换的查询语句，其中idx_considered为参考索引（被替换的索引），alternative_to为使用的索引
SELECT CONCAT_WS('.', db, tbl, alt_idx) AS idx_considered,
   GROUP_CONCAT(DISTINCT idx) AS alternative_to,
   GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
FROM index_alternatives
GROUP BY db, tbl, alt_idx
HAVING COUNT(*) > 1;

# 查看可能会被使用，但实际上从未被使用的索引（冗余索引），以及对应的查询语句信息
SELECT CONCAT_WS('.', i.db, i.tbl, i.idx) AS idx,
   alt.alternative_to, alt.queries, alt.cnt
FROM indexes AS i
   INNER JOIN (
      SELECT db, tbl, alt_idx, GROUP_CONCAT(DISTINCT idx) AS alternative_to,
         GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
      FROM index_alternatives
      GROUP BY db, tbl, alt_idx
      HAVING COUNT(*) > 1
   ) AS alt ON i.db = alt.db AND i.tbl = alt.tbl
     AND i.idx = alt.alt_idx
WHERE i.cnt = 0;

# 按库、表、索引分组统计索引的使用情况
SELECT i.tbl,i.idx, iu.usage_cnt, iu.usage_total,
   ia.alt_cnt, ia.alt_total
FROM indexes AS i
   LEFT OUTER JOIN (
      SELECT db, tbl, idx, COUNT(*) AS usage_cnt,
         SUM(cnt) AS usage_total, GROUP_CONCAT(query_id) AS used_by
      FROM index_usage
      GROUP BY db, tbl, idx
   ) AS iu ON i.db=iu.db AND i.tbl=iu.tbl AND i.idx = iu.idx
   LEFT OUTER JOIN (
      SELECT db, tbl, idx, COUNT(*) AS alt_cnt,
         SUM(cnt) AS alt_total,
         GROUP_CONCAT(query_id) AS alt_queries
      FROM index_alternatives
      GROUP BY db, tbl, idx
   ) AS ia ON i.db=ia.db AND i.tbl=ia.tbl AND i.idx = ia.idx;

# 按库、表、索引查看没有备选索引的查询语句的索引使用情况
SELECT i.db, i.tbl, i.idx, no_alt.queries
FROM indexes AS i
   INNER JOIN (
      SELECT iu.db, iu.tbl, iu.idx,
         GROUP_CONCAT(iu.query_id) AS queries
      FROM index_usage AS iu
         LEFT OUTER JOIN index_alternatives AS ia
            USING(db, tbl, idx)
      WHERE ia.db IS NULL
      GROUP BY iu.db, iu.tbl, iu.idx
   ) AS no_alt ON no_alt.db = i.db AND no_alt.tbl = i.tbl
      AND no_alt.idx = i.idx
ORDER BY i.db, i.tbl, i.idx, no_alt.queries;

47.4.2　实战演示

1．普通辅助索引重复

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

mysql> alter table sbtest1 add index i_k(k);
Query OK, 0 rows affected, 1 warning (6.60 sec)
Records: 0 Duplicates: 0 Warnings: 1

 CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `i_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

[root@localhost ~]# pt-duplicate-key-checker --defaults-file=/etc/ my.cnf –uroot\ -ppassword --databases=sbtest
......

2．显式指定主键字段的辅助索引重复

 CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `i_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

mysql> alter table sbtest1 add index i_k2(k,id);
Query OK, 0 rows affected (6.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `i_k` (`k`),
  KEY `i_k2` (`k`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

[root@localhost ~]# pt-duplicate-key-checker --defaults-file=/etc/ my.cnf –uroot\ -ppassword --databases=sbtest
......

mysql> alter table sbtest1 add index i_k(k);
Query OK, 0 rows affected, 1 warning (6.60 sec)
Records: 0 Duplicates: 0 Warnings: 1

Warning (Code 1831): Duplicate index 'i_k' defined on the table 'sbtest.sbtest1'. This is deprecated and will be disallowed in a future release.

47.5.1　pt-mysql-summary

2．实战演示

[root@localhost ~]# pt-mysql-summary --user root --password password -S /data/mysqldata1/\ sock/mysql.sock

[root@localhost ~]# pt-mysql-summary --user=admin --password=password --host=10.10.30.161\ --port 3306
......

47.5.2　pt-summary

2．实战演示 ··· [root@localhost ~]# pt-summary ...... ···

47.6.2　实战演示

··· [root@localhost ~]# pt-pmp --pid=pgrep mysqld |tail -1 ......

[root@localhost ~]# pstack pgrep mysqld |tail -1 > a.txt [root@localhost ~]# pt-pmp a.txt ...... ···

47.7.2　实战演示

1．以daemon方式运行pt-stalk

# 这里以TCP方式连接数据库
[root@localhost ~]# pt-stalk --collect-gdb --collect-oprofile --collect-strace\ --collect-tcpdump --defaults-file=/etc/my.cnf --dest=/var/lib/pt-stalk --threshold=2\ --user=admin --host=127.0.0.1 --port=3306 --password=letsg0 --variable=Threads_connected\ --log=/var/log/pt-stalk.log --daemonize

[root@localhost ~]# ps aux |grep pt-stalk
......

[root@localhost pt-stalk]# tailf -20 /var/log/pt-stalk.log 
......

[root@localhost pt-stalk]# ll                                                                                                                                                                                                        
......

2．以非daemon方式运行pt-stalk

# 这里以Socket方式连接数据库
[root@localhost pt-stalk]# pt-stalk --collect-gdb --collect-oprofile --collect-strace\ --collect-tcpdump --defaults-file=/etc/my.cnf --dest=/var/lib/pt-stalk --user=admin\ --socket=/home/mysql/data/mysqldata1/sock/mysql.sock --password=letsg0 --iterations=1\ --no-stalk --prefix=no-stalk
......

[root@localhost pt-stalk]# ll /var/lib/pt-stalk/no-stalk-*
......

47.8.2　实战演示

[root@localhost ~]# pt-sift /var/lib/pt-stalk/no-stalk-df 

	......
	
[root@localhost ~]# pt-sift /var/lib/pt-stalk/no-stalk

	......
	
[root@localhost ~]# pt-sift /var/lib/pt-stalk
......

上一篇：第46章　利用Prometheus Grafana 搭建炫酷的MySQL监控平台 |

第47章 Percona Toolkit常用工具详解 - xiaoboluo768/qianjinliangfang GitHub Wiki

47.1.2 实战演示

47.2.2 实战演示

47.3.2 实战演示

47.4.2 实战演示

47.5.1 pt-mysql-summary

47.5.2 pt-summary

47.6.2 实战演示

47.7.2 实战演示

47.8.2 实战演示

第47章　Percona Toolkit常用工具详解 - xiaoboluo768/qianjinliangfang GitHub Wiki

47.1.2　实战演示

47.2.2　实战演示

47.3.2　实战演示

47.4.2　实战演示

47.5.1　pt-mysql-summary

47.5.2　pt-summary

47.6.2　实战演示

47.7.2　实战演示

47.8.2　实战演示