作为开发人员,平时和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日志和连接数方面来介绍影响数据库性能的因素。