mysql监视分析工具

最新推荐文章于 2024-05-10 00:47:51 发布

epee

最新推荐文章于 2024-05-10 00:47:51 发布

阅读量277

点赞数

mysqlbinlog 这个大家都应该知道的，用来看mysql的binlog的。mysql自带。

mysqldumpslow 用于分析mysql的slow log。自带的。结果如下。


Count: 1  Time=4.00s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[127.0.0.1]
  select * from user ignore key (PRIMARY)  where address = 'S' order by id asc limit N

Count: 3  Time=4.00s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[127.0.0.1]
  select * from user  where address = 'S' order by id asc limit N

Count: 2  Time=4.00s (8s)  Lock=0.00s (0s)  Rows=32.0 (64), root[root]@[127.0.0.1]
  select count(*), age from user group by age with rollup

mysql_explain_log 分析mysql的通用查询日志（用log选项打开的），自带的。需要注意的是用客户端连接mysql的时候需要指定数据库，使用use XXX选择数据库的时候此工具无法分辨出sql使用的是什么数据库。


explain_log     provided by http://www.mobile.de
===========     ================================

Index usage ------------------------------------

Table   test.user: ---
 count  key:
  1     PRIMARY
 count  possible_keys:
  1     PRIMARY
 count  type:
  1     ALL
  1     range

Queries causing table scans -------------------

EXPLAIN select * from test.user
Sum: 1 table scans

Summary ---------------------------------------

Select:         4 queries
Update:         0 queries

Init:           0 times
Field:          6 times
Refresh:        0 times
Query:          35 times
Statistics:     0 times

Logfile:        59 lines
Started:        Tue Mar 27 10:45:12 2007
Finished:       Tue Mar 27 10:45:12 2007

perror 显示错误码对应的错误信息。mysql自带。


shell>perror 13
OS error code  13:  Permission denied

mysqlslap 一个性能测试的工具。mysql的test suite中带的。

mtop 一个进程监视的工具，可以直接在里面显示出explain的结果。也可以使用watch -n 1 mysqladmin status processlist来监视。


127.0.0.1  mysqld 5.0.27-standard-log up 0 day(s),  0:10 hrs
1 threads: 1 running, 1 cached. Queries/slow: 26/0 Cache Hit: 100.00%
Opened tables: 0  RRN: 467  TLW: 0  SFJ: 0  SMP: 0  QPS: 0

ID       USER     HOST             DB           TIME   COMMAND STATE        INFO
26       root     1270.0.1:52841                       Query                show full processlist

mysql_explain_slow_log 和mysql_explain_log差不多，不过这个是分析slow log的。


mysql_explain_slow_log
======================

Index usage ------------------------------------

Table   test: ---
 count  type:
  10    ALL

Table   test.click: ---
 count  type:
  1     ALL

Table   test.user: ---
 count  key:
  1     username,useraa
  9     usertime
  16    PRIMARY
  29    useraa
 count  possible_keys:
  1     PRIMARY
  7     useraa,username
  9     usertime
  23    useraa
 count  type:
  1     index_merge
  2     range
  23    ref
  29    index
  31    ALL

Queries causing table scans -------------------
EXPLAIN select * from test.user
EXPLAIN select * from test.user order by rand() limit 5
省略若干行...

Sum: 56 table scans

Summary ---------------------------------------

Select:         97 queries
Update:         0 queries
Load:   33 queries

Logfile:        1657 lines
Started:        Tue Mar 27 11:01:14 2007
Finished:       Tue Mar 27 11:01:14 2007

mysqlreport 监视分析mysql状态的工具。


MySQL 5.0.27-standard-l  uptime 0 0:17:57       Tue Mar 27 11:04:15 2007

__ Key _________________________________________________________________
Buffer used         0 of  32.00M  %Used:   0.00
  Current       3.68M            %Usage:  11.51
Write ratio     0.000
Read ratio      0.000

__ Questions ___________________________________________________________
Total             170     0.2/s
Slow                0       0/s  %Total:   0.00  %DMS:   0.00
DMS               105     0.1/s           61.76

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate         109     0.1/s

__ Tables ______________________________________________________________
Open                8 of 2048    %Cache:   0.39
Opened             14     0.0/s

__ Connections _________________________________________________________
Max used            2 of  500      %Max:   0.40
Total              38     0.0/s

__ Created Temp ________________________________________________________
Disk table          0       0/s
Table              16     0.0/s
File                5     0.0/s

mysqlsla 分析mysql的各种日志。分析slow log的结果如下。


Reading slow log 'db-slow.log'
170 total queries, 72 unique
Sorting by 't'

__ 001 _______________________________________________________________________

Count         : 15 (8%)
Time          : 1297.000 total, 86.467 avg, 3.000 min to 833.000 max
                9:20% 10:13% 833:6% 67:6% 57:6% 3:6% 61:6% 58:6% 20:6% 103:6% (87%)
Lock          : 0.000 total, 0.000 avg, 0.000 min to 0.000 max
Rows sent     : 0 avg, 0 min to 0 max
Rows examined : 0 avg, 0 min to 0 max
User          : root[root]@/127.0.0.1 (100%)

SET insert_id=N;
LOAD data infile 'S' INTO table user;
省略若干行...

mysqlsniffer 监听mysql通讯的工具。


