MySQL Slow query log analysis

MySQL Slow query:

Slow query-related variables

Slow_query_log: This parameter controls the state of the slow query, 1 indicates the on state, and 0 indicates the off state

Slow_query_log_file: Slow Query log path

Long_query_time: Maximum query threshold, the query time exceeds this value is considered slow query and recorded to the slow query log, slow query log path

By slow_query_log_file this variable setting

Log_queries_not_using_indexes: Query statements that do not use the index are recorded in the slow query log.

Log_slow_slave_statements:

Log_slow_admin_statements:

State variables related to slow queries:

Slow_queries: Number of queries in a slow query log file

Slow query log format:

# time:140919 4:34:28 SQL statement Execution time

# [email protected]: Root[root] @ [192.168.57.108] id:19260444 user name and server IP execution thread Id

# query_time:9.556629 lock_time:0.000035 rows_sent:2606718 rows_examined:2606718

Number of records queried for time waiting for lock index scan or table scan count

SET timestamp=1411072468; # Time to write slow query log

SELECT/*!40001 Sql_no_cache */* from ' cm_players '; # SQL statements

Slow Query Log Analysis:

The system comes with Log analysis tool: Mysqldumpslow

[Email protected] mysql]# Mysqldumpslow--help

Usage:mysqldumpslow [OPTS ...] [LOGS ...]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose

--debug Debug

--help write this text to standard output

-V Verbose

-D Debug

-S ORDER what to sort by (Al, at, AR, C, L, R, T), ' on ' is default

Al:average Lock Time

Ar:average rows Sent

At:average Query Time

C:count

L:lock time

R:rows sent

T:query time

-R Reverse the sort order (largest last instead of first)

-T NUM just show the top n queries

-A don ' t abstract all numbers-N and strings to ' S '

-N NUM abstract numbers with at least n digits within names

-G PATTERN Grep:only Consider stmts that include the This string

-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),

Default is ' * ', i.e. match all

-I name name of server instance (if using Mysql.server startup script)

-L don ' t subtract lock time from total time

Common parameters are-s,-T,-G

-S: Indicates sort. Can be based on the number of SQL executions C, total time spent T, total lock wait time L, Total return rows R,

The default is descending (C,t,l,r), preceded by a single A to indicate ascending sort (ac,at,al,ar)

-T: Show the first few lines

-G: Similar to fuzzy query

Finds the SQL containing the left join and sorts the first 2 SQL statements by the total number of executions

[Email protected] mysql]# mysqldumpslow-s c-t 2-g left join Ldap-slow.log

Reading MySQL slow query log from join Ldap-slow.log

Can ' t open join:no such file or directory At/usr/bin/mysqldumpslow line 91.

count:11294 time=4.51s (50925s) lock=0.15s (1659s) rows=11.1 (125610), eventdev[eventdev]@2hosts

Select A.*,aes_decrypt (B.playermobile, "S") as Playermobile,b.submitby,b.gameaccount,aes_decrypt (B.playerRealName, "S") as Playerrealname,b.isvip,b.gameid,b.gameareaid,b.gameserverid from Cm_sms_send_log A left join Cm_events B using ( EVENTID) where a.issend=n and A.smstype!=n order by A.sendid limit N

count:858 time=14.66s (12579s) lock=0.00s (0s) rows=26.9 (23072), eventdev[eventdev]@[192.168.119.45]

Select A.*,b.catename,c.tagname,

(SELECT COUNT (*) from cm_event_reasons AA where aa.isdelete=n and aa.ccid like Concat (a.ccid, ' S ')) as subcnt

From Cm_event_reasons a LEFT join Cm_event_cates B on A.cateid=b.cateid

Left join Cm_event_tags C on A.tagid=c.tagid

where A.isdelete=n and N order by Reasonid limit N,n

Reference Links:

Http://www.ccvita.com/410.html

Http://blog.sina.com.cn/s/blog_53b13d950100vmc5.html

A variety of slow query analysis tools:

http://blog.csdn.net/jkh753/article/details/11590273

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1555269

MySQL Slow query log analysis