Mysql Open Slow Query log Log-slow-queries method _mysql

Source: Internet
Author: User
Tags log log

A common web site often need to query n SQL statements to get the results of the page, when the Web site access speed is slow and the front-end done a lot of optimization work, the database bottleneck of the search is also an important part of the Web optimization.

MySQL provides a slow query logging function, you can query the SQL statement time is greater than the number of seconds to write to the slow query log, daily maintenance can be slow query log information quickly and accurately judge the problem.

Turn on slow query function

Log-slow-queries Slow query log file path
Long_query_time queries for more than a few seconds are written to the log

Open the MY.CNF configuration file and add the following code:

Log-slow-queries =/tmp/mysql-slow.log
Long_query_time = 2

If it's windows, add it to the My.ini.

My.ini

Copy Code code as follows:

Log_slow_queries
Long_query_time = 2

Save exit, restart MySQL.

about Long_query_time Settings
Normally we set the value of Long_query_time to 2, which means that the query SQL statement is logged for more than two seconds, usually 2 seconds is enough, and the default is 10 seconds. However, for many web programs, a 2-second query is still too long. Indeed, in many sites, a SQL statement is slower than 1 seconds of execution time.
Mysql5.1.21 later to provide finer-grained long_query_time settings, the previous version can only be used as a second unit.

View Log

Copy Code code as follows:

[Root@lizhong tmp]# tail-f/tmp/mysql_slow.log
time:120815 23:22:11
User@host:root[root] @ localhost []
query_time:9.869362 lock_time:0.000035 rows_sent:1 rows_examined:6261774
SET timestamp=1294388531;
Select COUNT (*) from blog;

First line: Execution time
Second line: Execute user
Third line (important):

Query_time SQL execution time, the longer the slower
Lock_time waits for table lock time in the MySQL server phase (not in the storage engine phase)
Number of rows returned by the rows_sent query
Number of rows rows_examined query checks

At last

1, the log can not explain all the problems, knowledge representation, may be linked to the table, the system busy incidental, of course, if a SQL statement often query slow that basic can be judged can be again optimized.
2, do not open log-queries-not-using-indexes No index query logging function, this function is not really useful. is when the SQL query is recorded, there is no index of all records. Although the index has an effect on the speed of the query, it depends on the amount of data. Since this feature is turned on, queries such as SELECT * from Tab will also be recorded in the log, and soon the log file will be filled with spam, which can affect the viewing of the main query slow log.
3, MySQL with the Mysqldumpslow tool used to analyze the slow query log, or other tools can also be better through the tool matching 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.