mysql的SQL性能监控-阿里云开发者社区

开发者社区> 数据库> 正文

mysql的SQL性能监控

简介:

1、慢查询

1.1、慢查询的作用

慢查询的主要作用是将执行超过一定时间的sql语句记录到慢查询日志文件,方便找出有性能问题的sql,然后我们可以针对这些sql进行分析调优。

和慢查询相关的参数有三个:

long_query_time参数设置时间,执行时间大于该参数设置时间的sql都会被记录下来,支持小于1秒的设置,不过一般设置为1秒,主要原因时小于1秒的sql太多了,而且执行计划在大数据量情况下小于1秒的一般是没全表扫描的,而小数据量小于1秒的,即便全表扫描也问题不大,除非是执行频率非常高。

slow_query_log参数设置是否打开慢查询日志的开关。

slow_query_log_file参数设置慢查询日志文件。

1.2、慢查询的设置

两种配置方式,一种是通过命令配置,可以即时生效,但重启服务后失效。示例如下:

root@laojiang:~> mysql -uroot -proot

set global long_query_time=1;

set global slow_query_log=on;

set global slow_query_log_file='/home/root/data/data/mysql-slow_1.log';

Exit

root@laojiang:~> mysql -uroot -proot

show variables like '%_query_%';

第二种方式是修改配置文件,然后重启服务,如下:

root@laojiang:~/etc> vi mysql.ini

[mysqld]

long_query_time=1

slow_query_log=ON

slow_query_log_file=/home/root/data/data/mysql-slow_2.log

root@laojiang:~/etc> service mysql stop

root@laojiang:~/etc> service mysql start



1.3、慢查询示例

进入命令窗口执行查询语句:

root@laojiang:~> mysql -uroot -proot

mysql> select count(*) from performance.order_line where ol_dist_info like '%abcdefg%' or ol_dist_info like '%hijk%';

查看慢查询日志文件:

root@laojiang:~/data/data> tail -f mysql-slow_2.log



1.4、系统慢查询状态检查

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name    | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |

| Slow_queries        | 4148 |

+---------------------+-------+

打开慢查询日志可能会对系统性能有一点点影响,如果MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响又小。



1.5、mysqldumpslow命令

慢查询日志有可能会有很多重复的sql语句,我们如何过滤呢?Mysql有自带的命令mysqldumpslow可进行查询,例下列命令可以查出访问次数最多的20个sql语句

mysqldumpslow -s c -t 20 host-slow.log

2、当前系统正在执行的sql

show processlist以及show full processlist可以查看mysql当前正在执行的sql语句,以便找到当前消耗资源的sql。第2个命令可以查看完整的正在执行的sql语句。

3、sql执行各阶段所花费的时间

通过mysql自带profiling(性能分析)工具可以诊断某个sql各个执行阶段消耗的时间,每个执行阶段在cpu  disk io等方面的消耗情况。

mysql> show variables like '%profiling%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| have_profiling         | YES   |

| profiling              | OFF   |

| profiling_history_size | 15    |

+------------------------+-------+



mysql> set global profiling=1;



mysql> show variables like '%profiling%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| have_profiling         | YES   |

| profiling              | ON    |

| profiling_history_size | 15    |

+------------------------+-------+

mysql> select count(*) from test_info;

+----------+

| count(*) |

+----------+

| 10000000 |

+----------+ 

mysql> show  profiles;

+----------+------------+--------------------------------------+

| Query_ID | Duration   | Query                                |

+----------+------------+--------------------------------------+

|        1 | 0.00014900 | 1                                    |

|        2 | 0.00007725 | set global profiling=1               |

|        3 | 0.00059175 | show variables like '%profiling%'    |

|        4 | 2.83227700 | select count(*) from test_info |

+----------+------------+--------------------------------------+ 

mysql> show profile for query 4;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000067 |

| checking permissions | 0.000007 |

| Opening tables       | 0.000021 |

| init                 | 0.000017 |

| System lock          | 0.000008 |

| optimizing           | 0.000007 |

| statistics           | 0.000016 |

| preparing            | 0.000015 |

| executing            | 0.000003 |

| Sending data         | 2.831913 |

| end                  | 0.000015 |

| query end            | 0.000009 |

| closing tables       | 0.000015 |

| freeing items        | 0.000141 |

| cleaning up          | 0.000024 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

4、sql执行状态

这些状态在show processlist和show profile for query n中会出现,所谓sql执行状态,就是只sql执行的步骤,可以查到当前sql正在做什么,这个在oracle中对应就是sql的等待事件。




     本文转自aaron428 51CTO博客,原文链接http://blog.51cto.com/aaronsa/1728263:,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章