Slow Query Log
1. The slow query log is logged when the query does not return results for a certain amount of time.
2. The slow query log helps DBAs find SQL statements that perform inefficiently and help with database optimization efforts.
3. The slow query log is not turned on by default, and it does not have to be turned on. (The performance of the system will be consumed if it is turned on)
4. Manually open when sampling analysis is required.
slow query log related parameters
After you modify the following configuration in mysql.cnf, you need to restart the database service for it to take effect.
Slow_query_log=on|off---whether to turn on slow query log
Slow_query_log_file=filename---Specify the slow query log save path and file name. Default values are used if not set. The default location is the directory where the database files are located, with the name Hostname-slow.log
long_query_time=2---Specifies how many seconds a query statement that does not return a result belongs to a slow query.
Long-queries-not-using-indexes---Records 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, ANALYZE table, and ALTER TABLE statements.
Log-slow-slave-statements---Records the slow query generated by slave.
Do not restart modify slow query configuration
SET @ @global. slow_query_log=1---1 Open 0 off
SET @ @global. long_query_time=3
Or
SET Global Slow_query_log=1
SET Global long_query_time=3
Other parameters can be consulted by the following command:
Show variables like '%slow% ';
model turns on slow query log:
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.22 sec)
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.04 sec)
[Email protected] ~]# cd/var/lib/mysql/
[Email protected] mysql]# pwd
/var/lib/mysql
[[email protected] mysql]# ls---directory will have a host name-slow.log file
AUTO.CNF Gusha.wyzc.com.err Ib_logfile1 rpm_upgrade_history
Gusha gusha.wyzc.com.pid MySQL Rpm_upgrade_marker-last
Gusha.log ibdata1 Mysql.sock t1.ibd
Gusha-slow.log Ib_logfile0 Performance_schema Worldb
Mysql> Select Sleep (3);
+----------+
| Sleep (3) |
+----------+
| 0 |
+----------+
1 row in Set (3.05 sec)
Mysql> Select variables like ' long% ';
ERROR 1054 (42S22): Unknown column ' Variables ' in ' Field list '
Mysql> Show variables like ' long% ';
+-----------------+----------+
| variable_name | Value |
+-----------------+----------+
| Long_query_time | 2.000000 |
+-----------------+----------+
1 row in Set (0.25 sec)
[[email protected] mysql]# tail-f gusha-slow.log---View information about the journal record
/usr/sbin/mysqld, version:5.6.16 (MySQL Community Server (GPL)). Started with:
TCP port:3306 Unix Socket:/var/lib/mysql/mysql.sock
Time Id Command Argument
# time:150204 3:13:06
# [email protected]: root[root] @ localhost [] id:23
# query_time:3.022983 lock_time:0.000000 rows_sent:1 rows_examined:0
SET timestamp=1422990786;
Select Sleep (3);
Common slow query analysis tools
1.mysqldumpslow---MySQL comes with tools that show unfriendly
2.mysqlsla
An analysis tool produced by hackmysql.com. You can download it on its website or at the system prompt by entering the following command:
#wget Http://hackmysql.com/scripts/mysqlsla
3.percona-toolkit in pt-query-digest---need to be aware of dependency packages when installing
A-mysqldumpslow
[Email protected] mysql]#Mysqldumpslow Gusha-slow.log---Slow log file name
Reading MySQL slow query log from Gusha-slow.log
Count:1 time=3.02s (3s) lock=0.00s (0s) rows=1.0 (1), Root[root] @localhost
Select Sleep (N)
B-mysqlsla
[Email protected] ~]# cd/var/mysql-7/
[[email protected] mysql-7]# ls
mysql-5.6.17-1.el6.src.rpm mysqlsla-2.03
Mysql-5.6.17.tar.gz percona-toolkit-2.2.7-1.noarch.rpm
[Email protected] mysql-7]# chmod +x mysqlsla-2.03
[Email protected] mysql-7]#./mysqlsla-2.03/var/lib/mysql/gusha-slow.log
auto-detected logs as slow logs
Report for slow logs:/var/lib/mysql/gusha-slow.log
1 queries total, 1 unique
Sorted by ' T_sum '
Grand Totals:time 3 S, Lock 0 s, rows sent 1, rows examined 0
______________________________________________________________________ 001 ___
Count:1 (100%)
time:3.022983 s Total, 3.022983 s AVG, 3.022983 s to 3.022983 s Max (100%)
Lock time (s): 0 Total, 0 AVG, 0 to 0 max (0%)
Rows sent:1 AVG, 1 to 1 max (100%)
Rows examined:0 avg, 0 to 0 max (0%)
Database:
Users:
[Email protected]: 100.00% (1) of query, 100.00% (1) of all users
Query Abstract:
SET timestamp=n; SELECT sleep (N);
Query Sample:
SET timestamp=1422990786;
Select Sleep (3);
C-percona-toolkit
[email protected] mysql-7]# Yum install percona-toolkit-2.2.7-1.noarch.rpm
.......
[Email protected] mysql-7]# Pt-query-digest/var/lib/mysql/gusha-slow.log
# 170ms User Time, 20ms system time, 23.98M rss, 202.45M vsz
# current date:wed Feb 4 03:33:40 2015
# Hostname:gusha.wyzc.com
# Files:/var/lib/mysql/gusha-slow.log
# overall:1 Total, 1 unique, 0 QPS, 0x concurrency ______________________
# time Range:all events occurred at 2015-02-04 03:13:06
# Attribute Total min max avg 95% StdDev Median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3s 3s 3s 3s 3s 0 3s
# Lock time 0 0 0 0 0 0 0
# Rows sent 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0
# Query Size 15 15 15 15 15 0 15
# profile
# Rank Query ID Response time Calls r/call v/m Item
# ==== ================== ============= ===== ====== ===== ======
# 1 0xf9a57dd5a41825ca 3.0230 100.0% 1 3.0230 0.00 SELECT
# Query 1:0 QPS, 0x concurrency, ID 0xf9a57dd5a41825ca at byte 0 ________
# This item was included in the because it matches--limit.
# scores:v/m = 0.00
# time Range:all events occurred at 2015-02-04 03:13:06
# Attribute pct Total min max avg 95% StdDev Median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1
# Exec time 3s 3s 3s 3s 3s 0 3s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 100 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query Size 100 15 15 15 15 15 0 15
# String:
# Hosts localhost
# Users Root
# Query_time Distribution
# 1us
# 10US
# 100US
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# EXPLAIN/*!50100 partitions*/
Select Sleep (3) \g
Delete slow query log
Slow query log will continue to grow. This is often done only when special work such as sampling analysis is turned on, while others are closed. Useless slow log to remember to delete in time.
There are two ways to delete a slow query log:
1. The operating system commands are removed directly.
2. Use the flush logs command at the MySQL prompt, or use the Mysqladmin flush-logs command at the system prompt.
mysql--One of the slow query logs
