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个并发用户的情况下，关闭查询缓存的情况下，执行的情况，从性能上看1个并发用户的平均响应时间是7.454秒，具体如下：

   

Transaction Summary

 

Transactions:

Total Passed: 2,988

Total Failed: 0

Total Stopped: 0

Average Response Time

 

Transaction Name		SLA Status	Minimum	Average	Maximum		Std. Deviation		90 Percent	Pass	Fail	Stop
Action_Transaction			4.344	7.454	20.25		0.991		8.641	2,986	0	0
vuser_end_Transaction			0	0	0		0		0	1	0	0
vuser_init_Transaction			0	0	0		0		0	1	0	0
	Service Level Agreement Legend:			Pass		Fail		No Data

 

在并发测试同时对操作系统进行监控，和我们所预料到的一样，这次即使在只有1个并发用户的情况下也出现了CPU冲高的情况，同时在这种情况下磁盘的压力很大，每秒写磁盘的峰值甚至能够达到数十Ｍ，虽然这是一个查询语句，但是由于使用了中间表因此可能mysql将中间表作为临时表进行了存储，而这些数据量就是在写临时表时候产生的。

 

 

四、             针对Oracle和SqlServer的性能测试

由于根据以往我们对oracle和sqlserver的开发经验，这种写法是不存在问题的，为了更全面的分析问题，我们将存贮在mysql下的表导入到oracle10G中，使用同样的查询进行测试比对，我们发现两种sql写法的执行效果是没有明显的效率差别，其单个用户的查询时间在百分之几秒级别（大概在0.015-0.05秒之间），在sqlserver2000中，发现两种不同写法的执行时间都非常的接近并且高效（执行时间均在50毫秒左右）。这个问题在我们已经测试的三种数据库中，仅在Mysql数据库中存在。

注：此次测试的三种数据库版本分别为：

mysql5.0.48

oracle 10g

sqlserver 2000

 

五、             结论及解决建议

因此，经过上面的分析，我们基本可以判断该问题的产生是由于在缓存无法命中的情况下，Mysql的查询优化器在执行这种sql语句的时候并没有使用最优化或最聪明的执行方式，该SQL语句在MYSQL数据库中的执行效率存在问题。

我们可以换另外一种sql写法，应该能够提高效率至数百倍，有可能消除CPU冲高的现象。

 

最后经过我们向mysql厂商求证，的确innodb引擎对select count(*) from (select * from xxx) as new 结构的sql执行是存在性能问题的，而如果采用myisam引擎就不会存在性能问题。