Mysql Slow Query log

First, the concept part:

As the name implies, the slow query log is a long execution of query, which is what we often call slow query, by setting--log-slow-queries[=file_name] to open the function and set the record location and file name, The default file name is Hostname-slow.log, and the default directory is the data directory.
The slow query log is a simple text format that allows you to view the content in a variety of text editors. It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information. MySQL also provides a tool program mysqlslowdump for analyzing full query logs to help database managers resolve possible performance issues.

Second, slow query log related variables

2.1. Command line parameters:

--log-slow-queries

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

2.2. System Variables

Log_slow_queries

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

Slow_query_log

Slow quere log switch, when the value is 1, turn on slow query.

Slow_query_log_file

Specify the log file location, can be empty, the system will give a default file Host_name-slow.log

Long_query_time

Record over time, default is 10s

Log_queries_not_using_indexes

Log down does not use the index of query, depending on the circumstances to decide whether to open

Third, the experimental part:

----Use the log_slow_queries parameter to open a slow query, which will appear in the Err log because the parameter is obsolete----Modify the My.cnf file, add the Log_slow_queries parameter[[Email protected] ~]# VI/opt/mysql5.5/My.cnf[[Email protected] ~]# Cat/opt/mysql5.5/my.cnf|grep‘^log_slow‘Log_slow_queries=/tmp/mysqlslow.Log----Empty The Err log contents:[[Email protected] ~]# Cat/dev/Null>/tmp/Mysql3306.err[[Email protected] ~]# service MySQL startstarting mysql ....[Ok]----View the Err log information[[Email protected] Data]# Tail-F/tmp/Mysql3306.err13080102:26:Mysqld_safe Starting mysqld Daemonwith databasesFrom/opt/mysql5.5/Data1308012:26:28[Warning] The syntax‘--log-slow-queries‘is deprecatedand would be removedIn a future release. PleaseUse‘--slow-query-log‘/‘--slow-query-log-file‘instead.1308012:26:28[Warning] You needToUse--Log-bin to make--binlog-format work.1308012:26:Innodb:the InnoDB Memory HeapIsDisabled1308012:26:Innodb:mutexesand Rw_locksUse InnoDB‘s own implementation130801 2:26:28 innodb:compressed tables use zlib 1.2.3130801 2:26:28 innodb:initializing buffer pool , size = 128.0m130801 2:26:28 innodb:completed initialization of buffer pool130801 2:26:28 innodb:highest supported file Format is barracuda.130801 2:26:28 innodb:waiting for the background threads to start130801 2:26:30 innodb:1.1.8 Starte D Log sequence number 3069452130801 2:26:30 [note] Event scheduler:loaded 0 events130801 2:26:30 [note]/opt/mysql5.5/bin/m Ysqld:ready for connections. Version:‘5.5.22-Log‘Socket‘/tmp/mysql.sock‘port:3306 Source Distribution----using Slow_query_log and Slow_query_log_file[[email protected] ~]# vi/opt/mysql5.5/my.cnf[[email protected] ~]# cat/opt/ MYSQL5.5/MY.CNF |grep‘^slow_query‘Slow_query_log = 1slow_query_log_file =/tmp/mysqlslow.log1[[email protected] ~]# service mysql startstarting mysql ... [OK] [Email protected] ~]# Mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.22-log Source distributioncopyright (c) (+), Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type' Help;‘Or' \h‘For help. Type' \c‘To clear the current input statement.mysql> show variables like‘%slow%‘;+---------------------+---------------------+| variable_name | Value |+---------------------+---------------------+| log_slow_queries | On | | Slow_launch_time | 10 | | Slow_query_log | On | | Slow_query_log_file | /tmp/mysqlslow.log1 |+---------------------+---------------------+4 rows in Set (0.00 sec)----about the Slow_launch_time parameter, first modify the parameter value mysql> set global long_query_time=1; Query OK, 0 rows Affected (0.00 sec) mysql> Show variables like‘%long_query%‘;+-----------------+----------+| variable_name | Value |+-----------------+----------+| Long_query_time | 1.000000 |+-----------------+----------+1 row in Set (0.00 sec)----Take a look at the contents of/tmp/mysqlslow.log1 mysql> Select Database (); +------------+| Database () |+------------+| NULL |+------------+1 row in Set (0.00 sec) mysql> use test;database changedmysql> show tables; Empty Set (0.00 sec) mysql> CREATE table T as select * from Information_schema.tables; Query OK, affected (0.38 sec) records:85 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, affected (0.05 sec) records:85 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, affected rows (0.03 sec) records:170 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, 340 rows affected (0.05 sec) records:340 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, 680 rows Affected (0.08 sec) records:680 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, 1360 rows affected (0.29 sec) records:1360 duplicates:0 warnings:0mysql> INSERT INTO t select * from T; Query OK, 2720 rows Affected (1.49 sec) records:2720 duplicates:0 warnings:0 ----is over 1s here, view/tmp/mysqlslow.log1[[email protected] data]# tail-f/tmp/mysqlslow.log1# time:130801 2:36:25# [email Protected]: Root[root] @ localhost []# query_time:2.274219 lock_time:0.000322 rows_sent:0 rows_examined:5440use Test;S ET Timestamp=1375295785;insert into T-select * from T;Experiment on log_queries_not_using_indexes parameters of----mysql> show variables like‘%indexes%‘;+-------------------------------+-------+| variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |+-------------------------------+-------+1 row in Set (0.00 sec) mysql> Set log_queries_not_using_indexes = 1; ERROR 1229 (HY000): Variable' Log_queries_not_using_indexes‘is a global variable and should are set with set globalmysql> set GLOBAL log_queries_not_using_indexes = 1; Query OK, 0 rows affected (0.01 sec) mysql> Show variables like‘%indexes%‘;+-------------------------------+-------+| variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | On |+-------------------------------+-------+1 row in Set (0.00 sec) mysql> desc t;+-----------------+-------------- -------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+---------------------+------+-----+---------+-------+| Table_catalog | VARCHAR (512) | NO | | | | | Table_schema | VARCHAR (64) | NO | | | | | table_name | VARCHAR (64) | NO | | | | | Table_type | VARCHAR (64) | NO | | | | | ENGINE | VARCHAR (64) | YES | | NULL | | | VERSION | bigint (+) unsigned | YES | | NULL | | | Row_format | varchar (10) | YES | | NULL | | | Table_rows | bigint (+) unsigned | YES | | NULL | | | Avg_row_length | bigint (+) unsigned | YES | | NULL | | | Data_length | bigint (+) unsigned | YES | | NULL | | | Max_data_length | bigint (+) unsigned | YES | | NULL | | | Index_length | bigint (+) unsigned | YES | | NULL | | | Data_Free | bigint (+) unsigned | YES | | NULL | | | auto_increment | bigint (+) unsigned | YES | | NULL | | | Create_time | datetime | YES | | NULL | | | Update_time | datetime | YES | | NULL | | | Check_time | datetime | YES | | NULL | | | table_collation | varchar (32) | YES | | NULL | | | CHECKSUM | bigint (+) unsigned | YES | | NULL | | | create_options | varchar (255) | YES | | NULL | | | table_comment | varchar (2048) | NO | | | |+-----------------+---------------------+------+-----+---------+-------+21 rows in Set (0.05 sec)----The following command is to view the index of mysql> Show index from T; Empty Set (0.01 sec) mysql> select * from t where engine=' xxx'; Empty Set (0.18 sec) # time:130801 2:43:43# [email protected]: root[root] @ localhost []# query_time:0.185773 lock_time: 0.148868 rows_sent:0 rows_examined:5440set Timestamp=1375296223;select * from T where engine=' xxx'; 

Iv. Mysqldumpslow

If there is a lot of log content, with the eyes of a single look will be exhausted, MySQL comes with the tool of analysis, using the following methods:

[[Email protected] Data]# Mysqldumpslow--Helpusage:mysqldumpslow[OPTS ...][LOGS ...]ParseAnd summarize the MySQL slow queryLog. Options are--Verbose verbose--Debug Debug--Help write the text to standard output-V Verbose-D Debug-SORDER whatTo sortBy (Al, at, AR, C, L, R, T),‘At‘IsDefaultAl:average Lock time ar:average rows sent At:average query time C:CountL:lock time R:rows sent T:query time-RReverse the SortOrder (largest last insteadOfFirst-t NUM just show the top n Queries -a don "t abstract All numbers to N and strings to  ' s ' -n NUM abstract numbers with at least n digits within names-g PATTERN Grep:only consider stmts that include thi S string-h HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can being wildcard), default is  ' *"  "t subtract lock ti Me from total time          

Article quoted----www.cnblogs.com/Richardzhu/p/3230221.html

Mysql Slow Query log