Mysql慢查询日志以及优化

1.查询当前慢查询日志的状态

mysql> show variables like "%slow_query_log%";
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | ON                                  |
| slow_query_log_file | /www/server/data/ebs-54701-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.01 sec)

可以看到,慢查询日志的状态和默认的慢查询日志的文件(未开启请请参考2步骤开启)

[root@ebs-54701 ~]# cd /www/server/data
[root@ebs-54701 data]# ll
total 223032
-rw-r----- 1 mysql mysql        0 Dec 19 21:51 ebs-54701.err
-rw-r----- 1 mysql mysql        6 Dec 19 21:48 ebs-54701.pid
-rw-r----- 1 mysql mysql      749 Dec 19 21:55 ebs-54701-slow.log
-rw-r----- 1 mysql mysql      611 Dec 19 21:48 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Dec 19 21:48 ibdata1
-rw-r----- 1 mysql mysql 67108864 Dec 19 21:48 ib_logfile0
-rw-r----- 1 mysql mysql 67108864 Dec 10 15:12 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Dec 19 21:55 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Dec 10 14:53 mysql
-rw-r----- 1 mysql mysql      177 Dec 10 15:34 mysql-bin.000001
-rw-r----- 1 mysql mysql      903 Dec 10 15:35 mysql-bin.000002
-rw-r----- 1 mysql mysql      247 Dec 19 21:48 mysql-bin.index
drwxr-x--- 2 mysql mysql     4096 Dec 10 14:53 performance_schema
[root@ebs-54701 data]# 

可以看到在该位置生成的慢查询日志的文件ebs-54701-slow.log

2.手动开启慢查询日志

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------------+
| Variable_name                          | Value                               |
+----------------------------------------+-------------------------------------+
| binlog_rows_query_log_events           | OFF                                 |
| ft_query_expansion_limit               | 20                                  |
| have_query_cache                       | YES                                 |
| log_queries_not_using_indexes          | ON                                  |
| log_throttle_queries_not_using_indexes | 0                                   |
| long_query_time                        | 1.000000                            |
| query_alloc_block_size                 | 8192                                |
| query_cache_limit                      | 1048576                             |
| query_cache_min_res_unit               | 4096                                |
| query_cache_size                       | 33554432                            |
| query_cache_type                       | OFF                                 |
| query_cache_wlock_invalidate           | OFF                                 |
| query_prealloc_size                    | 8192                                |
| slow_query_log                         | ON                                  |
| slow_query_log_file                    | /www/server/data/ebs-54701-slow.log |
+----------------------------------------+-------------------------------------+
15 rows in set (0.01 sec)
mysql>

3.修改mysql配置文件保证永久生效

由于慢查询日志记录的信息比较多,会影响mysql的性能,所以生产环境不建议长期开启

vim /etc/my.cnf
-----------------------------------
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/slow-query.log
-----------------------------------

其他可用的配置参数

long_query_time=1           # 慢查询日志的时间定义(秒),默认为10秒,多久就算慢查询的日志
log_queries_not_using_indexes=1    # 将所有没有使用带索引的查询语句全部写到慢查询日志中

mysql命令行设置命令

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_output = file;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file = '/www/server/data/ebs-54701-slow.log';
Query OK, 0 rows affected (0.01 sec)

修改完配置重启即可生效

MySQL 慢查询日志分析

1.查看有多少慢查询

mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 12    |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

2.取出执行次数最多的前5条sql

[root@ebs-54701 /]# /home/server/mysql/bin/mysqldumpslow -s c -t 5 /www/server/data/ebs-54701-slow.log | more

Reading mysql slow query log from /www/server/data/ebs-54701-slow.log
Count: 6  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (6), root[root]@localhost
  SELECT * FROM `tp_project_img` WHERE  `project_id` = 'S' LIMIT N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=3.0 (6), root[root]@localhost
  SHOW COLUMNS FROM `tp_project_img`

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.5 (5), root[root]@localhost
  SELECT * FROM `tp_project_img` WHERE  `project_id` = 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=24.0 (24), root[root]@localhost
  SHOW COLUMNS FROM `tp_project`

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-19T13:N:N.006552Z
  # User@Host: root[root] @ localhost [N.N.N.N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use property_exchange;
  SET timestamp=N;
  SHOW COLUMNS FROM `tp_project`

3.取出耗时最长的前10条慢SQL

[root@ebs-54701 /]# /home/server/mysql/bin/mysqldumpslow -s t -t 10 /www/server/data/ebs-54701-slow.log

Reading mysql slow query log from /www/server/data/ebs-54701-slow.log
Count: 6  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (6), root[root]@localhost
  SELECT * FROM `tp_project_img` WHERE  `project_id` = 'S' LIMIT N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=3.0 (6), root[root]@localhost
  SHOW COLUMNS FROM `tp_project_img`

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=24.0 (24), root[root]@localhost
  SHOW COLUMNS FROM `tp_project`

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.5 (5), root[root]@localhost
  SELECT * FROM `tp_project_img` WHERE  `project_id` = 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-19T13:N:N.006552Z
  # User@Host: root[root] @ localhost [N.N.N.N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use property_exchange;
  SET timestamp=N;
  SHOW COLUMNS FROM `tp_project`

Died at /home/server/mysql/bin/mysqldumpslow line 167, <> chunk 11.

王如棋博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论