mysql5.5慢日志设置和查询
mysql> showvariables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
|innodb_version | 5.5.31 |
|protocol_version | 10 |
|slave_type_conversions | |
| version | 5.5.31-log |
|version_comment | Sourcedistribution |
|version_compile_machine | x86_64 |
|version_compile_os | Linux |
+-------------------------+---------------------+
7 rows in set (0.00sec)
开启慢查询日志
在mysql的配置文件my.cnf 中的mysqld下方添加以下参数:
log_slow_queries=ON
long_query_time=1
slow_launch_time=1
slow_query_log=ON
slow_query_log_file=/log/mysql/slow_queries.log
注：
long_query_time=1 表示记录查询时间超过1秒的sql
slow_query_log_file 慢日志文件
slow_launch_time : 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
重启mysql后检查结果
mysql> showvariables like '%slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
|log_slow_queries | ON |
|slow_launch_time | 1 |
|slow_query_log | ON |
|slow_query_log_file | /log/mysql/slow_queries.log |
+---------------------+-----------------------------+
4 rows in set (0.00sec)
mysql> showvariables like '%long%';
+---------------------------------------------------+----------+
| Variable_name |Value |
+---------------------------------------------------+----------+
|long_query_time | 1.000000 |
|max_long_data_size | 1048576 |
|performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+----------+
3 rows in set (0.00sec)
测试
mysql> selectsleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00sec)
查看日志是否记录
[root@HE1 /]# tail-f /log/mysql/slow_queries.log
# Time: 16021722:26:54
# User@Host:root[root] @ localhost []
# Query_time:2.000661 Lock_time: 0.000000 Rows_sent:1 Rows_examined: 0
SETtimestamp=1455776814;
select sleep(2);
设置好参数后，以后可通过
mysql>set GLOBAL slow_query_log=1;开启慢日志
mysql> set GLOBAL slow_query_log=0;关闭慢日志
是否记录没用到索引的SQL语句
mysql> showvariables like '%using%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
|log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00sec)
mysql> set globallog_queries_not_using_indexes=1;
Query OK, 0 rowsaffected (0.00 sec)
mysql> showvariables like '%using%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
|log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00sec)
通过mysqldumpslow工具查询慢日志
[root@HE1 bin]#mysqldumpslow --help
Usage: mysqldumpslow[ OPTS... ] [ LOGS... ]
Parse and summarizethe 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
-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
[root@HE1 bin]#mysqldumpslow -s at -n 3 /log/mysql/slow_queries.log
Reading mysql slowquery log from /log/mysql/slow_queries.log
Count: 3 Time=2.33s (7s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost
select sleep(N)
Count: 1 Time=0.37s (0s) Lock=0.44s (0s) Rows=0.0 (0), root[root]@localhost
create table helei as select * frommysql.help_keyword
Count: 2 Time=0.11s (0s) Lock=0.03s (0s) Rows=0.0 (0), root[root]@localhost
insert into helei select * from mysql.help_keyword
Count: 2 Time=0.04s (0s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(*) from helei
最新文章
- java中如何获取昨天的当前日期
- python模块使用案例
- js封装的类似java StringBuilder类
- SSM-网站前台博客系统制作（2）---完善版Google的Kaptcha
- 决策树decision tree原理介绍_python sklearn建模_乳腺癌细胞分类器（推荐AAA）
- Spring Aware接口
- NET Core小细节杂记
- Week 1 工程表格
- rocketmq总结(消息的顺序、重复、事务、消费模式)
- js－ 类数组对象
- C++学习（十一）（C语言部分）之 练习
- linux 内存布局以及tlb更新的一些理解
- yarn 制作 npm 包
- winform TreeView 节点选择
- 安装ionic 以及 cordova 环境配置详细过程,(错误解决)
- jQuery实现全选、不选和反选功能
- 【 C 】高级字符串查找之查找标记（token）函数 strtok介绍
- abp项目如何按业务功能模块横向分割？
- Knowledge Point 20180305 数据在计算机中的表示
- PLSQL中scott账户登录不上，报错ORA-01017: invalid username/password; logon denied