MySQL in slow query log slow log query analysis

Source: Internet
Author: User
Tags mysql slow query log

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.