MySQL slow query log slow log queries analysis

Source: Internet
Author: User
Tags mysql slow query log

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]#

Related Article

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.