MySQL Slow query

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