MySQL Slow query log analysis

Source: Internet
Author: User
Tags ldap mysql slow query log


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.