MySQL Slow query log (ii)

The slow query log is used to optimize the query statement, the following are several parameters, 5.5 can be later to the subtle (MS)

Mysql> Show variables like '%slow% ';
+---------------------------+-------------------------------+
| variable_name | Value |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| Slow_launch_time | 2 |
| slow_query_log | On |
| Slow_query_log_file | /opt/mysql/data/hack-slow.log |
+---------------------------+-------------------------------+
5 rows in Set (0.45 sec)

Mysql> Show variables like '%long% ';
+--------------------------------------------------------+----------+
| variable_name | Value |
+--------------------------------------------------------+----------+
| long_query_time | 0.050000 |
| Performance_schema_events_stages_history_long_size | 10000 |
| Performance_schema_events_statements_history_long_size | 10000 |
| Performance_schema_events_waits_history_long_size | 10000 |
+--------------------------------------------------------+----------+
4 rows in Set (0.03 sec)

Mysql> viewing slow query logs via tail or Mysqldumslow

[Email protected] data]# tail-f Hack-slow.log
TCP port:3306 Unix Socket:/usr/local/mysql/mysql.sock
Time Id Command Argument
/usr/local/mysql/bin/mysqld, Version:5.6.14-log (Source distribution). Started with:
TCP port:3306 Unix Socket:/usr/local/mysql/mysql.sock
Time Id Command Argument
# time:150417 1:23:49
# [email protected]: root[root] @ localhost [] id:1
# query_time:0.118892 lock_time:0.000000 rows_sent:1 rows_examined:0
SET timestamp=1429205029;
Select version ();

# time:150417 1:24:20
# [email protected]: root[root] @ localhost [] id:1
# query_time:0.426234 lock_time:0.139966 rows_sent:5 Rows_examined:5
SET timestamp=1429205060;
Show variables like '%slow% ';
^c
[Email protected] data]# Mysqldumpslow Hack-slow.log

Reading MySQL slow query log from Hack-slow.log
count:1 time=0.29s (0s) lock=0.14s (0s) rows=5.0 (5), Root[root] @localhost
Show variables like ' S '

count:1 time=0.12s (0s) lock=0.00s (0s) rows=1.0 (1), Root[root] @localhost
Select Version ()

[Email protected] data]#

[Email protected] data]#Mysqldumpslow--help
Usage:mysqldumpslow [OPTS ...] [LOGS ...]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose
--debug Debug
--help write this text to standard output

-V Verbose
-D Debug
-S ORDER what to sort by (Al, at, AR, C, L, R, T), ' on ' is default
Al:average Lock Time
Ar:average rows Sent
At:average Query Time
C:count
L:lock time
R:rows sent
T:query time
-R Reverse the sort order (largest last instead of first)
-T NUM just show the top n queries
-A don ' t abstract all numbers-N and strings to ' S '
-N NUM abstract numbers with at least n digits within names
-G PATTERN Grep:only Consider stmts that include the This string
-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),
Default is ' * ', i.e. match all
-I name name of server instance (if using Mysql.server startup script)
-L don ' t subtract lock time from total time

[Email protected] data]#
In addition, starting with the 5.1 version, the slow query log can be logged into the database, and there is a slow_log table in MySQL

Mysql> Show CREATE TABLE Mysql.slow_log \g
1. Row ***************************
Table:slow_log
Create table:create Table ' Slow_log ' (
' Start_time ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp,
' User_host ' Mediumtext not NULL,
' Query_time ' time not NULL,
' Lock_time ' time not NULL,
' Rows_sent ' int (one) is not NULL,
' rows_examined ' int (one) is not NULL,
' DB ' varchar (+) not NULL,
' last_insert_id ' int (one) is not NULL,
' insert_id ' int (one) is not NULL,
' server_id ' int (ten) unsigned not NULL,
' Sql_text ' Mediumtext not NULL,
' thread_id ' bigint (+) unsigned not NULL
) engine=csv DEFAULT charset=utf8 comment= ' Slow log '
1 row in Set (0.07 sec)

Mysql> can see the table's engine as CSV

Mysql> Show variables like '%log_output% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Log_output | FILE |
+---------------+-------+
1 row in Set (0.03 sec)

mysql> set global log_output= ' file,table '; change log output to files and tables
Query OK, 0 rows affected (0.07 sec)

Mysql> Show variables like '%log_output% ';
+---------------+------------+
| variable_name | Value |
+---------------+------------+
| Log_output | file,table |
+---------------+------------+
1 row in Set (0.09 sec)

Mysql> Select Sleep (10);
+-----------+
| Sleep (10) |
+-----------+
| 0 |
+-----------+
1 row in Set (10.09 sec)

Mysql> SELECT * from Mysql.slow_log \g i open table to query has two statements to reach the time of slow query setting
1. Row ***************************
START_TIME:2015-04-17 01:33:50
User_host:root[root] @ localhost []
query_time:00:00:00
lock_time:00:00:00
rows_sent:0
rows_examined:0
Db:
last_insert_id:0
insert_id:0
Server_id:13
Sql_text:set global log_output= ' file,table '
Thread_id:1
2. Row ***************************
START_TIME:2015-04-17 01:34:17
User_host:root[root] @ localhost []
Query_time:00:00:10
lock_time:00:00:00
Rows_sent:1
rows_examined:0
Db:
last_insert_id:0
insert_id:0
Server_id:13
Sql_text:select Sleep (10)
Thread_id:1
2 rows in Set (0.01 sec)

mysql> Modify Slow_log's storage engine to MyISAM

mysql> ALTER TABLE Mysql.slow_log Engine=myisam;
ERROR 1580 (HY000): Cannot ' ALTER ' a log table if logging is enabled
mysql> set global Slow_query_log=off;
Query OK, 0 rows Affected (0.00 sec)

mysql> ALTER TABLE Mysql.slow_log Engine=myisam;
Query OK, 3 rows affected (0.15 sec)
Records:3 duplicates:0 warnings:0

Mysql>

MySQL Slow query log (ii)