MySQL A method for starting slow query logs log slow queries

An ordinary WEB site page often needs to query N SQL statements to get the page results, when the site access speed is slow and the front end has done a lot of optimization work, database bottleneck search is also an important part of WEB optimization.

MySQL provides a slow query logging function, which can write the query SQL statement longer than how many seconds into the slow query log. In daily maintenance, the problem can be quickly and accurately determined by the record information of the slow query log.

Enable slow queries

log-slow-queries slow query log file path
long_query_time queries that exceed how many 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 is windows, add it in my.ini

my.ini

log_slow_queries

long_query_time = 2

Save exit and restart MySQL.

About long_query_time Settings
The long_query_time value is usually set to 2, which means that if the SQL statement is queryd for more than 2 seconds, 2 seconds is usually enough, and the default is 10 seconds. However, for many WEB programs, a query of 2 seconds is still too long. Indeed, at many sites, an SQL statement that takes more than one second to execute is considered slow.
long_query_time The more granular long_query_time Settings have only been available since mysql5.1.21. Previous versions were only available in seconds.

See the log

[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;

Line 1: Execution time
Line 2: Execute the user
Line 3 (Important) :

Query_time The longer SQL is executed, the slower it will be
Lock_time waits for table lock time in MySQL server phase (not in storage engine phase)
Number of rows returned by the Rows_sent query
Number of rows checked by Rows_examined query

The last

1, the log can not explain a cut problem, knowledge representation, may be related to the lock table, the system is busy occasionally, of course, if a certain SQL statement often query slowly that basic can be determined to be optimized again.
log-queries-not-using-indexes does not have index query record function, this function is not really useful. When recording SQL queries, there is no index of all records. Although the index has an impact on the speed of the query, it depends on the data size. With this feature enabled, queries such as select * from tab will also be logged, and soon the log file will be filled with spam, affecting the view of the main query slow log records.
3, MySQL comes with mysqldumpslow tool for slow log analysis, or other tools can also be used, through the tool can be better analysis.