#!/usr/bin/shell
# clear 
rm -f *.dig

# normal
pt-query-digest slow.log > nor.dig

# limit, default: 95%:20
# top n worst query
pt-query-digest --limit=4 slow.log > limit_n.dig
# top pct worst query
pt-query-digest --limit=30% slow.log > limit_pct.dig
# top pct & top n
pt-query-digest --limit=90%:2 slow.log > limit_mul.dig


# outliers, --outlines=Query_time:m:n
# option of each outlines for group-by
# queries whose 95th percentile Query_time is at least 10 seconds and which are seen at least 5 times
pt-query-digest --outlines=Query_time:10:5 slow.log > outliners.dig


# group by,{user|db|user,db|add so on}
# user,db : group by user , group by db.
pt-query-digest --group-by user,db slow.log 


# order by sum/min/max/avg
pt-query-digest --order-by=Query_time:sum slow.log > order.dig




# DSN string can't contain space
# TABLE: review & history default is created, params: --create-review-table --create-history-table
# review DSN string can't contain space
pt-query-digest --review h=10.0.7.155,u=upt,p=lm,D=test,t=pt_query_review --create-review-table slow.log > review.dig


# review & history
pt-query-digest --review h=10.0.7.155,u=upt,p=lm,D=test,t=pt_query_review --history h=10.0.7.155,u=upt,p=lm,D=test,t=pt_query_review_history slow.log > review_history.dig


# iteration & run time
pt-query-digest --iterations=3 slow.log > itera.dig


# filter
过滤 选项

Query is 1 MB or larger
   –filter ‘$event->{bytes} >= 1048576’

设置某个选项的值
With creative use of--filter, you can create new attributes derived from existing attributes. For example, to create an attribute calledRow_ratiofor examining the ratio ofRows_senttoRows_examined, specify a filter like: $event->{hostname}=\"$hostKey\"

# 

Report the slowest queries from the processlist on host1:

Capture MySQL protocol data with tcppdump, then report the slowest queries:

Save query data fromslow.logto host2 for later review and trend analysis:

For example, to report queries whose 95th percentile Query_time is at least 60 seconds and which are seen at least 5 times, use the following argument:

Order by default Query_time:sum

--order-by Query_time:cnt

            
--type  支持的日志类型

binlog
    Parse a binary log file that has first been converted to text using mysqlbinlog.
    For example:
    mysqlbinlog mysql-bin.000441 > mysql-bin.000441.txt
    pt-query-digest --type binlog mysql-bin.000441.txt
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.
slowlog
    Parse a log file in any variation of MySQL slow log format.
tcpdump
    # 不能捕获socket内容
    Remember tcpdump has a handy -c option to stop after it captures some number of packets! That’s very useful for testing your tcpdump command.
    The parser expects the input to be formatted with the following options: -x -n -q -tttt  -s, -c, and -i

-c  捕获n个packets后停止

port 默认3306

    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:  ....

    # 不丢包的方式捕获
    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 http://www.mysqlperformanceblog.com/?p=6092 for details.)
    tcpdump -i any -s 65535 -x -n -q -tttt 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2'