论MySQL的监控和调优

时间:2023-01-22 06:59:19

懂PHP的人一般都懂MySQL这一点不假,大多数书籍里也是这样,书中前面讲PHP后面到数据库这块就会讲到MySQL的一些知识,前几年MySQL一直是PHP书籍的一部分,后来开始从国外翻译了一些专门讲述MySQL的书籍。但大多数还是不如MySQL手册里讲的精细。

目前国内也有了一些讲MySQL比较好的数据,我推荐几本大家可以看,一本是《MySQL性能调优与架构设计》,一本是《深入浅出MySQL--数据库开发、优化与管理维护》这两本是笔者确实读过的,也是国内两只比较有实力的DBA团队的经验之谈。

书大家可以买回去慢慢读,作为一篇文章篇幅有限,我只用片言片语引导性的介绍一些MySQL监控和优化方面的知识,这就算是一种普及式的讲解,深入的了解我会在文中插入一些参考阅读,深入了解大家可以在参考阅读中获取。

MySQL的监控和调试办法也是MySQL逐步发展一步步完善起来的,大体上可以被分为几个方面,从日志分析性能瓶颈,从运行时信息观察性能,从运行参数调优,对执行语句进行分析调优等。

第一种方法,慢查询日志

慢查询是MySQL自带的一种长期观测执行效率的日志系统,对于长期运营的大型网站是必须要开启的一项服务,对于那些流量极少或是一般性的企业小站而言,是没有必要的,所以我发现很多正在做外包或正在做企业网站的朋友,对这方面就不太在意,甚至可能并不清楚这东西,如果想开启慢查询,需要在my.ini或my.cnf中加入以下语句:

log-slow-queries=/data/log/www_langwan_com/mysql_slow.log
long_query_time=1

记录执行在1秒以上的慢查询,初期如果网站没有进行过任何优化,光日志一天就要几十兆并不少见。日志中记录了从被监控的时候起到目前的所有可被称为慢查询的SQL语句,不一定慢的就是SELECT,UPDATE这样的操作也有可能,有时候更新索引比查询要更慢。

如果你发现日志太大,可以先调整一下long_query_time,例如放宽到5秒,先解决一些最棘手的慢查询,逐步缩小时间,直到有一天你发现慢查询日志基本消失为止,最好是完全消失,例如以下日志:

# Time: 090909 14:04:24
# User@Host: sparty[sparty] @  [localhost]
# Query_time: 20  Lock_time: 0  Rows_sent: 10  Rows_examined: 2500284
select * from test order by views limit 10;

Query_time : 执行时间20秒已经很慢了。
Lock_time :  锁定时间0秒,如果锁的时间较长,应该找到引起锁等待的语句。
Rows_sent : 返回10行记录
Rows_examined: 一共影响了250万行记录

这四个参数每个都需要仔细看,例如最后一个影响的记录数往往可以进一步缩小,例如用时间范围进一步缩小范围。从250万行数据中返回10行这明显是大动干戈了。

select * from test where create_time > 'xxxxx' order by views limit 10;

返回两周以来的数据中最新的10条数据,也许这时候你会看到Rows_examined会被减少到2万行,甚至更少。

如果你的日志真的有几十兆,最好用工具来分析慢查询日志,可以更好的捕捉到一些通病。关于MySQL慢查询分析工具,官方自带的是mysqldumpslow,但我推荐大家使用mysqlsla这个工具来进行分析,网上有一篇文章叫《五款常用mysql slow log分析工具的比较》值得一看,地址是:http://www.javaeye.com/topic/242516,本文只是一个短小精悍的引导文,告诉大家方向,推荐一些书籍和文章地址,详细细节大家可以亲自翻阅,细节上的东西就不翻出来再讲了。这个工具与官方工具mysqldumpslow结合到一起使用即可。

第二种方法,show [full] processlist

