下载地址】可以用来分析mysql日志查询情况
man pt-query-digest
/--type
定为到--type的option那段如下--type
type: Array
The type of input to parse (default slowlog). The permitted types are
binlog
Parse a binary log file.
genlog
Parse a MySQL general log file. General logs lack a lot of "ATTRIBUTES", notably "Query_time". The default "--order-by" for general logs
changes to "Query_time:cnt".
http
Parse HTTP traffic from tcpdump.
pglog
Parse a log file in PostgreSQL format. The parser will automatically recognize logs sent to syslog and transparently parse the syslog
format, too. The recommended configuration for logging in your postgresql.conf is as follows.
The log_destination setting can be set to either syslog or stderr. Syslog has the added benefit of not interleaving log messages from
several sessions concurrently, which the parser cannot handle, so this might be better than stderr. CSV-formatted logs are not supported
at this time.
The log_min_duration_statement setting should be set to 0 to capture all statements with their durations. Alternatively, the parser will
also recognize and handle various combinations of log_duration and log_statement.
You may enable log_connections and log_disconnections, but this is optional.
It is highly recommended to set your log_line_prefix to the following:
log_line_prefix = '%m c=%c,u=%u,D=%d '
This lets the parser find timestamps with milliseconds, session IDs, users, and databases from the log. If these items are missing,
you'll simply get less information to analyze. For compatibility with other log analysis tools such as PQA and pgfouine, various log line
prefix formats are supported. The general format is as follows: a timestamp can be detected and extracted (the syslog timestamp is NOT
parsed), and a name=value list of properties can also. Although the suggested format is as shown above, any name=value list will be cap-
tured and interpreted by using the first letter of the 'name' part, lowercased, to determine the meaning of the item. The lowercased
first letter is interpreted to mean the same thing as PostgreSQL's built-in %-codes for the log_line_prefix format string. For example, u
means user, so unicorn=fred will be interpreted as user=fred; d means database, so D=john will be interpreted as database=john. The
pgfouine-suggested formatting is user=%u and db=%d, so it should Just Work regardless of which format you choose. The main thing is to
add as much information as possible into the log_line_prefix to permit richer analysis.
Currently, only English locale messages are supported, so if your server's locale is set to something else, the log won't be parsed prop-
erly. (Log messages with "duration:" and "statement:" won't be recognized.)
slowlog
Parse a log file in any variation of MySQL slow-log format.
tcpdump
Inspect network packets and decode the MySQL client protocol, extracting queries and responses from it.
pt-query-digest does not actually watch the network (i.e. it does NOT "sniff packets"). Instead, it's just parsing the output of tcpdump.
You are responsible for generating this output; pt-query-digest does not do it for you. Then you send this to pt-query-digest as you
would any log file: as files on the command line or to STDIN.
The parser expects the input to be formatted with the following options: "-x -n -q -tttt". For example, if you want to capture output
from your local machine, you can do something like the following (the port must come last on FreeBSD):
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 \
> mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt
The other tcpdump parameters, such as -s, -c, and -i, are up to you. Just make sure the output looks like this (there is a line break in
the first line to avoid man-page problems):
2009-04-12 09:50:16.804849 IP 127.0.0.1.42167
> 127.0.0.1.3306: tcp 37
0x0000: 4508 0059 6eb2 4000 4006 cde2 7f00 0001
0x0010: ....
Remember tcpdump has a handy -c option to stop after it captures some number of packets! That's very useful for testing your tcpdump com-
mand. Note that tcpdump can't capture traffic on a Unix socket. Read if you're confused about
this.
Devananda Van Der Veen explained on the MySQL Performance Blog how to capture traffic without dropping packets on busy servers. Dropped
packets cause pt-query-digest to miss the response to a request, then see the response to a later request and assign the wrong execution
time to the query. You can change the filter to something like the following to help capture a subset of the queries. (See
for details.)
tcpdump -i any -s 65535 -x -n -q -tttt \
'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2'
All MySQL servers running on port 3306 are automatically detected in the tcpdump output. Therefore, if the tcpdump out contains packets
from multiple servers on port 3306 (for example, 10.0.0.1:3306, 10.0.0.2:3306, etc.), all packets/queries from all these servers will be
analyzed together as if they were one server.
If you're analyzing traffic for a MySQL server that is not running on port 3306, see "--watch-server".
Also note that pt-query-digest may fail to report the database for queries when parsing tcpdump output. The database is discovered only
in the initial connect events for a new client or when is executed. If the tcpdump output contains neither of these, then pt-
query-digest cannot discover the database.
Server-side prepared statements are supported. SSL-encrypted traffic cannot be inspected and decoded.
如上红色标明部分所述：
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
获得抓包结果，-c为抓包数量，可自定义，-i为抓包的网络接口，可自定义，-s为抓取数据包时指定长度，抓取数据包时，默认抓取长度68字节，用-s 0可以抓到完整的数据包
pt-query-digest --type tcpdump mysql.tcp.txt
将上面16进制的抓包信息转换为文本信息，并分析查询负载情况
