MySQL Slow query
First, check the system all parameter status:
Mysql> Show variables
Second, see the definition of slow query:
Mysql> Show variables like '%quer% ';
| log_slow_queries | On |
| Long_query_time | 5.000000 |
Mysql> Show variables like ' slow% ';
+---------------------+-------------------------------+
| variable_name | Value |
+---------------------+-------------------------------+
| Slow_launch_time | 5 |
| Slow_query_log | On |
| Slow_query_log_file | /var/log/mysql/slow_query_log |
+---------------------+-------------------------------+
3 Rows in Set (0.00 sec)
Third, modify the slow query definition:
1. Modify it now:
Turn on slow query function, time definition
Mysql> SET Log_slow_queries=on;
Mysql> SET long_query_time=5;
Turn on slow query recording function, time definition, file save location
Mysql> SET Slow_query_log=on;
Mysql> SET slow_launch_time=5;
Mysql> SET Slow_query_log_file=/var/log/mysql/slow_query_log;
2. Modify the configuration file (restart service required)
Vim/etc/my.cnf
Turn on slow query function, time definition
Log_slow_queries=on|off
Long_query_time=5
Turn on slow query recording function, time definition, file save location
Slow_query_log=on|off
Slow_launch_time=5
Slow_query_log_file=/var/log/mysql/slow_query_log
Restart Service
Service MySQL Restart
Iv. Other Settings
1. Log all query statements that are not used to the index
Log_queries_not_using_indexes=on|off
2. Record slow queries caused by finding extra 1000 times
min_examined_row_limit=1000
3. Record those slow optimize table,analyze table and ALTER TABLE statements
Log-slow-admin-statements
4. Record slow queries generated by slave
Log-slow-slave-statements
V. Log Analysis Tool Mysqldumpslow
-S, which means to sort by:
C: Access Count
L: Lockout time
R: Return record
T: Query time
Al: Average lockout time
AR: Average number of returned records
At: Average query time
-T, which is the meaning of top N, which is to return the data of the previous number of bars;
-G, you can write a regular matching pattern, the case is not sensitive;
Cases:
1. Get the maximum number of 10 SQL returned to the recordset.
Mysqldumpslow-s r-t 10/var/log/mysql/slow_query_log/mysql06_slow.log
2. Get the most visited 10 sql
Mysqldumpslow-s c-t 10/var/log/mysql/slow_query_log/mysql06_slow.log
3. Get the query that contains the left link in the first 10 lines sorted by time.
Mysqldumpslow-s t-t 10-g "left join"/var/log/mysql/slow_query_log/mysql06_slow.log
4. It is also recommended to combine these commands with | And more use, otherwise there may be a situation where the brush screen appears.
Mysqldumpslow-s r-t 20/var/log/mysql/slow_query_log/mysql06-slow.log | More
MySQL Slow query