这是我学过的最早的一条分析语句,当线上系统不正常运行的时候,可以在任何客户端下输入show processlist指令来观察当前MySQL正在运行的SQL语句。有三种情况是立即可以发现问题的,某update或select语句反复执行这可能是你的缓存没有生效,导致与数据库频繁交互,也许你会看到State这一列有大量的Locked标志,说明你的数据库由于读写冲突产生了锁,同时观察到Time一列的时间很大,这需要找到最初被锁的语句,后面的语句往往是受到最早那条语句的影响。最后一种情况出现大量的unauthenticated user,这是一个非常经典的问题,解决办法是在MySQL启动参数中增加skip-name-resolve即不启用DNS反向解析。

我只是提到了最普遍的三种现象关于show processlist的详细使用,可以通过网络中其他文章进行更细的了解。

第三种方法 explain

无论我们从慢查询日志还是从show指令当中拿到了一条具体的有效率问题的SQL语句,经验丰富并且语句并不复杂的基础上可以人为判断出问题的所在,但大多数情况下还是要依赖explain命令通过MySQL本身的优化策略来找到问题的关键所在,这条指令帮助我们分析SQL语句的执行效率,例如SQL语句是否利用到了索引,是否进行了额外的排序,是否进行了全表扫描等,实际上SQL语句并没有执行,仅仅是通过Explain对可能的执行效率进行了详细的分析,帮助程序员对SQL语句的索引等进行优化。网上对Explain的分析文章较多,但最详细的还是建议大家多看MySQL参考手册,应该描述的相对比较全面了,另外最近也发现Explain可以和其它一些命令选项配合使用,具体可以参考老王的一篇文章http://hi.baidu.com/thinkinginlamp/blog/item/eef0cd119239db17b8127b6f.html详细写了关于Explain的一些使用上的问题。不过对于Explain最直接有效的方法还是看MySQL手册。

第四种方法 profile

利用Profile可以观察MySQL占用的CPU及内存等信息,早期版本并不支持profile,所以你要确定自己的MySQL至少是5.0.37版关于Profile的详细用法笔者也推荐一篇资料大家可以查阅,地址是:http://blog.csdn.net/radkitty/archive/2009/10/04/4632289.aspx

第五种方法 调整MySQL参数和状态

1. 修改启动参数

MySQL参数一共有两种,第一种是启动参数,需要在MySQL启动之前改写my.cnf文件或添加到启动命令行中才可以生效,例如前面提到的慢查询日志,需要在启动前配置my.cnf文件。

2. 修改运行时状态及变量

MySQL在运行时通过两组数据来描述MySQL的状态,一种是VARIABLES变量集合,一种是STATUS状态集合,例如在命令行下输入如下两个命令:

mysql> show global VARIABLES like '%cache%';
mysql> show global STATUS like '%cache%';

第一行用于获取与cache有关的变量信息,例如是否开启了查询缓存,缓存有多大。第二行用户获取与cache有关的状态信息,例如缓存已经被使用了多少,还剩余多少等。

关于MySQL参数的调优的确有点复杂,一般应该是DBA来进行处理,但对于PHP程序员多了解些也无妨,因为大多数公司是没有专门的MySQL DBA的,所以这些工作还是需要我们自己来完成。

状态是无法设置的,只有变量可以,例如:

set global query_cache_size = 32 * 1024 * 1024;

我加大了 query_cache_size ,查询缓存大小。

由于工作关系我经常的需要帮公司调试各类MySQL状态,每次也是要重新查阅不少资料,因为MySQL的变量和状态值相当多,并且经常暗含一些公式,所以为了减轻每次的工作负担我写了一个软件叫MySQLMonitor,目前公司内的所有MySQL都使用这个软件进行了监控,通过这个软件可以获取对MySQL参数和状态优化的一些建议信息。就和我在现场调试差不多。

MySQLMonitor中的优化算法来源于已经向大家推荐的两本MySQL专业书籍,以及对MySQL参数手册中一些算法公式的研究,也包括一些自己的经验所得,可以从http://www.echohello.cn/获取到这个软件。