命令记不住

slow_log慢日志

1.作用

记录慢SQL语句的日志

2.开启慢日志

默认没开启
2.1开关:

db01-[x86]>select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

slow_query_log=1

2.2文件位置及名字

db01-[x86]>select @@slow_query_log_file;
+-----------------------+
| @@slow_query_log_file |
+-----------------------+
| /data/mysql/slow.log  |
+-----------------------+
1 row in set (0.00 sec)

slow_query_log_file=/data/mysql/slow.log

2.3设定慢查询时间:

db01-[x86]>show variables like '%long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

long_query_time=0.1

2.4没走索引的语句也记录:
此参数设置到my.cnf

log_queries_not_using_indexes

2.5永久开启慢日志功能

vim /etc/my.cnf 

slow_query_log=1   
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes

/etc/init.d/mysqld restart

3.mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log (并不好用，建议第三方工具)

[root@db01 mysql]#mysqldumpslow -s c -t 10 /data/mysql/slow.log

Reading mysql slow query log from /data/mysql/slow.log
Count: 3  Time=0.26s (0s)  Lock=0.00s (0s)  Rows=4111.0 (12333), root[root]@localhost
  select * from t1 where stuname like 'S'

Count: 2  Time=0.29s (0s)  Lock=0.00s (0s)  Rows=0.5 (1), root[root]@localhost
  select * from t1 where stuname='S'

Count: 1  Time=0.14s (0s)  Lock=0.00s (0s)  Rows=99999.0 (99999), root[root]@localhost
  select * from t1 where stuid < N

Count: 1  Time=1.46s (1s)  Lock=0.00s (0s)  Rows=500000.0 (500000), root[root]@localhost
  select * from t1

Died at /server/tools/mysql/bin/mysqldumpslow line 161, <> chunk 7.

4.安装percona-toolkit日志分析工具

https://www.percona.com/downloads/percona-toolkit/LATEST/

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

• 1.rpm安装percona-toolkit-2.2.20
• 2.源码安装percona-toolkit-2.2.20

[root@db01 percona-toolkit-2.2.20]#perl Makefile.PL 
[root@db01 percona-toolkit-2.2.20]#make
[root@db01 percona-toolkit-2.2.20]#make install

toolkit工具包中的命令:

./pt-query-diagest  /data/mysql/slow.log 

[root@db01 ~]#cd /usr/local/bin
[root@db01 bin]#./pt-query-digest /data/mysql/slow.log 
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
# Query 3: 0.08 QPS, 0.02x concurrency, ID 0x3ABD85113CCFDE0F at byte 602
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2019-07-18T14:05:10 to 2019-07-18T14:05:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         28       2
# Exec time     19   587ms   206ms   381ms   294ms   381ms   123ms   294ms
# Lock time     25   200us    95us   105us   100us   105us     7us   100us
# Rows sent      0       1       0       1    0.50       1    0.71    0.50
# Rows examine  32 976.56k 488.28k 488.28k 488.28k 488.28k       0 488.28k
# Query size    29      85      42      43   42.50      43    0.71   42.50
# String:
# Databases    oldboy
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `oldboy` LIKE 't1'\G
#    SHOW CREATE TABLE `oldboy`.`t1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from t1 where stuname='alexsb_100'\G

5.使用Anemometer基于pt-query-digest将MySQL慢查询可视化帮助文档

https://www.jianshu.com/p/092fd6359e43