mysqlsniffer listening for MySQL on interface eth0 port 3306
server > 192.168.1.170.32958: ID 0 len 65 Handshake <proto 10="" ver="" 5.0.27-standard-log="" thd="" 39="">
192.168.1.170.32958 > server: ID 1 len 38 Handshake (new auth) <user root="" db="" (null)="" max="" pkt="" 16777216="">
server > 192.168.1.170.32958: ID 2 len 7 OK <fields 0="" affected="" rows="" 0="" insert="" id="" 0="" warnings="" 0="">
192.168.1.170.32958 > server: ID 0 len 18 COM_QUERY: SELECT DATABASE()
server > 192.168.1.170.32958: ID 1 len 1 1 Fields
        ID 2 len 32 Field: ..DATABASE() <type var="" string="" (253)="" size="" 102="">
        ID 3 len 5 End <warnings 0="">
        ID 4 len 1 || NULL ||
        ID 5 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 5 COM_INIT_DB: test
server > 192.168.1.170.32958: ID 1 len 7 OK <fields 0="" affected="" rows="" 0="" insert="" id="" 0="" warnings="" 0="">
192.168.1.170.32958 > server: ID 0 len 15 COM_QUERY: show databases
server > 192.168.1.170.32958: ID 1 len 1 1 Fields
        ID 2 len 49 Field: .SCHEMATA.Database <type var="" string="" (509)="" size="" 192="">
        ID 3 len 5 End <warnings 0="">
        ID 4 len 19 || information_schema ||
        ID 5 len 3 || aa ||
        ID 6 len 6 || mysql ||
        ID 7 len 7 || sakila ||
        ID 8 len 5 || test ||
        ID 9 len 6 || world ||
        ID 10 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 12 COM_QUERY: show tables
server > 192.168.1.170.32958: ID 1 len 1 1 Fields
        ID 2 len 57 Field: .TABLE_NAMES.Tables_in_test <type var="" string="" (509)="" size="" 192="">
        ID 3 len 5 End <warnings 0="">
        ID 4 len 6 || click ||
        ID 5 len 8 || s_click ||
        ID 6 len 5 || user ||
        ID 7 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 7 COM_FIELD_LIST: click
server > 192.168.1.170.32958: ID 1 len 42 Field: test.click.id <type long="" int="" (771)="" size="" 11="">
        ID 2 len 51 Field: test.click.user_id <type long="" int="" (2051)="" size="" 11="">
        ID 3 len 62 Field: test.click.promotion_id <type long="" int="" (2307)="" size="" 11="">
        ID 4 len 49 Field: test.click.status <type tiny="" int="" (1)="" size="" 4="">
        ID 5 len 78 Field: test.click.record_time <type timestamp="" (57607)="" size="" 19="">
        ID 6 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 9 COM_FIELD_LIST: s_click
server > 192.168.1.170.32958: ID 1 len 46 Field: test.s_click.id <type long="" int="" (771)="" size="" 11="">
        ID 2 len 66 Field: test.s_click.promotion_id <type long="" int="" (259)="" size="" 11="">
        ID 3 len 53 Field: test.s_click.status <type tiny="" int="" (1)="" size="" 4="">
        ID 4 len 59 Field: test.s_click.click_num <type long="" int="" (3)="" size="" 11="">
        ID 5 len 73 Field: test.s_click.record_date <type date="" (33034)="" size="" 10="">
        ID 6 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 6 COM_FIELD_LIST: user
server > 192.168.1.170.32958: ID 1 len 40 Field: test.user.id <type long="" int="" (771)="" size="" 11="">
        ID 2 len 43 Field: test.user.name <type var="" string="" (2301)="" size="" 120="">
        ID 3 len 45 Field: test.user.email <type var="" string="" (253)="" size="" 765="">
        ID 4 len 49 Field: test.user.address <type var="" string="" (253)="" size="" 765="">
        ID 5 len 41 Field: test.user.age <type long="" int="" (2051)="" size="" 11="">
        ID 6 len 76 Field: test.user.regist_time <type timestamp="" (59655)="" size="" 19="">
        ID 7 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 27 COM_QUERY: select * from user limit 1
server > 192.168.1.170.32958: ID 1 len 1 6 Fields
        ID 2 len 38 Field: test.user.id <type long="" int="" (771)="" size="" 11="">
        ID 3 len 42 Field: test.user.name <type var="" string="" (2301)="" size="" 120="">
        ID 4 len 44 Field: test.user.email <type var="" string="" (253)="" size="" 765="">
        ID 5 len 48 Field: test.user.address <type var="" string="" (253)="" size="" 765="">
        ID 6 len 40 Field: test.user.age <type long="" int="" (2051)="" size="" 11="">
        ID 7 len 56 Field: test.user.regist_time <type timestamp="" (59655)="" size="" 19="">
        ID 8 len 5 End <warnings 0="">
        ID 9 len 34 || 1 | ll | ff | ll | 10 | 2007-03-21 09:58:07 ||
        ID 10 len 5 End <warnings 0="">
192.168.1.170.32958 > server: ID 0 len 1 COM_QUIT
192.168.1.57.60248 > server: ID 0 len 48 COM_QUERY: SELECT COUNT(*) FROM product WHERE index_flag=0
server > 192.168.1.57.60248: ID 1 len 1 1 Fields
        ID 2 len 30 Field: ..COUNT(*) <type longlong="" (33032)="" size="" 21="">
        ID 3 len 1 End
        ID 4 len 2 || 0 ||
        ID 5 len 5 End <warnings 0="">
70 MySQL packets captured (2022 bytes)

下载

epee

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql监视分析工具

mysqlbinlog 这个大家都应该知道的，用来看mysql的binlog的。mysql自带。mysqldumpslow 用于分析mysql的slow log。自带的。结果如下。Count: 1 Time=4.00s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1] select * from user ignore ke...
复制链接

扫一扫