Enable MySQL slow query in Windows
In Windows, the configuration file of MySQL is usually my. ini. Find [mysqld] and add
Log-Slow-queries = F: \ mysql \ log \ mysqlslowquery. Log
Long_query_time = 2
Enable MySQL slow query in Linux
The configuration file of MySQL in Windows is usually my. CNF. Find [mysqld] and add
Log-Slow-queries =/data/mysqldata/slowquery. Log
Long_query_time = 2
Note:
Log-Slow-queries = F: \ mysql \ log \ mysqlslowquery. log is the location where slow query logs are stored. Generally, this directory requires the write permission of the MySQL running account. Generally, this directory is set to the MySQL data storage directory;
2 In long_query_time = 2 indicates that the query takes more than two seconds to record;
Add the log-queries-not-using-indexes parameter to my. CNF or my. ini, indicating that no index is used in the query.
For example:
Log-Slow-queries =/data/mysqldata/slowquery. Log
Long_query_time = 2
Log-queries-not-using-Indexes
1. mysqldumpslow
Mysqldumpslow is a Perl script officially provided, so you don't have to bother installing Bala. As long as you have a MySQL environment, you can basically use it:
Mysqldumpslow-S C
The parameter (-s c) means to sort by the number of slow queries. However, mysqldumpslow has a bad disadvantage, that is, the query results only have some abstract SQL statements, and there is no actual running SQL example.
Mysqldumpslow is the log analysis tool for slow query officially provided by MySQL. The output chart is as follows:
The main function is to count slow SQL statements
Number of occurrences (count ),
Maximum execution time (time ),
Total time consumed ),
Lock ),
Total number of rows sent to the client (rows ),
Total number of rows scanned (rows ),
The user and the SQL statement itself (Abstract The format, for example, limit 1, 20 is represented by limit N, N ).
Ii. mysqlsla
Mysqlsla is a log analysis tool launched by hackmysql.com (This website also maintains MySQL tools such as mysqlreport and mysq1_xchk)
Data reports are very helpful for analyzing the causes of slow queries, including execution frequency, data volume, and query consumption.
The installation method is as follows:
Tar zxf http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
CD mysqlsla-2.03
Perl makefile. pl
Make
Make install
Easy to use:
/Path/to/mysqlsla slow. Log
The format is described as follows:
Total number of queries (queries total), number of SQL statements after deduplication (unique)
Sorted)
The most important slow SQL statement statistics, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.
Count, the number of SQL executions and percentage of the total slow log count.
Time, execution time, including the total time, average time, minimum, maximum time, and time as a percentage of the total slow SQL time.
95% of time removes the fastest and slowest SQL statements, and the SQL Execution time with a coverage rate of 95%.
Lock time, the waiting time.
95% of lock, 95% of slow SQL wait lock time.
Rows sent, the number of result rows, including average, minimum, and maximum.
Rows examined: number of rows scanned.
Database, which database belongs
Users, which user and IP address account for the percentage of SQL statements executed by all users
Query Abstract: Abstract SQL statement
Query sample, SQL statement
In addition to the above output, the official website also provides many customized parameters, which are a rare tool.
From http://blog.csdn.net/a600423444/article/details/6854289