MySQL in slow query log slow log query analysis

Slow query log in MySQL is a very important feature, we can turn on the MySQL slow query log function, so that we can analyze the state and performance of each SQL execution to optimize.

One, slow query log configuration

To turn on the slow query log, configure the sample:

/etc/mysql/my.cnf
[Mysqld]
Log-slow-queries

Add the above configuration item in the MY.CNF configuration file and restart the MySQL service, when the MySQL slow query function takes effect. Slow query log will write to parameter DataDir (data directory:/var/lib/mysql)
Under the specified path, the default file name is Host_name-slow.log.
As with the error log and query log, the format of slow query logging is also plain text and can be read directly. The following example shows the settings and the read process for the slow query log.

1) First, check the value of Long_query_time.

Mysql> Show variables like ' long% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Long_query_time | 10 |
+-----------------+-------+
1 row in Set (0.00 sec)

(2) For the convenience of testing, the modified slow query time is 5 seconds.

Mysql> set long_query_time=1;
Query OK, 0 rows affected (0.02 sec)

The above operation is limited to the current valid, the shutdown server has to be reset. The following method can be written directly to the MY.CNF

Log-slow-queries=/var/lib/mysql/slowquery.log

Long_query_time=1

Second, the common parameters of Mysqldumpslow are as follows:

-S followed by the following parameter indicates the order in which the Mysqldumpslow results are displayed!
Number of times C query was executed
T-SQL Execution time
L LOCK the time of the watch
Number of rows returned by R SQL
Ac,at,al,ar, indicating reverse order
-T, which is top N, is the return of the previous N data.
-G, behind can write a regular match pattern, case insensitive
Shows the longest execution time of the first two
[Email protected] python]# mysqldumpslow-s t-t 2/opt/mysql/data/slowquery.log
Reading mysql slow query log from/opt/mysql/data/slowquery.log
Count:2 time=412.54s (825s) lock=0.00s (0s) rows=1.0 (2), Root[root] @localhost
Select COUNT (N) from sbtest, T1 where t1.c=sbtest.c
Count:1 time=778.20s (778s) lock=0.00s (0s) rows=1.0 (1), Root[root] @localhost
Select COUNT (N) from Sbtest where sbtest.id not in (select ID from t1)
#显示次数最多的前两个
[Email protected] python]# mysqldumpslow-s c-t 2/opt/mysql/data/slowquery.log
Reading mysql slow query log from/opt/mysql/data/slowquery.log
Count:12 time=0.00s (0s) lock=0.00s (0s) rows=1.0 (), Root[root] @localhost
Select COUNT (N) from tab_1
Count:2 time=412.54s (825s) lock=0.00s (0s) rows=1.0 (2), Root[root] @localhost
Select COUNT (N) from sbtest, T1 where t1.c=sbtest.c

MySQL in slow query log slow log query analysis