MYSQL slow query log)

Source: Internet
Author: User
Tags mysql slow query log
Mysql has a function to log down and run slow SQL statements. This log is not available by default. To enable this function, modify my. cnf or add some parameters when mysql is started.
If you modify it in my. cnf, add the following lines:
Long_query_time = 10
Log-slow-queries =/usr/local/mysql/log/slow. log

Long_query_time indicates how long the SQL statement will be logged, which is 10 seconds.
Log-slow-queries is set to write the log there. When it is null, the system will assign the host name to the slow query log and append the slow. log

If the log-long-format parameter is set, all queries without indexes will also be recorded. Add the following line to the file my. cnf or my. ini to record these queries.

This is a useful log. It has little impact on performance (assuming that all queries are fast), and emphasizes the most important queries (if indexes are lost or the indexes are not used properly)

# Time: 070927 8:08:52

# User @ Host: root [root] @ [192.168.0.20]

# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630
Select id, name from manager where id in (66,10135 );
This is one of the slow query logs. It takes 372 seconds, locks 136 seconds, returns 152 rows, and queries 263630 rows in total.

If there is a lot of log Content, it will be exhausting to look at it with one eye. mysql comes with an analysis tool, which is used as follows:
Enter the mysql/bin directory under the command line and enter mysqldumpslow-help or -- help to see the parameters of this tool.
Usage: mysqldumpslow [OPTS...] [LOGS...]

Parse and summarize the MySQL slow query log. Options are

-- Verbose

-- Debug

-- Help write this text to standard output

-V verbose

-D debug

-S ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default

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

-T NUM just show the top n queries

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

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

-G PATTERN grep: only consider into ts that include 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 scr limit pt)

-L don't subtract lock time from total time

-S is the order, which indicates that the write is not detailed enough. I used it, including reading the code, mainly including
C, t, l, r, and ac, at, al, ar are sorted by the number of queries, time, lock time, and number of returned records, respectively, flashback with a added above
-T indicates the top n, that is, the number of data records returned.
-G. You can write a regular expression matching later. It is case insensitive.

Mysqldumpslow-s c-t 20 host-slow.log
Mysqldumpslow-s r-t 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.
Mysqldumpslow-t 10-s t-g left join host-slow.log
Return the first 10 SQL statements containing the left join according to the time

Note: Write my. cnf

 

[Mysqld.

 

Original address: http://blog.chinaunix.net/space.php? Uid = 618253 & do = blog & cuid = 1093402

 

 

 

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.