mysql慢查询分析工具和分析方法

阅读量：5888 次

发布时间：2019-06-19

本文共 11232 字，大约阅读时间需要 37 分钟。

1.mysql慢查询分析工具

1.参考文档：

官方文档：

数据库管理员一般是用percona的toolkit工具来分析MySQL慢查询记录，但是不够直观。

下面介绍一款比较直观的工具来统计分析MySQL慢查询记录anemometer。

在使用之前需要安装percona的toolkit工具，anemometer提供web界面。

anemometer介绍参见：

1.1安装-perconatoolkit工具

wgethttps://www.percona.com/downloads/percona-toolkit/2.2.11/RPM/percona-toolkit-2.2.11-1.noarch.rpm

yum install perl-IO-Socket-SSL perl-DBD-MySQLperl-Time-HiRes perl-DBI -y

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

会在/usr/bin 下生成命令文件：

[root@cacti mysqld]# ls /usr/bin/pt-query-digest/usr/bin/pt-visual-explain /usr/bin/pt-variable-advisor

/usr/bin/pt-query-digest /usr/bin/pt-variable-advisor /usr/bin/pt-visual-explain

1.2安装测试环境介绍：

服务器221.195.1.245机器

[root@cacti anemometer]# httpd -v

Server version: Apache/2.2.15 (Unix)

Server built: Jul 18 2016 15:24:00

Php-5.5.38

[root@cacti anemometer]# mysql -V

mysql Ver14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

mysql 要开启慢查询日志

2.安装步骤：

2.1.下载源码：

git clone git://github.com/box/Anemometer.gitanemometer

或者是

git clone https://github.com/box/Anemometer.gitanemometer

cd anemometer

`2.2生成存放慢日志的库表`

导入表结构在mysql上生成两个表来存放慢查询日志的数据

mysql -uroot -p </var/www/html/anemometer/install.sql

mysql> grant ALL ON slow_query_log.* to'anemometer'@'localhost' IDENTIFIED BY 'wujianwei';

mysql> grant SELECT ON *.* to'anemometer'@'localhost' IDENTIFIED BY 'wujianwei';

mysql> use slow_query_log;show tables;

Database changed

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

| Tables_in_slow_query_log |

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

| global_query_review |

| global_query_review_history |

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

2 rows in set (0.00 sec)

2.3拷贝链接数据库的配置文件

cd anemometer/conf

cp sample.config.inc.php config.inc.php

2.4. 配置anemometer

参考这个文档：配置

cat -n/var/www/html/anemometer/conf/config.inc.php|sed -n '63,74p;248,252p;284,289p'

63 $conf['datasources']['localhost_history'] =array(

64 'host' => 'localhost',

65 'port' => 3306,

66 'db' => 'slow_query_log',

67 'user' => 'anemometer',

68 'password' => 'wujianwei',

69 'tables' => array(

70 'global_query_review'=> 'fact',

71 'global_query_review_history'=> 'dimension'

72 ),

73 'source_type' =>'slow_query_log'

74 );

248 $conf['plugins'] = array(

249

250 'visual_explain' =>'/usr/bin/pt-visual-explain',

251 # perconatoolkit has removed query advisor

252 'query_advisor' => '/usr/bin/pt-query-advisor',

284 $conn['user'] ='anemometer';

285 $conn['password'] ='wujianwei';

286

287 return $conn;

288 },

289 );

测试成功的配置文件：

2.5 存放获取到的慢查询日志数据到慢查询库表中

注意：

存放慢查询数据到慢查询库表中需要注意perconatoolkit工具的版本号，版本不通，采用pt-query-digest获取慢查询日志的参数是不一样的。

参考官网说明：

Put some data in the DB

Next, grab that slow query log file you have(mine's called "slow.log"!), and run pt-query-digest on it: NOTE: I'm using a BASH 3.0 shellhere on my MySQL database server! This is so the "$HOSTNAME" variableproperly replaces with "db.example.com")

For pt-query-digest version < 2.2

$ pt-query-digest --user=anemometer --password=superSecurePass \

--reviewh=db.example.com,D=slow_query_log,t=global_query_review \

--review-historyh=db.example.com,D=slow_query_log,t=global_query_review_history \

--no-report --limit=0% \

--filter="\$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" \

/var/lib/mysql/db.example.com-slow.log

For pt-query-digest version >= 2.2

$ pt-query-digest --user=anemometer --password=superSecurePass \

--reviewh=db.example.com,D=slow_query_log,t=global_query_review \

--historyh=db.example.com,D=slow_query_log,t=global_query_review_history \

--no-report --limit=0% \

--filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" \

/var/lib/mysql/db.example.com-slow.log

下面是测试采用的pt-query-digest参数来获取慢日志数据：

pt-query-digest --user=anemometer--password=wujianwei --review h=localhost,D=slow_query_log,t=global_query_review--history h=localhost,D=slow_query_log,t=global_query_review_history--no-report --limit=0% --filter=" \$event->{Bytes} =length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\"" /var/run/mysqld/slowquery_2017051617.log

