从内存到连接数,如何有效提升MYSQL性能(二)?

刘云
原创 2427       2017-09-30  

上文我们从MYSQL的状态值和内存方面来介绍MYSQL性能调优,本文将继续从表对象、文件方面使用监控和慢查询、Binary日志和连接数等方面全面介绍影响数据库性能的因素以及优化技巧。


【表对象及文件使用方面监控】

一、检查文件打开数 


当打开文件比率超过75%时,说明open_files_limit需要调大,但该值受限于操作系统参数ulimit –u。


二、表缓存状态监控 

慢查询、Binary日志和连接数方面 

当open_tables*100/opened_tables小于85%或者当open_tables*100/table_cache大于95%时,需要增加table_cache参数大小。

三、临时表使用情况监控(tmp ta-ble) 

按照经验,当

的比率大于25时,说明有过多的内存-磁盘数据交换用于临时表的处理,需要做的处理是增加tmp_table_size 和 max_heap_table_ size的大小来缓解临时表的使用对数据库性能的影响。 


四、全表扫描的监控(table scan) 

Handler_read_rnd_next这个涉及到table scans,肯定是越小越好。根据平时的经验,当Handler_read_rnd_next/ Com_select超过4000时,意味着需要调大read_buffer_size的值来缓解全表扫描造成的mysql整理性能下降的问题。 


五、表锁等待(table locking) 

关于表锁等待的情况是发生在使用MyISAM,如果使用的是InnoDB存储引擎的话,则不需要考虑对表锁等待状态的监控。该状态只在使用MyISAM引擎时关心,涉及到的状态值如下: 

如果table_locks_immediate/table_locks_ waited的比率小于5000,则要考虑使用InnoDB引擎来替代MyISAM引擎了。


【慢查询、Binary日志和连接数 】

一、慢查询日志状态监控(slow queries) 

慢查询日志默认是不开启的,当使用log_slow_queries=on开启慢查询日志之后,需要关心的状态值Slow_queries, 

一般对于慢查询日志的时间阀值建议是小于5秒,也就是long_query_time不宜设置超过5秒。 


二、二进制日志状态监控(binary log) 

二进制日志,是MySQL Server中最为重要的日志之一,其记录所有更改数据的语句。还用于复制。 二进制日志是记录着mysql所有事件的操作,可以通过二进制日志做完整恢复,基于时间点的恢复,和基于位置的恢复。二进制日志有关的数据库参数如下: 

参数expire_logs_days用于控制mysql实例对二进制日志的保留时间控制,超期日志将会被mysql实例自动删除,同时我们也可以通过RESET MASTER 或者PURGE MASTER LOGS命令来手工删除二进制日志文件。 
sync_binlog参数是为了保证当数据库实例宕机时,日志内容不会丢失。


三、线程和连接数状态监控

每秒钟监控Threads_created,如果差值大于2的话,说明线程数在迅速增长,需要注意thread_cache_size的值是否过小,导致连接数没有合理缓存。


对于连接数的监控,使用如下语句:

根据经验,当max_used_connections*100/max_connections大于85%的时候,max_connectio ns设置偏小,需要将其调大。
当max_used_connections*100/max_ connec-tions小于15%的时候,说明max_connections设置偏大,需要将其调小。


影响MySQL数据库的运行性能的因素有很多,本文是从对MySQL数据库实例的角度来考虑优化因素,其实最关键的还是sql语句因素,如果sql语句本身写的不合规格,例如使用 like ‘%关键字%’方式做模糊查询,即便是对参数都调整过了,最终还是选择全表扫描的处理方式。


所以,站在开发人员的角度,还是要考虑如何将sql写的更加合理规范,只有在sql语句合理规范的前提下,再对数据库其他方面的参数进行综合考量和调优,才能起到如虎添翼的作用。

恒生技术之眼原创文章,未经授权禁止转载。详情见转载须知

联系我们

恒 生 技 术 之 眼