`
Donald_Draper
  • 浏览: 980938 次
社区版块
存档分类
最新评论

MySQL慢日志

mysql 
阅读更多
The Slow Query Log:https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
Selecting General Query and Slow Query Log Output Destinations:https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
MySQL慢查询日志总结:http://www.cnblogs.com/kerrycode/p/5593204.html
MYSQL开启慢查询日志实施:http://www.cnblogs.com/wangtao_20/archive/2013/09/06/3304645.html
引言：
    慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值long_query_time的SQL语句，SQL执行时间超过long_query_timeL，则将记录到慢查询日志中。long_query_time的默认值为10，意思是执行时间10s以上的语句，将会被记录慢查询日志中。long_query_time精确到毫秒，即可配置1.005，这种形式的阀值。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数或通过配置文件（my.cnf），当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。就启动慢查询日志的方法而言，建议用手动来设置，不建议用配置文件，手动设置的话，不用重启就能起效，而配置文件方式需要重启，同时我们一般会在开发环境中，来调优，到正式就不用了，所以我们用手动设置方法。慢查询日志支持将日志记录写入文件(FILE)，也支持将日志记录写入数据库表(TABLE)。
今天我们就来看手动方式启动慢查询日志功能：
查看慢查询日志是否开启 
mysql> show variables like '%slow_query%';
    -> ;
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| slow_query_log            | OFF                             |
| slow_query_log_file       | /mysqldata/Donald_Draper-slow.log |
+---------------------------+---------------------------------+
2 rows in set


slow_query_log控制慢查询日志，slow_query_log_file为慢查询日志文件，一般为hostname-slow.log，主要这个文件mysql必须有权限读写。

开启慢查询日志功能 
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected

mysql> show variables like '%slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /mysqldata/Donald_Draper-slow.log |
+---------------------+---------------------------------+
2 rows in set

已开启

再来查看慢查询响应时间阀值long_query_time 
mysql> show variables like '%query_time%';
+------------------------------+---------------------------------+
| Variable_name                | Value                           |
+------------------------------+---------------------------------+
| long_query_time              | 10.000000                       |
+------------------------------+---------------------------------+
1 rows in set

long_query_time的默认值为10，意思是执行时间10s以上的语句，将会被记录慢查询日志中。
long_query_time精确到毫秒，即可配置1.005，这种形式的阀值。

修改慢查询响应时间阀值long_query_time为1秒 
mysql> set global  long_query_time = 1;
Query OK, 0 rows affected

mysql> show variables like '%query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set


查看修改后响应时间阀值long_query_time，
注意：这个时候不能在用上面的命令show variables like '%query_time%'，这个命令执行后还是原来的值，这个可能是mysql使用的缓存，我们用show global variables like '%query_time%'命令来查看 

mysql> show global variables like '%query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set

mysql>


来看慢查询日志默认输出的格式： 

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set

mysql>

log_output有两种方式，一种为FILE，及记录中日志中，一种为TABLE，将慢日志记录在
mysql.slow_log表中。

先来看第一种方式：
执行查询，查看慢查询日志文件： 
Donald_Draper:~ #tail -f Donald_Draper-slow.log 
# Time: 170518 14:29:14
# User@Host: donald[123456] @  [192.168.31.153]  Id:    16
# Query_time: 0.009314  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use test;
SET timestamp=1495088954;
# administrator command: Init DB;
# Time: 170518 14:29:15
# User@Host: donald[123456] @  [192.168.31.153]  Id:    16
# Query_time: 0.665908  Lock_time: 0.000156 Rows_sent: 69  Rows_examined: 69
SET timestamp=1495088955;
SHOW FULL TABLES WHERE Table_type != 'VIEW';
# Time: 170518 14:29:28
# User@Host: donald[123456] @  [192.168.31.153]  Id:    17
# Query_time: 0.033067  Lock_time: 0.000183 Rows_sent: 1  Rows_examined: 1
SET timestamp=1495088968;
SHOW TABLE STATUS LIKE 'message_info';
# User@Host: donald[123456] @  [192.168.31.153]  Id:    17
# Query_time: 0.048564  Lock_time: 0.000116 Rows_sent: 1000  Rows_examined: 1000
SET timestamp=1495088968;
SELECT * FROM `message_info` LIMIT 0, 1000;
# Time: 170518 14:32:28
# User@Host: donald[123456] @  [192.168.31.153]  Id:    10
# Query_time: 106.102272  Lock_time: 0.000191 Rows_sent: 1  Rows_examined: 8494928
SET timestamp=1495089148;
select count(*) from message_info;


我们取一条来看 
# Time: 170518 14:32:28 发生时间
# User@Host: donald[123456] @  [192.168.31.153]  Id:    10  访问信息
# 查询时间              锁住时间           
# Query_time: 106.102272  Lock_time: 0.000191 Rows_sent: 1  Rows_examined: 8494928
SET timestamp=1495089148;
#执行慢查询语句
select count(*) from message_info;


再来看第二种慢日志输出方式-TABLE：

修改慢日志输出方式log_output为TABLE 
mysql> set global  log_output = 'TABLE';
Query OK, 0 rows affected

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set

mysql>


查询慢日志表 
mysql> select * from mysql.slow_log;
+---------------------+-----------------------------------+------------+-----------+-----------+---------------+------------------+----------------+-----------+-----------+------------------------------------------+-----------+
| start_time          | user_host                         | query_time | lock_time | rows_sent | rows_examined | db               | last_insert_id | insert_id | server_id | sql_text                                 | thread_id |
+---------------------+-----------------------------------+------------+-----------+-----------+---------------+------------------+----------------+-----------+-----------+------------------------------------------+-----------+
| 2017-05-18 14:42:01 | donald[123456] @  [192.168.31.153] | 00:00:00   | 00:00:00  |        12 |            12 | mysql            |              0 |         0 |         1 | SHOW COLUMNS FROM `mysql`.`slow_log`     |        19 |
| 2017-05-18 14:42:30 | donald[123456] @  [192.168.31.153] | 00:00:37   | 00:00:00  |         1 |       1260746 | test |              0 |         0 |         1 | select count(*) from company_info |        10 |
| 2017-05-18 14:42:45 | donald[123456] @  [192.168.31.153] | 00:00:00   | 00:00:00  |         2 |             2 | mysql            |              0 |         0 |         1 | SELECT * FROM `slow_log` LIMIT 0, 1000   |        19 |
+---------------------+-----------------------------------+------------+-----------+-----------+---------------+------------------+----------------+-----------+-----------+------------------------------------------+-----------+
3 rows in set

mysql>


我们也可以配置log_queries参数来设置将没有使用索引的语句记录在
慢日志中

查看log_queries_not_using_indexes 
mysql> show variables like '%log_queries%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set


开启记录没有使用索引的语句功能 
mysql>  set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected

mysql> show variables like '%log_queries%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set


在开启记录没有使用索引的语句功能情况下，我们可以通过log_throttle_queries_not_using_indexes
控制每秒中允许记录没有使用索引的语句数量，默认为0，即不限制，如果不限制的情况，太多这样的日志产生将会影响mysql的性能。 

mysql> show variables like '%log_throttle%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set


直接通过慢日志文件和慢日志表的方式还不能清晰便捷的分析慢查询语句的具体慢的原因，我们可以通过MySQL提供的慢日志分析工具mysqldumpslow来分析慢日志文件。 

Donald_Draper:~ # 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), 'at' 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  只显示最前面n个慢查询
  -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 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

实例：查询语句的排序方式为，平均查询时间，平均锁住时间，查询时间，锁住时间 
Donald_Draper:~ # mysqldumpslow -s at al t l c -t 6 /mysqldata/Donald_Draper-slow.log 

Reading mysql slow query log from al t l c /mysqldata/Donald_Draper-slow.log
Count: 1  Time=173.86s (173s)  Lock=0.00s (0s)  Rows=1.0 (1), donald[123456]@[192.168.31.153]
  select count(*) from message_info

Count: 1  Time=106.10s (106s)  Lock=0.00s (0s)  Rows=1.0 (1), donald[123456]@[192.168.31.153]
  select count(*) from company_info

Count: 1  Time=0.67s (0s)  Lock=0.00s (0s)  Rows=69.0 (69), donald[123456]@[192.168.31.153]
  SHOW FULL TABLES WHERE Table_type != 'S'

Count: 1  Time=0.05s (0s)  Lock=0.00s (0s)  Rows=1000.0 (1000), donald[123456]@[192.168.31.153]
  SELECT * FROM `company_info` LIMIT N, N

Count: 1  Time=0.03s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), donald[123456]@[192.168.31.153]
  SHOW TABLE STATUS LIKE 'S'

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), donald[123456]@[192.168.31.153]


当输出结果的比较多时，我们可以使用管线，结合more或grep，查找需要的信息： 
Donald_Draper:~ # mysqldumpslow -s at al t l c -t 6 /mysqldata/Donald_Draper-slow.log | more/grep


或者用流导向，将排序后的结果导入文件在查看： 
Donald_Draper:~ # mysqldumpslow -s at al t l c -t 6 /mysqldata/Donald_Draper-slow.log > mysql-slow-log-sort.txt


     本篇文章中我们手动设置的所有变量的作用域可为全局和会话，并且可以动态生效。我们设置的都是全局的，由于这些都是手动设置，动态生效，数据库重启时，将会失效，如果不想失效可以写在配置文件中。 

[mysqld]
slow_query_log = 1
slow_query_log_file = /mysqldata/Donald_Draper-slow.log
long_query_time = 1 
log_output=FILE
log-queries-not-using-indexes = 1
log_throttle_queries_not_using_indexes = 10
0
顶
1
踩
分享到：
| 文件通道解析二（文件锁，关闭通道）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    cpp-Slowquery图形化显示MySQL慢日志工具

    "cpp-Slowquery图形化显示MySQL慢日志工具" 指的是一款基于C++语言开发的工具，名为Slowquery，它的主要功能是解析并以图形化方式展示MySQL数据库的慢查询日志。这个工具帮助数据库管理员更直观地理解和分析MySQL...

    mysql慢日志查询

    ### MySQL慢查询日志详解 #### 一、慢查询日志简介 MySQL慢查询日志是一种重要的工具，用于记录执行时间超过指定阈值的SQL语句。这些记录可以帮助数据库管理员和开发人员识别并优化那些效率较低的查询，从而提高...

    mysql慢日志分析工具mysqlsla

    MySQLsla是一款专为MySQL数据库设计的慢查询日志分析工具，它可以帮助数据库管理员高效地分析和理解MySQL服务器上的慢查询情况，从而优化数据库性能。在MySQL的运行过程中，某些SQL语句执行时间过长，就会被记录到慢...

    mysql 慢查日志实时解析工具

    我们知道mysql慢日志解析工具有很多种，比如我们常用的mysqldumpslow、pt-query-digest等，这些都是比较有效的解析工具，今天介绍的是基于go写的一款实时解析日志工具

    perl解释器，在Windows下用于执行mysqldumpslow.pl脚本，进行MySQL慢日志查询

    perl解释器，在Windows下用于执行mysqldumpslow.pl脚本，进行MySQL慢日志查询

    mysql慢日志导入数据库表中

    MySQL慢查询日志以纯文本格式存储，包含每个慢查询的详细信息，如查询时间、SQL语句、执行计划等。开启慢查询日志可以在服务器启动时配置，或者通过`SET GLOBAL slow_query_log = ON`动态设置。日志默认保存在数据...

    MySQL的慢日志线上问题及优化方案

    MySQL 慢日志（slow log）是 MySQL DBA 及其他开发、运维人员需经常关注的一类信息。使用慢日志可找出执行时间较长或未走索引等 SQL 语句，为进行系统调优提供依据。 本文将结合一个线上案例，分析如何正确设置 ...

    Mysql慢查询日志查看

    Mysql 慢查询日志查看 Mysql 慢查询日志查看是指在 Mysql 数据库中，记录和分析执行时间较长的查询语句，以便优化数据库性能和提高查询效率。慢查询日志查看可以帮助数据库管理员和开发者快速地定位和优化数据库中...

    详解mysql慢日志查询

    慢日志查询作用 慢日志查询的主要功能就是，记录sql语句中超过设定的时间阈值的查询语句。例如，一条查询sql语句，我们设置的阈值为1s，当这条查询语句的执行时间超过了1s，则将被写入到慢查询配置的日志中. 慢查询...

    MySQL慢日志实践小结

    MySQL慢日志实践小结，对于数据库性能优化至关重要。慢日志查询功能主要用来记录那些执行时间超过预设阈值的SQL语句，帮助我们识别并优化低效的查询操作。默认情况下，MySQL可能不会开启慢查询日志，因此需要我们...

    mysql慢查询日志的使用

    ### MySQL慢查询日志的使用 #### 概述 MySQL慢查询日志是MySQL提供的一种日志记录方式，用于记录所有执行时间超过指定阈值（默认10秒）的SQL语句，帮助开发者和DBA定位数据库性能瓶颈，优化SQL查询效率。 #### ...

    MySQL慢查询日志

    ### MySQL慢查询日志详解 #### 一、慢查询日志概述 MySQL慢查询日志是一种重要的工具，用于记录执行时间超过预设阈值的SQL语句。通过对这些记录进行分析，可以找出数据库中的性能瓶颈，并据此采取相应的优化措施。...

    mysql的慢日志配置查询和show processlist使用

    MySQL 的慢日志配置查询和 Show Processlist 使用 MySQL 的慢日志配置查询和 Show Processlist 使用是 MySQL 中两个非常重要的概念，它们都是用于优化和 Troubleshooting MySQL 性能的关键工具。在这篇文章中，我们...

    lepus（天兔）监控MySQL主从复制延迟，慢查询

    1. Lepus监控工具：这是一个数据库监控系统，专门用来监控MySQL主从复制延迟和慢查询问题。 2. MySQL主从复制延迟：这指的是MySQL数据库在主从架构模式下，从服务器与主服务器之间的数据复制可能存在时间差，而Lepus...

    MySQL优化篇：慢查询日志.pdf

    首先，要了解MySQL慢查询日志的概念。慢查询日志记录了所有执行时间超过`long_query_time`值的SQL语句。默认情况下，`long_query_time`的值设置为10秒，意味着所有运行时间超过10秒的SQL语句都会被记录下来。然而，...

    慢日志发送邮件脚本

    慢日志发送邮件脚本 filedir="/data/mysql/tmp/sqlcount.xls" tmpfile="/tmp/sqlcount.xls" #echo '<html><body>;">' > ${tmpfile} echo '<html><body>;font-size:12px;">' > ${tmpfile} echo '...

    Mysql慢查询日志_1--如何开启慢查询日志

    MySQL 慢查询日志配置 MySQL 慢查询日志是 MySQL database management system 的一个重要功能，通过该功能可以记录执行时间超过某个阈值的 SQL 语句，从而帮助数据库管理员或开发人员对数据库的性能进行优化和改进...

Magicbox
Global site tag (gtag.js) - Google Analytics