slow log可以查看系统的sql的执行时间 以及详细的sql，当分析系统性能的时候可以用来参考。
我用到的是这些：
+---------------------------+---------------------------------------------------------+
| Variable_name | Value |
+---------------------------+---------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\mysql-5.6.16\data\Data-SERVER01-slow.log |
+---------------------------+---------------------------------------------------------+
5 rows in set (0.00 sec)
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
打开slow log
下面是转载的，更详细一些可以留作参考。
show variableslike '%log_output%';-- 默认是FILE
show variableslike '%query%';
-- log_output 默认是FILE，表示慢查询日志输入至日志文件，可以通过set修改输出为TABLE
-- log_queries_not_using_indexes 默认是OFF，表示是否记录没有使用索引的查询
-- slow_query_log 默认是OFF，表示是否打开慢查询
-- long_query_time默认是 10.000000，表示记录超过时间的慢查询
-- 打开慢查询，记录查询时间超过5秒的慢查询，讲慢查询结果输出至slow_log表中
set global slow_query_log = ON;
SET GLOBAL long_query_time = 5;-- 10.000000
-- SET GLOBAL log_queries_not_using_indexes = ON;-- 是否打开看个人需要
set global log_output='TABLE';-- FILE
select * from mysql.slow_log order by start_time desc;
以下代码恢复慢查询的参数设置情况
SET global slow_query_log = OFF;
SET GLOBAL long_query_time = 10.000000;
SET GLOBAL log_queries_not_using_indexes = OFF;
SET global log_output='FILE';-- FILE
-- 清空慢查询日志
show variables like '%slow%';
+---------------------+------------------------------------------+
| Variable_name |Value |
+---------------------+------------------------------------------+
| log_slow_queries| ON |
| slow_launch_time| 2 |
| slow_query_log |ON |
|slow_query_log_file | /data/mysql/slow_queries_3306.log |
+---------------------+------------------------------------------+
4 rows in set(0.00 sec)
show variables like 'log_output%';
log_output FILE
-- 关闭日志输出
set global slow_query_log=0;
show variables like '%slow%';
+---------------------+------------------------------------------+
| Variable_name |Value |
+---------------------+------------------------------------------+
| log_slow_queries| OFF |
| slow_launch_time| 2 |
| slow_query_log |OFF |
|slow_query_log_file | /data/mysql/slow_queries_3306.log |
+---------------------+------------------------------------------+
4 rows in set(0.00 sec)
set global slow_query_log_file='/data/mysql/new-slow.log';
Query OK, 0 rowsaffected (0.03 sec)
set global slow_query_log=1;
Query OK, 0 rowsaffected (0.01 sec)
show variables like '%slow%';
+---------------------+----------------------------------------------+
| Variable_name |Value |
+---------------------+----------------------------------------------+
| log_slow_queries| ON |
| slow_launch_time| 2 |
| slow_query_log |ON |
|slow_query_log_file | /data/mysql/new_slow.log |
+---------------------+----------------------------------------------+
4 rows in set(0.00 sec)
show variableslike '%log_output%';-- 目前是FILE
mysql> selectsleep(10) as a, 1 as b;
+---+---+
| a | b |
+---+---+
| 0 | 1 |
+---+---+
1 row in set(10.00 sec)
[mysql@xxx-xxx ~]$more /data/mysql/new-slow.log
6, backup the oldbig slow log file to other directory.
-- 清空slow_log表
select * from mysql.slow_log;
SET GLOBALslow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
`user_host`mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time`time NOT NULL,
`rows_sent`int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db`varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id`int(11) NOT NULL,
`server_id`int(10) unsigned NOT NULL,
`sql_text`mediumtext NOT NULL
) ENGINE=CSVDEFAULT CHARSET=utf8 COMMENT='Slow log';
SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;
没有评论