MySQL Slow query log Open Analysis detailed introduction

(1) Configuration Open

Linux:

Add the following statement to the MySQL configuration file my.cnf:

Log-slow-queries=/var/lib/mysql/slowquery.log #指定日志文件存放位置, can be null, the system will give a default file Host_name-slow.log
Long_query_time=5 #记录超过的时间, the default is 10s, this is set to query time more than 5s query statement
log-queries-not-using-indexes = on # list query statements that do not use indexes
#log-queries-not-using-indexes whether to log all query that does not use the index, you can decide whether to open
#log-long-format whether to log all query records, including queries that do not use indexes

Windows:

In My.ini [mysqld] Add the following statement (statement options and Interpretation Ibid.):

Log-slow-queries = E:\mysql\log\mysqlslowquery.log
Long_query_time = 5

(2) View Way

Linux:

Use MySQL with command mysqldumpslow view

Common commands

-S order what to sort by (t, at, L, AL, R, AR etc), "at" is default
-T NUM just show the top n queries
The-G pattern grep:only Consider stmts the include this string

eg

s, is the order, the description is not written in detail, I use down, including looking at the code, mainly have C,t,l,r and ac,at,al,ar, according to query times, time, lock time and the number of records returned to the order, the previous plus a in reverse
-T is the meaning of top N, which is to return the number of previous data
-G, you can write a regular matching pattern, case insensitive

specific commands are used as follows :

Mysqldumpslow-s c-t Host-slow.log

Mysqldumpslow-s r-t Host-slow.log

The above command shows the 20 SQL statements that have the most access and the 20 SQL that returns the recordset.

Mysqldumpslow-t 10-s t-g "left join" Host-slow.log This is the time to return the first 10 inside the SQL statement containing the left-hand connection.

Windows:

When you first open MySQL slow query, will be in your designated directory to create this record file, this article is mysqlslowquery.log, the content of this document is roughly as follows (the first time to open the MySQL slow query)

E:\web\mysql\bin\mysqld, Version:5.4.3-beta-community-log (MySQL community Server (GPL)). Started with:
TCP port:3306, Named Pipe: (NULL)
Time Id Command Argument

You can view the number of records in a slow query by using the following command:

Mysql> show global status like '%slow% ';
+ ——————— + ——-+
| variable_name | Value |
+ ——————— + ——-+
| Slow_launch_threads | 0 |
| slow_queries | 0 |
+ ——————— + ——-+

Generated logs we can use the Mysqlsla Slow query log analysis tool to deal with specific examples

First, the installation of Mysqlsla

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
Tar zvxf mysqlsla-2.03.tar.gz
CD mysqlsla-2.03
Perl makefile.pl
Make
Make install

second, the use of parameter description

1.  --log-type (-LT) type logs:
This parameter is used to define the type of log, mainly slow, general, binary, MSL, UDL, parsing slow log by making For slow.
2.  --sort:
  Define what parameters to use to sort the analysis results, by default, by T_sum.
 t_sum sorted by total time, c_sum by total number of times
3.  --top:
Show the number of SQL, default is 10, to get the number of first
4.   sorted by rule-- Statement-filter (-SF) [+-][type]:
Filters The types of SQL statements, such as SELECT, Update, Drop. [TYPE] has SELECT, CREATE, DROP, UPDATE, INSERT, such as "+select,insert", does not appear by default is-, that is not included.
5.  --databases db:   
Log of which library to process:
 
Three, statistical parameter description

1 Queries total: Number of queries  
2 Unique: The number of SQL after heavy  
3. Sorted by: Output the contents of the report to sort the most significant slow SQL statistics, including the average execution time, waiting for the lock Time, the total number of resulting rows, the total number of rows scanned . 
4. The number of executions of the count:sql and the percentage of the total number of slow log . 
5. Time: Execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time . 
6 of the time: remove the fastest and slowest SQL, coverage 95% of SQL execution time . 
7 . Lock time: Wait for lock . 
8.95% of lock:95% Slow SQL wait lock time . 
9.Rows Sent: Results row statistics, including average, minimum, maximum number . 
10 . Rows examined: The number of rows scanned . 
11.Database: Which database belongs to.
12.Users: Which user, IP, represents the percentage of SQL executed by all users.
Query Abstract: An abstract SQL statement.
Query Sample:sql statements.
 
Four, using example

1. Slow query SQL for all select queries for Dowload_server1-slow.log, and display the longest execution time of 10 SQL, and write to Sql_time.sql
Mysqlsla-lt slow-sf "+select"-top dowload_server1-slow.log >test_time.log
2. Statistics slow query file for the Dowload_server1-slow.log database for all SELECT and update Ultraxsmutf8 queries SQL, and query the most times of the 100 SQL, and write to the Sql_num.sql
Mysqlsla-lt slow-sf "+select,update"-top 100-sort c_sum-db Ultraxsmutf8 dowload_server1-slow.log >num_time.log

v. FAQ

1. Error:
Can ' t locate dbi.pm in @INC (@INC contains:/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-.......
Solution: Perl-mcpan-e ' Install DBI ' or Cpan DBI
2. Error:
Use the uninitialized value in numeric comparison (<=>) At/usr/bin/mysqlsla line 2962.

Solution: When using-sort T_SU will appear this error, the default is to use time sorting, so you can remove.

Delete Slow query log

Delete slow query log directly via command rm-f

Third, the emergence of problems

Slow query log is not automatically generated, (if there is a backup, copy back again is not good to make)

Iv. Methods of Settlement

Administrator login mysql execute command flush logs;
If, after execution, the prompt executes successfully, and the slow query log is not actually generated, execute the following command (close the slow query first and then open)
SET GLOBAL log_slow_queries = off; (Close slow query)
SET GLOBAL log_slow_queries = on; (Re-open slow query)
Problem solving, ok!

V. RECOMMENDATIONS

If you encounter a slow query log file is too large, need to reclaim space to use, or other reasons need to delete slow query log files, it is recommended not to directly RM slow query log files, you can empty log files,
such as: >mysql-slow.log