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

姜泽涛
原创 4582       2017-09-28  

作为开发人员,平时和MySQL数据库打交道时,更加关注的是如何写出满足业务需求的SQL,而作为MySQL DBA除了关心开发人员写的业务SQL语句之外,对MySQL实例的整体运行状态也要十分关注,MySQL实例就像是一部精密的“机器”,如果某个方面出现短板,则将会对这部“机器”整体运行性能造成负面影响,影响MySQL数据库运行性能的方面有很多。


本文将主要从内存方面、IO与文件句柄限制方面、以及像慢查询、Binary日志和连接数方面来展开谈谈如何对MySQL数据库进行状态监控并根据经验值进行优化。从而使MySQL这部“机器”更高效的运转。


【“工欲善其事,必先利其器” 】

如何获取MySQL状态值以便于开展后续的诊断调优工作? 

查看mysql参数的命令,在mysql客户端输入"show variables" 

如果想要查看某个具体的值,可以使用如下命令:show variables like "%具体变量%"; 

例如:查看最大连接数: show variables like '%max_connections%';如图1:

图1) 
查看mysql状态的命令,在mysql客户端输入"show status"之后将会看到如下输出;如图2:

图2)

如果想要查看某个具体的值,可以使用如下命令:show status like "%具体变量%";例如:查看历史最大连接数: show status like '%max_used_ connections%';如图3:

(图3)


【内存方面监控及优化 】

目前MySQL数据库默认使用的存储引擎为InnoDB引擎,该引擎的存储机制同Oracle很相似,因此这就意味着内存的合理设置对于使用InnoDB存储引擎的MySQL数据库影响十分巨大。 



一、查看当前数据库实例配置的内存大小 

show variables like '%innodb_buffer_pool_ size%'; 

一般来讲,在使用InnoDB引擎的MySQL数据库需要将innodb_buffer_pool_size设置为实际物理内存的1/2或2/3,原因是通过提高查询的内存命中率来减少对磁盘IO操作。

 
二、查询缓存

作为加速查询的重要手段,其大小设置的是否合理也关系到MySQL数据库的性能好坏。启用查询缓存,可以极大地减低数据库服务器的CPU使用率。 

查看查询缓存设置情况:show variables like '%query_cache%'; 

查看查询缓存使用情况:show status like 'qcache%'; 

对于某些不想使用缓存的语句,可以这样使用:


三、排序操作监控(sort opera-tions) 

根据如下公式,评估参数sort_buffer_size设置是否合理 :
Sort_merge_passes/(Sort_scan+Sort_ range),如果其值大于2的话,则说明sort_buffer_size设置偏小,需要将其调大。 


四、连接操作监控(join opera-tions) 

当监控到这两个状态值为0时,说明数据库中索引被合理的利用,当其中有一项不为0时,则说明join_buffer_size的尺寸偏小,需要将其调大。 


本文我们从MYSQL的状态值和内存方面来介绍MYSQL性能调优,下文我们会从表对象和文件方面使用监控和慢查询、Binary日志和连接数方面来介绍影响数据库性能的因素。

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

联系我们

恒 生 技 术 之 眼