这个工具同样来自percona-toolkit

该工具集合的其他工具

MySQL Slave异常关机的处理 (pt-slave-restart)

http://blog.itpub.net/29254281/viewspace-1183545/

 

验证MySQL主从一致性(pt-table-checksum&pt-table-sync)

http://blog.itpub.net/29254281/viewspace-1208415/

 

 

slow log 设置：

 

 cat my.cnf 

# For advice on how to change settings please see

[mysqld]

#skip-grant-tables

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

 

 

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

log_bin=mysql-bin

slow_query_log=on

long_query_time=1

#long_query_time = 1 

slow_query_log = 1 

slow_query_log_file = slow.log 

 

************************************************************

mysql查看：

 

mysql> show variables like "%query%";   

+------------------------------+----------+

| Variable_name                | Value    |

+------------------------------+----------+

| binlog_rows_query_log_events | OFF      |

| ft_query_expansion_limit     | 20       |

| have_query_cache             | YES      |

| long_query_time              | 1.000000 |

| query_alloc_block_size       | 8192     |

| query_cache_limit            | 1048576  |

| query_cache_min_res_unit     | 4096     |

| query_cache_size             | 1048576  |

| query_cache_type             | OFF      |

| query_cache_wlock_invalidate | OFF      |

| query_prealloc_size          | 8192     |

| slow_query_log               | ON       |

| slow_query_log_file          | slow.log |

+------------------------------+----------+

13 rows in set (0.00 sec)

 

 

pt-query-digest：分析

     常用选项:  —create-review-table     当使用—review参数，把分析结果输出到表中

                      —create-history-table     但是用—history参数，把分析结果输出到表中

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

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

                      —host     HostName

                      —user     用户名

                      —password     密码

                      —history     将分析结果保存到表中，分析结果比较详细

                      —review      将分析结果保存到表中

                      —output     分析结果输出类型

                      —since     从什么时间开始分析，值为字符串

                      —until     截止时间，配合since一起分析

     分析结果:

               

     其中: 

               Overall:总共有多少条查询

               unique:唯一查询数量

               Time range:查询执行的时间范围

               total:总计

               min:最小

               max:最大

               avg:平均

               95%:95%的查询时间，重点分析

               median:中位数，把所有值从小到大排列，位置位于中间那个数

 

     详细的分析结果：

               

               Response:总的响应时间

               time:该查询在本次分析中占用的时间比

               Calls:执行次数

               R/Call:平均每次执行的响应时间

               Item:查询对象

 

          每一条查询的详细分析结果：

               

     ID:查询的ID号，和上图的Query ID对应

     Databases: 数据库名

     Users:执行的用户

     Query_time distribution:查询的时间分布(此图说明，查询几乎都在1ms-10ms之间)

     Tables:查询涉及到的表

     EXPLAIN:SQL语句

 

pt-query-digest安装：

yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes

*********

rpm包安装：

rpm -ivh percona-toolkit-2.1.7-1.noarch.rpm

warning: percona-toolkit-2.1.7-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing...                ########################################### [100%]

   1:percona-toolkit        ########################################### [100%]

 

 

 

它可以剖析各种日志,从而分析性能问题

包括

1.慢日志(默认)

2.tcpdump

3.general log

4.binlog

5.show processlist

 

实验通过tpcc模拟一定的压力,并开启binlog,general log,slow log和tcpdump收集日志。

关于tpcc参考:http://blog.itpub.net/29254281/viewspace-1195589/

 

1.slow log方式

将slow_query_log设置为on

long_query_time设置为0

重新连接服务器(一般意味着中间件服务器重启)

这样慢日志将记录所有的操作。

 

4.用法示例
(1)直接分析慢查询文件:
pt-query-digest  slow.log > slow_report.log

(2)分析最近12小时内的查询：

pt-query-digest  --since=12h  slow.log > slow_report2.log

 

(3)分析指定时间范围内的查询：

pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_report3.log

 

(4)分析指含有select语句的慢查询

pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

 

(5) 针对某个用户的慢查询

pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

 

(6) 查询所有所有的全表扫描或full join的慢查询

pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

 

(7)把查询保存到query_review表

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log

 

(8)把查询保存到query_history表

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_ history--create-review-table  slow.log_20140401

pt-query-digest  --user=root –password=abc123--review  h=localhost,D=test,t=query_history--create-review-table  slow.log_20140402

 

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

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

 

(10)分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

 

(11)分析general log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

 

 

 

通过pt-query-digest分析慢日志文件,生成剖析报告

首先是概要信息,包括一共有54.17k个操作,46个语句,平均每秒1.55k查询,剖析报告覆盖的时间等。

概要还包括类似Top SQL的分析

可以从响应时间查看对服务器性能影响最大的SQL,其中R/Call指的是  平均每次执行的响应时间 ResponseTime/Calls

然后就是每条SQL的详细剖析报告,

pct指的是该项占概要报告中的百分比

2.tcpdump方式

因为慢日志方式需要重新连接,而生产环境重启中间件是非常昂贵的操作。

所以在数据库服务器上抓包分析也是一种不错的选择。

pt-query-digest对于抓包有一定的格式。(-x -nn -q -tttt)

-s:源端口

-c:抓包的数量

tcpdump -s 65535 -x -nn -q -tttt -i any -c 100000 port 3306 > test.dmp

在完成抓包采集之后进行分析

pt-query-digest --limit 10 --type tcpdump test.dmp

limit 10 限制显示排名前10的操作

 

其他方式生成的信息会有缺失,可能达不到预期的效果。

而processlist方式实验失败,尚不清楚如何使用这种方式剖析性能。