MySQL Log analysis tool

MySQL's performance starts with the view log. Low hardware configuration often leads to such problems, but in fact most of them are not here. Some "slow" SQL blocks the execution of other statements, and optimizing queries is the first step to be made.

"工欲善其事 its prerequisite", MySQL itself, a mysqldumpslow query Log analyzer, the tool is not only obsolete, the validation specification is not accurate. Today is the Percona tool pt-query-digest, which can analyze slow query log content, generate query reports, filter, replay or transmit some query statements to mysql,postgresql,memcached or others.

Basic syntax: Pt-query-digest [OPTION ...] [FILE]

Pt-query-digest[option ...] [FILE]

Disadvantage: Expensive system resources (you can copy the slow query log to other locations for analysis)

Example 1 (in the test library),

     Pt-query-digest/usr/local/mysql3307/data/slow_my3307.log
# 120.6s User Time, 1.4s system time, 59.63M RSS, 103.21M v SZ
# current DATE:FRI Aug  3 12:21:26
# hostname:xxxx
# Files:/usr/local/mysql3307/data/slow_ My3307.log
# overall:515.52k Total, the unique, 0.12 QPS, 0.00x concurrency ________
# time range:2012-06-14 06: 41:25 to 2012-08-03 12:21:26
* Attribute          total     min     max     avg     .  StdDev  Median
# ============     ======= ======= ======= ======= ======= ======= =======

# Exec Time          4742s    64us     16s     9ms    40ms    35ms   287us
# Lock time            20s    13us    98ms    38us    49us   370us    23us
# Rows sent          5.22M       0   1.10k   10.62   51.63   54.93    0.99
# Rows examine       8.29G       0 101.66k  16.86k  97.04k  33.18k  964.41
# Query size        32.28M     930   65.66  107.34   35.79   34.95

Part of the explanation is as follows:

The first line indicates the time used to parse the log. There is a total of 515.52k slow queries in the file (the test is a little bit more. , where there are 240 completely different types of queries, the number of queries processed per second during that time period: 0.12 (discussed later on the difference between different queries)

Next is:

The analysis section of the more critical sql:

# profile
# Rank Query ID           Response time   Calls  r/call apdx v/m   Item
# = = ================== = = ============ = = = = = ===== =======
#    1 0xf32359e9a4679928 2680.8630 56.5% 116551 0.0230 1.00  S Elect User_bloods
#    2 0xb05f93ceb2ded5f5 1908.3559 40.2% 62714 0.0304 1.00 0.00  SELECT user_bloods
#    4 0x85e98d19b3a42237   28.8959  0.6%     2.4080 0.83 11.49 SELECT appfuse.titems
# MISC 0xMISC              123.5087  2.6% 336240 0.0004   NS   0.0 <237 items>

One of the most serious 4 SQL statements, (can be set through the parameter--limit) It all statements response time sum, call scale, query type, etc.

The following is an analysis of a single statement:

String:
# Databases    YYY #
Hosts
# Users        XXX
# query_time Distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
100ms #    1s  ########################
#  10s+  ########

You can see in the database yyy users xx Use this statement query response time map, 10s+ is still a lot of.

Finally, the analysis of the situation:

# Tables
# show    table STATUS from ' YYY ' like ' Titems ' \g
# show    CREATE TABLE ' ZZZ '. ' Titems ' \g
# E Xplain/*!50100 partitions*/
select * from ' ZZZ '. ' Titems ' limit  0,1000\g

# is part of the analysis step, the final statement can be preceded by the explain for replication, further analysis.

This column more highlights: http://www.bianceng.cn/database/MySQL/