Mysql管理必备工具Maatkit详解之十二(mk-query-digest)
Mysql管理必备工具Maatkit详解之十二(mk-query-digest)
2009年09月17日 作者： 大头刚
mk-query-digest 一个功能很强大的工具，能分析慢查询日志，也可以对当前的语句进行分析等。安装方法查看
这里
。
一、分析慢查询日志,生成报表
mysql通过log-slow-queries和long_query_time参数来记录慢查询，默认的格式如下：
# Time: 0909094:34:28
# User@Host: sparty @
# Query_time: 18Lock_time: 0Rows_sent: 5Rows_examined: 52830
select * from test;
# Time: 090909 14:04:24
# User@Host: sparty @
# Query_time: 20Lock_time: 0Rows_sent: 10Rows_examined: 2500284
select * from test2;
每产生一条慢查询记录一行，分析起来不直观，也无法进行统计。mk-query-digest能对slow log进行了统计和分析产生报告，可以更具自己的需要，分析出想要的结果。例如
mk-query-digest --limit 2 --select Query_time,Lock_time,Rows_sent,Rows_examined,ts slow.log \
|grep -v '1us' |grep -v '10us'|grep -v '1ms'|grep -v '10ms'|grep -v '100ms' \
|grep -v '1s'|grep -v '10s'|grep -v 'SHOW'|grep -v '100us'|grep -v 'EXPLAIN'
# Overall: 3 total, 2 unique, 0.00 QPS, 0.00x concurrency ________________
# total min max avg 95%stddevmedian
# Exec time 78s 8s 44s 26s 42s 14s 25s
# Lock time 2ms 0 2ms 588us 2ms 822us 0
# Rows sent 23.87M 111.93M 7.96M11.87M 5.59M11.87M
# Rows exam 35.80M11.93M11.93M11.93M11.87M 011.87M
# Time range 2009-05-20 20:33:59 to 2009-05-27 14:50:13
# Query 1: 0.08 QPS, 2.68x concurrency, ID 0x67A347A2812914DF at byte 1240
# This item is included in the report because it matches --limit.
# pct total min max avg 95%stddevmedian
# Count 66 2
# Exec time 89 70s 26s 44s 35s 44s 13s 35s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 9923.87M11.93M11.93M11.93M11.93M 0.7111.93M
# Rows exam 6623.87M11.93M11.93M11.93M11.93M 0.7111.93M
# Time range 2009-05-27 14:49:47 to 2009-05-27 14:50:13
# Query_time distribution
# Tables
SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`\G
# Query 2: 0 QPS, 0x concurrency, ID 0x92203A59492D2ECF at byte 0 ________
# This item is included in the report because it matches --limit.
# pct total min max avg 95%stddevmedian
# Count 33 1
# Exec time 10 8s 8s 8s 8s 8s 0 8s
# Lock time 100 2ms 2ms 2ms 2ms 2ms 0 2ms
# Rows sent 0 1 1 1 1 1 0 1
# Rows exam 3311.93M11.93M11.93M11.93M11.93M 011.93M
# Time range 2009-05-20 20:33:59 to 2009-05-20 20:33:59
# Query_time distribution
# Tables
select count(*) from test where t1=20 and t2=100\G
# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ======= ========== ====
# 1 0x67A347A2812914DF 69.7409 89.4% 234.870474 SELECT test
# 2 0x92203A59492D2ECF 8.2742 10.6% 1 8.274248 SELECT test
报表的输出分三部分，开头部分是整个报表的综合统计。中间部分把单独的Query进行统计，按照total Exec time从高到低排序。结尾部分是输出的每条Query的一些统计。
解释一下主要参数：
–limit 指输出耗时最多的Query的条数限制。
–select 选择需要统计的指标。
二、对当前的查询进行统计分析
mk-query-digest也能使用–processlist参数，对指定mysql的当前Query进行统计分析，例如
mk-query-digest –processlist h=localhost -u sg -p
然后在CTRL+C结束，将对这段时间h=localhost里所进行的所有Query进行统计分析并生成报表，报表的输出和上面介绍的一样。
三、分析慢查询日志，将结果导入到指定表。
mk-query-digest也能使用–review参数，将慢查询日志的分析结果插入到指定表中。
首先需要创建表，存放分析结果：
CREATE TABLE query_review (
checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
fingerprintTEXT NOT NULL,
sample TEXT NOT NULL,
first_seen DATETIME,
last_seen DATETIME,
reviewed_byVARCHAR(20),
reviewed_onDATETIME,
comments TEXT
)
COLUMN MEANING
==========================
checksum A 64-bit checksum of the query fingerprint
fingerprintThe abstracted version of the query; its primary key
sample The query text of a sample of the class of queries
first_seen The smallest timestamp of this class of queries
last_seen The largest timestamp of this class of queries
reviewed_byInitially NULL; if set, query is skipped thereafter
reviewed_onInitially NULL; not assigned any special meaning
comments Initially NULL; not assigned any special meaning
例如：
mk-query-digest --review h=host1,D=test,t=query_review /path/to/slow.log
mysql> use test;
Database changed
mysql> select * from query_review\G;
*************************** 1. row ***************************
checksum: 7467891370387641567
fingerprint: mysqldump
sample: SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`
first_seen: 2009-05-27 14:49:47
last_seen: 2009-05-27 14:50:13
reviewed_by: NULL
reviewed_on: NULL
comments: NULL
*************************** 2. row ***************************
checksum: 10529480083946417871
fingerprint: select count(*) from test where test1=? and test2=?
sample: select count(*) from test where test1=20 and test2=100
first_seen: 2009-05-20 20:33:59
last_seen: 2009-05-20 20:33:59
reviewed_by: NULL
reviewed_on: NULL
comments: NULL
2 rows in set (0.00 sec)
四、查找未走索引的语句
mk-query-digest还可以用来找出当前数据库未走索引的语句，例如我使用下面命令：
tcpdump -i bond0 port 3306 -s 65535 -x -n -q -tttt | mk-query-digest –type tcpdump –filter ‘($event->{No_index_used} || $event->{No_good_index_used})’
然后在CTRL+C结束，将生成下面的报告。
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
3 packets captured
3 packets received by filter
0 packets dropped by kernel
# Caught SIGINT.
# 170ms user time, 20ms system time, 12.00M rss, 73.26M vsz
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# total min max avg 95%stddevmedian
# Exec time 223us 223us 223us 223us 223us 0 223us
# Time range 2009-09-16 09:22:50.637146 to 2009-09-16 09:22:50.637146
# bytes 26 26 26 26 26 0 26
# Rows affe 0 0 0 0 0 0 0
# Warning c 0 0 0 0 0 0 0
# 100% (1) No_index_used
# Query 1: 0 QPS, 0x concurrency, ID 0x3D8C074C26D05CD7 at byte 0 ________
# This item is included in the report because it matches --limit.
# pct total min max avg 95%stddevmedian
# Count 100 1
# Exec time 100 223us 223us 223us 223us 223us 0 223us
# Hosts 1 10.0.0.203
# Time range 2009-09-16 09:22:50.637146 to 2009-09-16 09:22:50.637146
# bytes 100 26 26 26 26 26 0 26
# Errors 1 none
# Rows affe 0 0 0 0 0 0 0 0
# Warning c 0 0 0 0 0 0 0 0
# 100% (1) No_index_used
select * from test\G
# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ======= ========== ====
# 1 0x3D8C074C26D05CD7 0.0002 100.0% 1 0.000223 SELECT test
很明显，使用tcpdump只能对本地的mysql进行监听，一目了然的找到了哪条sql是没走索引的。
最后，我们在看下这个工具的帮助选项：
mk-query-digest --help
mk-query-digest parses and analyzes MySQL log files.With no FILE, or when FILE
is -, read standard input.For more details, please use the --help option, or
try 'perldoc /usr/bin/mk-query-digest' for complete documentation.
Usage: /usr/bin/mk-query-digest
Options:
--ask-pass Prompt for a password when connecting to MySQL
--attribute-aliases List of attribute|alias,etc (default db|Schema)
--attribute-value-limit A sanity limit for attribute values (default
4294967296)
--aux-dsn Auxiliary DSN used for special options
--charset -ADefault character set
--check-attributes-limit Stop checking for new attributes after this
many events (default 1000)
--config Read this comma-separated list of config files;
if specified, this must be the first option on
the command line
--continue-on-error Continue parsing even if there is an error
(default yes)
--create-review-history-tableCreate the --review-history table if it does
not exist
--create-review-table Create the --review table if it does not exist
--daemonize Fork to the background and detach from the
shell
--defaults-file -FOnly read mysql options from the given file
--embedded-attributes Two Perl regex patterns to capture
pseudo-attributes embedded in queries
--execute Execute queries on this DSN
--expected-range Explain items when there are more or fewer than
expected (default 5,10)
--explain Run EXPLAIN for the sample query with this DSN
and print results
--filter Discard events for which this Perl code doesn't
return true
--fingerprints Add query fingerprints to the standard query
analysis report
--for-explain Print extra information to make analysis easy
(default yes)
--group-by Which attribute of the events to group by
(default fingerprint)
--help Show help and exit
--host -hConnect to host
--ignore-attributes Do not aggregate these attributes when
auto-detecting --select (default
s,key,res,val,server_id,offset,end_log_pos,Xid)
--inherit-attributes If missing, inherit these attributes from the
last event that had them (default db,ts)
--interval How frequently to poll the processlist, in
seconds (default .1)
--iterations How many times to iterate through the
collect-and-report cycle (default 1)
--limit Limit output to the given percentage or count
(default 95%:20)
--log Print all output to this file when daemonized
--mirror How often to check whether connections should
be moved, depending on read_only
--order-by Sort events by this attribute and aggregate
function (default Query_time:sum)
--outliers Report outliers by attribute:percentile:count
(default Query_time:1:10)
--password -pPassword to use when connecting
--pid Create the given PID file when daemonized
--port -PPort number to use for connection
--print Print log events to STDOUT in standard
slow-query-log format
--processlist Poll this DSN's processlist for queries, with
--interval sleep between
--report Print out reports on the aggregate results from
--group-by (default yes)
--report-all Include all queries, even if they have already
been reviewed
--report-format Print these elements in the query analysis
report (default
rusage,header,query_report,profile)
--review Store a sample of each class of query in this
DSN
--review-history The table in which to store historical values
for review trend analysis
--run-time How long to run before exiting.Optional
suffix s=seconds, m=minutes, h=hours, d=days;
if no suffix, s is used.
--sample Filter out all but the first N occurrences of
each query
--select Compute aggregate statistics for these
attributes
--set-vars Set these MySQL variables (default
wait_timeout=10000)
--shorten Shorten long statements in reports (default
1024)
--since Parse only queries newer than this value (parse
queries since this date)
--socket -SSocket file to use for connection
--tcpdump-errors Write the tcpdump data to this file on error
--timeline Show a timeline of events
--type The type of input to parse (default slowlog)
--until Parse only queries older than this value (parse
queries until this date)
--user -uUser for login if not current user
--version Show version and exit
--watch-server This option tells mk-query-digest which server
IP address and port (like "10.0.0.1:3306") to
watch when parsing tcpdump (for --type tcpdump
and memcached); all other servers are ignored
--zero-admin Zero out the Rows_XXX properties for
administrator command events (default yes)
--zero-bool Print 0% boolean values in report
Rules:
DSN values in --review-history default to values in --review if COPY is yes.
DSN syntax is key=value[,key=value...]Allowable DSN keys:
KEYCOPYMEANING
====================================================
A yes Default character set
D yes Database that contains the query review table
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no Table to use as the query review table
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--ask-pass FALSE
--attribute-aliases db|Schema
--attribute-value-limit 4294967296
--aux-dsn (No value)
--charset (No value)
--check-attributes-limit 1000
--config /etc/maatkit/maatkit.conf,/etc/maatkit/mk-query-digest.conf,/root/.maatkit.conf,/root/.mk-
query-digest.conf
--continue-on-error TRUE
--create-review-history-tableFALSE
--create-review-table FALSE
--daemonize FALSE
--defaults-file (No value)
--embedded-attributes (No value)
--execute (No value)
--expected-range 5,10
--explain (No value)
--filter (No value)
--fingerprints FALSE
--for-explain TRUE
--group-by fingerprint
--help TRUE
--host (No value)
--ignore-attributes
arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid
--inherit-attributes db,ts
--interval .1
--iterations 1
--limit 95%:20
--log (No value)
--mirror (No value)
--order-by Query_time:sum
--outliers Query_time:1:10
--password (No value)
--pid (No value)
--port (No value)
--print FALSE
--processlist (No value)
--report TRUE
--report-all FALSE
--report-format header,profile,query_report,rusage
--review (No value)
--review-history (No value)
--run-time (No value)
--sample (No value)
--select
--set-vars wait_timeout=10000
--shorten 1024
--since (No value)
--socket (No value)
--tcpdump-errors (No value)
--timeline FALSE
--type slowlog
--until (No value)
--user (No value)
--version FALSE
--watch-server (No value)
--zero-admin TRUE
--zero-bool FALSE
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2184987.html
页:
[1]