MySQL slow query log slow log queries analysis

One, slow query log configuration

Turn on the slow query log to configure the sample:

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

Add the above configuration entry in the MY.CNF configuration file and restart the MySQL service, when the MySQL slow query function takes effect. Slow query log writes to parameter DataDir (data directory:/var/lib/mysql)
The default filename is host_name-slow.log under the specified path.
As with error logs, query logs, the format of slow query logging is also plain text and can be read directly. The following example shows the setup and read procedures for slow query logs.

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) In order to facilitate testing, will modify the slow query time of 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 must 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

The common parameters of Mysqldumpslow are as follows:

-S followed by the following parameters to indicate the order in which Mysqldumpslow results are displayed!
Number of times C query executes
T-SQL Execution time
L LOCK the time of the table
R SQL returns the number of rows
Ac,at,al,ar, indicating descending order
-T is top N, which returns the preceding N data.
-G, a regular matching pattern can be written behind, and case insensitive
Displays the first two of the longest execution times
[Root@rac3 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 isn't in (select ID from t1)
#显示次数最多的前两个
[Root@rac3 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 (a), 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
[Root@rac3 python]#