[root@cacti html]# pt-query-digest--user=anemometer --password=wujianwei --reviewh=localhost,D=slow_query_log,t=global_query_review --historyh=localhost,D=slow_query_log,t=global_query_review_history --no-report--limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and\$event->{hostname}=\"$HOSTNAME\""/var/run/mysqld/slowquery_2017051617.log

[root@cacti html]#

一切正常，没有错误输出

到此配置完成。

下面是httpd.conf文件配置

2.5 httpd.conf文件配置

[root@cacti mysqld]# tail -7/etc/httpd/conf/httpd.conf

Listen 12336

NameVirtualHost *:12336

ServerName 221.15.10.25:12336

DocumentRoot "/var/www/html/anemometer"

</VirtualHost>

[root@cacti mysqld]# /etc/init.d/httpdrestart

Stopping httpd: [ OK ]

Starting httpd: [ OK ]

2.6浏览器显示：

2.tcpdump慢查询分析方法

tcpdump -s 0 -l -w - dst xxx.xxx.xxx.xxxand port 3306 |strings

desc tab_user3L Y

("}@

v"~@

select * from tab_user where phone ='1371337%'order by desc id limit 1,2

L Y@

select * from tab_user where phone =1371337% orderby desc id limit 1,2

select * from tab_user where phone =1371337% orderby desc id limit 20

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

pt-query-digest --type tcpdump mysql.tcp.txt1

生成的文件：

[root@www1 ~]# cat mysql.tcp.txt1

# 490ms user time, 10ms system time, 23.08M rss,198.90M vsz

# Current date: Sat May 20 22:42:51 2017

# Hostname: www1.2166.com

# Files: mysql.tcp.txt

# Overall: 333 total, 1 unique, 20.63 QPS, 0.01xconcurrency _____________

# Time range: 2017-05-20 22:37:22.575782 to22:37:38.720840

# Attribute total min max avg 95% stddev median

# ============ ======= ======= ======= ======= ======= ======= =======

# Exec time 100ms 96us 1ms 299us 384us 86us 287us

# Rows affecte 333 1 1 1 1 0 1

# Query size 16.58k 51 51 51 51 0 51

# Warning coun 0 0 0 0 0 0 0

# Profile

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

# ==== ================== ============= =========== ===== ===============

# 10x6C7F3DF62B32E3F4 0.0996 100.0% 3330.0003 0.00 UPDATE dr_stats

# Query 1: 20.63 QPS, 0.01x concurrency, ID0x6C7F3DF62B32E3F4 at byte 248845

# This item is included in the report because itmatches --limit.

# Scores: V/M = 0.00

# Time range: 2017-05-20 22:37:22.575782 to22:37:38.720840

# Attribute pct total min max avg 95% stddev median

# ============ === ======= ======= ======= ============== ======= =======

# Count 100 333

# Exec time 100 100ms 96us 1ms 299us 384us 86us 287us

# Rows affecte 100 333 1 1 1 1 0 1

# Query size 100 16.58k 51 51 51 51 0 51

# Warning coun 0 0 0 0 0 0 0 0

# String:

# Hosts 127.0.0.1

# Query_time distribution

# 1us

# 10us #

# 100us ################################################################

# 1ms #

# 10ms

# 100ms

# 1s

# 10s+

# Tables

# SHOWTABLE STATUS LIKE 'dr_stats'\G

# SHOWCREATE TABLE `dr_stats`\G

UPDATE `dr_stats` SET `sync`=1 WHERE id='14820620'\G

# Converted for EXPLAIN

# EXPLAIN /*!50100 PARTITIONS*/

select `sync`=1 from `dr_stats` where id='14820620' \G

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

3.mysql性能分析工具

3.1mysqladmin

1.debug 信息输出

mysqladmin -uroot -p'w3@TG&45BvD345' -h127.0.0.1 -P3306 debug

输出信息到mysql的errorlog日志文件内

2. extended-status

可以获得所有MySQL性能指标，即show global status的输出，因为多数这些指标都是累计值，如果想了解当前的状态，则需要进行一次差值计算，这就是mysqladmin extended-status的一个额外功能，非常实用。

默认的，使用extended-status，看到也是累计值，但是，加上参数-r(--relative)，就可以看到各个指标的差值，配合参数-i(--sleep)就可以指定刷新的频率，那么就有如下命令

[root@localhost ~]# mysqladmin -uroot -p'erG&Bveret'-h 127.0.0.1 -P3306 -r -i 1 extended-status|grep Handler_commit

| Handler_commit | 517760180 |

| Handler_commit | 222 |

| Handler_commit | 149 |

| Handler_commit | 194 |

| Handler_commit | 203 |

| Handler_commit | 149 |

结合grep一块使用

[root@localhost~]# mysqladmin -uroot -p'w3@TG&BvDOpQ#X' -h 127.0.0.1 -P3306 -r -i 1extended-status|egrep "Handler_commit|Bytes_received| Bytes_sent"

| Bytes_received | 113594008546 |

| Bytes_sent |270109905767 |

| Handler_commit | 517839061 |

| Bytes_received | 33551 |

| Bytes_sent | 73427 |

| Handler_commit | 165 |

| Bytes_received | 26124 |

| Bytes_sent | 62250 |

| Handler_commit | 133 |

| Bytes_received | 36482 |

| Bytes_sent | 104021 |

简单配合awk使用

线上一般参看mysql的参数：

[root@localhost ~]# mysqladmin -uroot-p'w3@TG&BvX' -h127.0.0.1 -P3306 -r -i 1 ext |awk -F"|" '{\

if($2 ~/Variable_name/){\

print" <------------- " strftime("%H:%M:%S") " ------------->";\

print $2$3;\

更详细的写法使用

[root@localhost ~]# mysqladmin -P3306 -uroot -p'w3@erTG&BvD'-h127.0.0.1 -r -i 1 ext |awk -F"|" "BEGIN{ count=0; }"'{if($2 ~ /Variable_name/ && ++count == 1){\

print"----------|---------|--- MySQL Command Status --|----- Innodb rowoperation ----|-- Buffer Pool Read --";\

print"---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";\

else if ($2 ~ /Queries/){queries=$3;}\

else if ($2 ~ /Com_select /){com_select=$3;}\

else if ($2 ~ /Com_insert /){com_insert=$3;}\

else if ($2 ~ /Com_update /){com_update=$3;}\

else if ($2 ~ /Com_delete /){com_delete=$3;}\

else if ($2 ~/Innodb_rows_read/){innodb_rows_read=$3;}\

else if ($2 ~/Innodb_rows_deleted/){innodb_rows_deleted=$3;}\

else if ($2 ~/Innodb_rows_inserted/){innodb_rows_inserted=$3;}\

else if ($2 ~/Innodb_rows_updated/){innodb_rows_updated=$3;}\

else if ($2 ~/Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\

else if ($2 ~/Innodb_buffer_pool_reads/){innodb_phr=$3;}\

else if ($2 ~ /Uptime / && count >= 2){\

printf(" %s |%9d",strftime("%H:%M:%S"),queries);\

printf("|%6d %6d %6d%6d",com_select,com_insert,com_update,com_delete);\

printf("|%6d %8d %7d%7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\

printf("|%10d %11d\n",innodb_lor,innodb_phr);\

}}'

输出代码：

----------|---------|---MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --

---Time---|---QPS---|selectinsert update delete| read insertedupdated deleted| logical physical

18:30:54 | 499| 68 64 14 0| 54 64 14 0| 1618 3

18:30:55 | 467| 64 54 13 0| 53 54 13 0| 1674 2

18:30:56 | 457| 57 66 13 0| 44 66 13 0| 1514 0

18:30:57 | 545| 79 51 15 0| 64 51 15 0| 1602 2

18:30:58 | 493| 70 47 22 0| 61 47 22 0| 1525 4

18:30:59 | 287| 35 43 8 0| 31 43 8 0| 972 0

18:31:00 | 297| 41 29 10 0| 38 29 10 0| 908 1

18:31:01 | 445| 63 46 13 0| 52 46 13 0| 1354 0

18:31:02 | 526| 73 46 24 0| 86 46 24 0| 1649 8

18:31:02 | 500| 69 45 19 0| 80 45 19 0| 1584 1

参考文档：

http://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/

3.ping参数

检查数据库是否存活

[root@localhost ~]# mysqladmin -uroot -p ping

Enter password:

mysqld is alive

4.设置mysql超级oot密码

Mysqladmin –uroot –p

5.修改mysql的root密码

mysqladmin -u root-poldpassword password 'newpassword'

6.processlist

显示服务器所有运行的进程：

-i参数意思是每隔几秒运行一次

mysqladmin -uroot -p -i 4 processlist

7. mysqladmin执行kill进程

mysqladmin-uroot -p kill idnum

8.同时执行多个命令

mysqladmin -u root -pprocess status version

参考资料：http://blog.csdn.net/radkitty/article/details/4627400

.mysql参数详解

http://blog.csdn.net/liehuo123/article/details/26052159

转载于:https://blog.51cto.com/wujianwei/1941711

你可能感兴趣的文章

sql server 无法用sql server身份验证

在controller中无法通过注解@Value获取到配置文件中定义的值

查看>>

常用Mysql或者PostGresql或者Greenplum的语句总结。

查看>>

用实例讲DynamicResource与StaticResource的区别

查看>>

工控随笔_12_西门子_WinCC的VBS脚本_03_变量类型

微信nickname乱码（emoji）及mysql编码格式设置（utf8mb4）解决的过程

[Leetcode] Search in Rotated Sorted Array

查看>>

委托、Lambda表达式、事件系列02,什么时候该用委托

查看>>

在ASP.NET MVC控制器中获取链接中的路由数据

查看>>

使用ASP.NET Atlas SortBehavior实现客户端排序

查看>>