加油 臭小子

MySQL慢查询(一) – 开启慢查询

编程开发 moonygao 603℃ 0评论

一、简介

开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

二、参数说明

slow_query_log :慢查询开启状态
slow_query_log_file : 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
long_query_time 查询超过多少秒才记录
log_output :参数是指定日志的存储方式。log_output=’FILE’表示将日志存入文件,默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output=’FILE,TABLE’。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
slow_launch_time:的设定跟慢查询日志的查询阀值设定不同,表示了thread create的一个阀值,如果thread create的时间超过了这个值,这变量slow_launch_time的值加1.而设置Long_query_time表示超过多少秒的查询就写入日志,默认的是10s,设置为0的话表示记录所有的查询。在Mysql 5.5可以追踪到微秒的查询。

log-queries-not-using-indexes:管理语句和不使用索引的语句是不会记录在慢查询中的,开启后启用未使用索引查询语句,写入log日志。尽量不要开启log-queries-not-using-indexes没有索引查询记录功能,这个功能实际用处不大。就是记录SQL查询的时候,没有索引的通通记录。虽然索引对查询的速度有影响,
但要看数据量大小。因为开启了这个功能以后,select * from tab这样的查询也会被记录在日志中,很快日志文件就会被垃圾信息给充满,从而影响主要的查询慢日志记录的查看。

查询有多少条慢查询记录
show status like "%slow_queries%";

补充说明:

1. MySQL5.21版以前long_query_time 参数的单位是秒,默认值是10。这相当于说最低只能记录执行时间超过 1 秒的查询,怎么记录查询时间超过100毫秒的SQL语句记录呢?在mysql5.21+后版本支持毫秒记录

2.mysql的慢日志,如果没设置根据日期或者大小自动打包或者重新生成文件,在全局变量设置方法下,set global  slow_query_log=1;  如果手动删除或者更文件名。mysql的慢日志活不会写入,把日志路径重新设置一下就可以:set global slow_query_log_file=’/var/log/mysql_slow1.log’; 文件的权限一定设置 777

三、设置步骤

mysql> show variables like ‘slow_query%’;
+—————————+———————————-+
| Variable_name | Value |
+—————————+———————————-+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+—————————+———————————-+

mysql> show variables like ‘long_query_time’;
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+

2.设置方法
方法一:全局变量设置

设置set global  slow_query_log=1; 必须是root或者管理员权限,否则就set slow_query_log=1; 其他命令同样有效

有时候:set global  slow_query_log=1; 没起作用,可以去掉global   ,  set slow_query_log=1; 对当前窗口有效。
将 slow_query_log 全局变量设置为“ON”或者设置为1,(0为关闭)状态

mysql> set global slow_query_log='ON';
mysql> set global slow_query_log=1;

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

mysql> set global long_query_time=1;

方法二:配置文件设置
修改配置文件my.cnf,在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

复制代码
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
复制代码

四、测试

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

补充知识:Mysql 慢查询日志分析工具、软件 五款

mysqldumpslow:官方自带

mysqlsla, hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)

mysql-explain-slow-log, 德国人写的一个perl脚本.
http://www.willamowius.de/mysql-tools.html

mysql-log-filter, google code上找到的一个分析工具.提供了 python 和 php 两种可执行的脚本.
http://code.google.com/p/mysql-log-filter/

myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上.
http://myprofi.sourceforge.net/

总结

工具/功能 一般统计信息 高级统计信息 脚本 优势
mysqldumpslow 支持 不支持 perl mysql官方自带
mysqlsla 支持 支持 perl 功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log 支持 不支持 perl
mysql-log-filter 支持 部分支持 python or php 不失功能的前提下,保持输出简洁
myprofi 支持 不支持 php 非常精简

 

 

 

 

转载请注明:IT起义 » MySQL慢查询(一) – 开启慢查询

喜欢 (0)or分享 (0)