MySQL enables the log-slow-queries Method for slow query, logslowqueries

MySQL enables the log-slow-queries Method for slow query, logslowqueries

A page of a common WEB site often needs to query n SQL statements to get the PAGE result. When the Website access speed is slow and the front-end has done a lot of optimization work, searching for database bottlenecks is also an important part of WEB optimization.
 
MySQL provides a slow query log record function, which can write statements that query SQL statements for more than a few seconds into the slow query log, during routine maintenance, you can quickly and accurately identify the problem through the slow query log record information.

Enable slow Query

Log-slow-queries
Logs are written to long_query_time queries within seconds.

Open the my. cnf configuration file and add the following code:

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

If it is windows, add it to my. ini.

My. ini

Copy codeThe Code is as follows:
Log_slow_queries
Long_query_time = 2

Save and exit. Restart MySQL.

About long_query_time settings
Generally, we set the value of long_query_time to 2, indicating that the query SQL statement records more than two seconds. Generally, 2 seconds is enough. The default value is 10 seconds. However, for many WEB programs, 2 seconds of query is too long. Indeed, in many sites, the execution time of an SQL statement over one second is slow.
Mysql5.1.21 and later provide more fine-grained long_query_time settings. Previous versions can only be measured in seconds.

View logs

Copy codeThe Code is 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;

Row 1: execution time
Row 2: Execution user
Row 3 (important ):

Query_time: the longer the SQL statement is executed, the slower it is.
Lock_time: Wait for the table lock time in the MySQL server stage (not in the storage engine stage)
Rows_sent
Rows_examined

Last

1. Logs cannot indicate all problems. The knowledge representation may be related to the occasional occurrence of lock tables and BUSY systems. Of course, if an SQL statement is often slow to query, it can be determined that it can be optimized again.
2. Do not enable log-queries-not-using-indexes without the index query record function. This function is of little practical use. It is a general record that does not have an index during SQL queries. Although the index affects the query speed, it depends on the data size. After this function is enabled, queries such as select * from tab will also be recorded in the log, and the log file will soon be filled with junk information, this affects the query of slow log records.
3. MySQL comes with the mysqldumpslow tool used to analyze slow query logs, or other tools, which can be used together for better analysis.