mysql监视分析工具
mysqlbinlog 这个大家都应该知道的，用来看mysql的binlog的。mysql自带。mysqldumpslow 用于分析mysql的slow log。自带的。结果如下。
Count: 1Time=4.00s (4s)Lock=0.00s (0s)Rows=0.0 (0), root@
select * from user ignore key (PRIMARY)where address = 'S' order by id asc limit N
Count: 3Time=4.00s (12s)Lock=0.00s (0s)Rows=0.0 (0), root@
select * from userwhere address = 'S' order by id asc limit N
Count: 2Time=4.00s (8s)Lock=0.00s (0s)Rows=32.0 (64), root@
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: ---
countkey:
1 PRIMARY
countpossible_keys:
1 PRIMARY
counttype:
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 code13:Permission denied
mysqlslap 一个性能测试的工具。mysql的test suite中带的。
mtop 一个进程监视的工具，可以直接在里面显示出explain的结果。也可以使用watch -n 1 mysqladmin status processlist来监视。
127.0.0.1mysqld 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: 0RRN: 467TLW: 0SFJ: 0SMP: 0QPS: 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: ---
counttype:
10 ALL
Table test.click: ---
counttype:
1 ALL
Table test.user: ---
countkey:
1 username,useraa
9 usertime
16 PRIMARY
29 useraa
countpossible_keys:
1 PRIMARY
7 useraa,username
9 usertime
23 useraa
counttype:
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-luptime 0 0:17:57 Tue Mar 27 11:04:15 2007
__ Key _________________________________________________________________
Buffer used 0 of32.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 of500 %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@/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)
下载
页:
[1]