Mysql数据库服务器CPU冲高问题定位及分析

一、             现象概述和初步分析

在生产系统中最近经常发现数据库CPU冲高的现象，冲高的时间不定最长情况有达到过2、30分钟的情况，甚至会较严重的影响数据库服务器的运行和稳定。每次出现这种情况的，以下的sql语句出现的概率较大：

select count(*) from ( select * from xxx) as new

根据上述现象，我们初步把测试重点放在了引起该语句的商户查询模块上，针对这一模块进行了多次压力测试，但无论是10个并发甚至更多，数据库的ＣＰＵ利用率一直很低，无法重现CPU冲高的情况。

面对这样的测试结果，我们考虑到：如果该问题与这一语句和模块相关，那么在现实生产环境中，必然有其它因素影响了这一模块的性能。经过进一步的分析，我们把关注点转向了MySQL的查询缓存方面。

 

二、             针对Mysql缓存的分析

在这里我们首先对MySQL查询缓存进行简单的解释：

从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。

可以想见，在相对简单的测试环境中，缓存的命中率是很高的，而由于在生产环境存在着对表的更新，所以往往导致查询缓存不能命中，因此当执行这条语句的时候，它所需要的结果不再缓存中就必须重新进行查询动作。

而根据监控的日志，在这些sql语句的出现问题时时也往往伴有对这些表的updata语句。所以以往我们的测试手段实际上是忽视了这个问题，因此没能将问题重现。针对这个问题我们改变了测试的方法。

 

三、             针对Mysql缓存的性能测试

首先在mysql中手动执行sql语句，并在每次执行sql语句之前执行命令:flush tables来清空缓存，这样就能够真实的反映sql语句的性能。select count(*) from (select * from xxx) as new 其平均执行的时间为18.22秒，说明这个SQL的确存在性能问题，而其等价写法select count(*) from xxx的平均执行时间仅在0.05秒左右或更低，二者相差竟然达到数百倍之多。

（注：我们的程序中SQL语句采用此种写法，是因为程序框架自动生成的标准SQL语句，为了适应一些特殊情况，所以采用这种统一写法。在大型商业应用的开发过程中，我们都是在oracle进行标准下开发，完成后再移植到mysql下面的）

 

    为了验证我们对于查询缓存影响的推论，我们将数据库中关于缓存的一项设置为0，即query_cache_size=0,那么这样在进行压力测试的时候每一个并发用户的每一次操作都不会从查询缓存中直接返回结果。以下是在1