Write by lyc at 2018-04-16

一、MySQL慢查询日志

MySQL slow log参数

MySQL日志（一）：slow query log

以下参数都是动态参数，可以在实例运行时修改。

1
2
3
4
5
6
7
8
9
10
11
12
$ cat my.cnf
[mysqld]
slow_query_log=1                        # 是否启用慢查询日志，1为启用，0为禁用
slow_query_log_file=slow.log            # 指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datadir目录
long_query_time=1                       # SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来
#min_examined_row_limit=100             # SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值
#log_queries_not_using_indexes=1        # 将没有使用索引的语句记录到慢查询日志
#log_throttle_queries_not_using_indexes=10     # 设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log-slow-admin-statements=1             # 记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
log_slow_slave_statements=1             # 复制产生的slow log是否也要记录。记录从库上执行的慢查询语句
log_timestamps=system                   # 5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区
log_output=FILE                         # 指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中

二、查看慢查询

1.慢查询日志状态

1
2
3
4
5
6
7
8
mysql> show global variables like "%slow_query%";
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |     # 慢查询日志记录功能：ON开启；OFF关闭
| slow_query_log_file | /data/mysql_log/slow_log_3306.log |     # 慢查询日志默认存放路径
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

2.慢查询时间

1
2
3
4
5
6
7
mysql> show global variables like "%long_query%";    
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |      # 查看MySQL慢查询时间设置，默认是10秒
+-----------------+----------+
1 row in set (0.00 sec)

3.查看是否记录未使用索引的查询语句

1
2
3
4
5
6
7
8
mysql> show global variables like "%using_indexes%";
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | ON    |      # ON：记录未使用索引的慢查询语句
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
2 rows in set (0.00 sec)

三、开启慢查询

1.MySQL控制台开启慢查询

临时生效，mysqld重启后失效

1
2
3
4
set global slow_query_log=ON;		                                # 开启慢查询功能
set global slow_query_log_file='/data/mysql_log/slow_log_3306.log';	# 设置慢查询日志存放路径
set global long_query_time=1;	                            	    # 设置记录慢超过1秒的SQL语句
set global log_queries_not_using_indexes=ON;		                # 记录未使用索引的查询

2.my.cnf开启慢查询

1
2
3
4
5
[mysqld]
slow-query-log = on		                            # 开启MySQL慢查询功能
slow-query-log-file = /application/mysql/slow.log	# 设置MySQL慢查询日志路径
long-query-time = 1		                            # 修改为记录1秒内的查询，默认不设置此参数为记录10秒内的查询
log-queries-not-using-indexes = on	                # 记录未使用索引的查询语句

3.测试慢查询语句记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 1.写一条查询10秒的语句
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.03 sec)

# 2.查看该查询语句被记录到慢查询日志
shell > tail -f /data/mysql_log/slow_log_3306.log
# Time: 180416 21:41:05
# User@Host: root[root] @ localhost []  Id:     4
# Query_time: 10.049585  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1523886065;
select sleep(10);

四、切割慢查询日志

1.mysqladmin flush-log切割

1
2
$ /bin/mv /data/mysql_log/slow_log_3306.log /data/mysql_log/slow_log_3306_$(date +%Y%m%d).log &&\
/usr/local/mysql/bin/mysqladmin -uroot -p -S /data/3306/mysql.sock flush-log

2.手动切割

1
2
/bin/cp /data/mysql_log/slow_log_3306.log /data/mysql_log/slow_log_3306_$(date +%Y%m%d).log
>slow.log

3.加入crontab每天切割

1
2
$ crontab -l
00 00 * * * /bin/sh /root/tdserver/cut_slow_log.sh >/dev/null 2>&1

五、MySQL数据库负载高思路

  • uptime查看服务器负载
  • 登陆MySQL数据库 show full processlist;
  • 检查慢查询日志开启状态
1
2
3
4
slow-query-log = on
slow_query_log_file=/data/mysql_log/slow_log_3306.log
long_query_time=1
# log-queries-not-using-indexes = on        # 不要开启，开启将疯狂记录slow_log到文件，造成磁盘IO压力。
  • mysqlsla分析慢查询日志
  • 优化慢查询语句