Slow query log

Environment: ubuntu14.04 mysql5.7

Slow query log is logged when the query does not return results for a certain amount of time

Slow query logs can find SQL statements that perform inefficiently and help with database optimization

The slow query log does not open by default and does not have to be turned on, and needs to be opened manually when sampling analysis

Related parameters

        slow-querg-log=on/off             --  whether to turn on slow query log         slow_ query_log_file=filename             specifying a slow query log save path and file name , default storage/usr/local/mysql/data/hostname-slow.log        long_query_time=2             --  A query statement that does not return a result after 2 seconds belongs to slow query          long-queries-not-using-indexes             --  record all query statements that are not used to the index          min_examined_row_limit=1000            --  Record slow queries that are caused by finding more than 1000 extra times         log-slow-admin-statements             --  record those slow optimize table,anakyze table and alter table statements          log-slow-slave-statements             --  record slow queries generated by slave

First, start

-The parameters are written in the my.cnf file and need to be restarted

-Do not restart modify slow query configuration

Set global.slow_query_log=1 turn on slow query log set global.long_query_time=3 3 seconds after the query that did not return the result is slow query other Commands can be consulted by the following command > show variables like '%slow% ';

Second, slow query log analysis tool

1.mysqldumpslow

MySQL comes with analysis tools, not to see, only do simple statistics

2.mysqlsla

Analysis tools from backmysql.com

· Download

Download at the official website or download at the system prompt

# wget http://backmysql.com/scripts/mysqlsla-2.03.tar.gz

       · Extract

            # tar zxvf  mysqlsla-2.03.tar.gz                                  mysqlsla-2.03/                 mysqlsla-2.03/changes                 mysqlsla-2.03/INSTALL                 mysqlsla-2.03/README                 mysqlsla-2.03/Makefile.PL                 mysqlsla-2.03/bin/                 mysqlsla-2.03/bin/mysqlsla                 mysqlsla-2.03/META.yml                 mysqlsla-2.03/lib/                 mysqlsla-2.03/lib/mysqlsla.pm                 mysqlsla-2.03/MANIFEST             # cd mysqlsla-2.03/                                //into the installation directory//

· Execution Perl Script Check package dependencies

# perl makefile.pl//Configuration//

· Compiling the installation

# make # Make Install

How to use

            # mysqlsla -lt slow  nengjian-slow.logreport for slow logs: nengjian-slow.log2 queries total,  1 uniqueSorted by  ' T_sum ' grand totals: time 7 s, lock 0  s, rows sent 2, rows examined 0___________________________________________ ___________________________ 001 count         : 2    (100.00%) time          : 7.001213 s  total, 3.500606 s avg, 3.000447 s to 4.000766 s max    (100.00%) lock time  (s)  : 0 total, 0 avg, 0 to 0  max   (0.00%) rows sent     : 1 avg, 1 to 1  max   (100.00%) Rows examined : 0 avg, 0 to 0 max   (0.00%) Database       :Users         :         [email protected]  : 100.00%  (2)  of query,  100.00%  (2)  of all usersQuery abstract:SET timestamp=N; SELECT  Sleep (N); Query sample:set timestamp=1464159700;select sleep (3);

3.percona-toolkit (not)

Dependency Packages

Percona-toolkit

Perl-io-socket-ssl

Perl-net-libidn

Perl-net-sslesy

Third, delete the slow query log

The slow query log will continue to grow. So only in the sampling analysis of such special work only to open, other times is closed, useless slow query log to be deleted in time.

1. Turn off the slow query log

Comment out parameters about the slow query log in the MY.CNF configuration file

2. Delete log files

# rm-f/usr/local/mysql/data/hostname-slow.log

Slow query log