& 关于RDS实例CPU超过100%的分析
关于RDS实例CPU超过100%的分析
经常听见用户说自己的rds实例cpu超过100%，通常这种情况都是由于sql性能问题导致的,下面我用一则案例来分析：
用户实例zuowenwang反映cpu超过100%，实例偶尔出现卡住的现象；
1.原理：cpu消耗过大通常情况下都是有慢sql造成的，这里的慢sql包括全表扫描，扫描数据量过大，内存排序，磁盘排序，锁争用等待等；
2.表现现象sql执行状态为：sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，
3.解决方法：用户可以登录到rds，通过show processlist查看当前正在执行的sql，当执行完show processlist后出现大量的语句，通常其状态出现sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result, Using filesort 都是sql有性能问题；
A.sending data表示：sql正在从表中查询数据，如果查询条件没有适当的索引，则会导致sql执行时间过长；
B.Copying to tmp table on disk：出现这种状态，通常情况下是由于临时结果集太大，超过了数据库规定的临时内存大小，需要拷贝临时结果集到磁盘上，这个时候需要用户对sql进行优化；
C.Sorting result, Using filesort：出现这种状态，表示sql正在执行排序操作，排序操作都会引起较多的cpu消耗，通常的优化方法会添加适当的索引来消除排序，或者缩小排序的结果集；
通过show processlist发现如下sql：
| 2815961 | sanwenba
| 10.241.142.197:55190 | sanwenba | Query
| 0 | Sorting RESULT
SELECT z.aid,z.subject FROM www_zuowen z RIGHT JOIN www_zuowenaddviews za ON za.aid=z.aid
ORDER BY za.viewnum DESC LIMIT 10;
SELECT z.aid,z.subject FROM www_zuowen z RIGHT JOIN www_zuowenaddviews za ON za.aid=z.aid
ORDER BY za.viewnum DESC LIMIT 10;
用explain 查看执行计划：
sanwenba@3018 10:00:54&explain SELECT z.aid,z.subject FROM www_zuowen z
RIGHT JOIN www_zuowenaddviews za ON za.aid=z.aid ORDER BY za.viewnum DESC LIMIT 10;
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------
| id | select_type | TABLE | TYPE
| possible_keys | KEY
| key_len | REF
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------
1 | SIMPLE
| viewnum | 6
| 537029 | USING INDEX; USING filesort |
1 | SIMPLE
| eq_ref | PRIMARY
| PRIMARY | 3
| sanwenba.za.aid |
添加适当索引消除排序：
sanwenba@3018 10:02:33 ALTER TABLE www_zuowenaddviews ADD INDEX ind_www_zuowenaddviews_viewnum(viewnum);
sanwenba@3018 10:03:27explain SELECT z.aid,z.subject FROM www_zuowen z RIGHT JOIN www_zuowenaddviews za ON za.aid=z.aid ORDER BY za.viewnum DESC LIMIT 10;
+----+-------------+-------+--------+---------------+--------------------------------+---------+-
| id | select_type | TABLE | TYPE
| possible_keys | KEY
| key_len | REF
| ROWS | Extra
+----+-------------+-------+--------+---------------+--------------------------------+---------+-
1 | SIMPLE
| ind_www_zuowenaddviews_viewnum | 3
10 | USING INDEX |
1 | SIMPLE
| eq_ref | PRIMARY PRIMARY | 3
| sanwenba.za.aid |
+----+-------------+-------+--------+---------------+--------------------------------+---------+-
| 2825321 | netzuowen | 10.200.120.41:44172
| netzuowen | Query |
2 | Copying TO tmp TABLE ON disk
SELECT * FROM `www_article` WHERE 1=1 ORDER BY rand() LIMIT 0,30
这种sql order by rand()同样也会出现排序;
netzuowen@3018 10:23:55
SELECT * FROM `www_zuowensearch` WHERE checked = 1 ORDER BY rand() LIMIT 0,10 ;
+----+-------------+------------------+------+---------------+--------+---------+-------+------+
| id | select_type | TABLE
| TYPE | possible_keys | KEY
| key_len | REF
| ROWS | Extra|
+----+-------------+------------------+------+---------------+--------+---------+-------+------+
1 | SIMPLE
| www_zuowensearch | REF
| newest | 1
| const | 1443 | USING TEMPORARY; USING filesort |
+----+-------------+------------------+------+---------------+--------+---------+-------+------+
这种随机抽取一批记录的做法性能是很差的，表中的数据量越大，性能就越差：
解决方法如下：
http://www.piaoyi.org/php/MySQL-Order-By-Rand.html
第一种方案，即原始的 Order By Rand() 方法：
$sql=”SELECT * FROM content ORDER BY rand() LIMIT 12″;
$result=mysql_query($sql,$conn);
$rnds=”;
while($row=mysql_fetch_array($result)){
$rnds=$rnds.$n.”. &a href=’show”.$row[‘id’].”-“.strtolower(trim($row[‘title’])).”‘&”.$row[‘title’].”&/a&&br /&\n”;
3万条数据查12条随机记录，需要0.125秒，随着数据量的增大，效率越来越低。
第二种方案，改进后的 JOIN 方法：
for($n=1;$n&=12;$n++){
$sql=”SELECT * FROM `content` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2
WHERE t1.id &= t2.id ORDER BY t1.id ASC LIMIT 1″;
$result=mysql_query($sql,$conn);
$yi=mysql_fetch_array($result);
$rnds = $rnds.$n.”. &a href=’show”.$yi[‘id’].”-“.strtolower(trim($yi[‘title’])).”‘&”.$yi[‘title’].”&/a&&br /&\n”;
3万条数据查12条随机记录，需要0.004秒，效率大幅提升，比第一种方案提升了约30倍。缺点：多次select查询，IO开销大。
第三种方案，SQL语句先随机好ID序列，用 IN 查询（飘易推荐这个用法，IO开销小，速度最快）：
$sql=”SELECT MAX(id),MIN(id) FROM content”;
$result=mysql_query($sql,$conn);
$yi=mysql_fetch_array($result);
$idmax=$yi[0];
$idmin=$yi[1];
$idlist=”;
for($i=1;$i&=20;$i++){
if($i==1){ $idlist=mt_rand($idmin,$idmax); }
else{ $idlist=$idlist.’,’.mt_rand($idmin,$idmax); }
$idlist2=”id,”.$
$sql=”select * from content where id in ($idlist) order by field($idlist2) LIMIT 0,12″;
$result=mysql_query($sql,$conn);
$rnds=”;
while($row=mysql_fetch_array($result)){
$rnds=$rnds.$n.”. &a href=’show”.$row[‘id’].”-“.strtolower(trim($row[‘title’])).”‘&”.$row[‘title’].”&/a&&br /&\n”;
3万条数据查12条随机记录，需要0.001秒，效率比第二种方法又提升了4倍左右，比第一种方法提升120倍。注，这里使用了 order by field($idlist2) 是为了不排序，否则 IN 是自动会排序的。缺点：有可能遇到ID被删除的情况，所以需要多选几个ID。
C．出现sending data的情况：
| 2833185 | sanwenba
| 10.241.91.81:45964
| sanwenba | Query
1 | Sending DATA
SELECT * FROM `www_article` WHERE CONCAT(subject,description) LIKE '%??%' ORDER BY aid DESC LIMIT 75,15
SELECT * FROM `www_article` WHERE CONCAT(subject,description) like ‘%??%’ ORDER BY aid desc LIMIT 75,15
这种sql是典型的sql分页写法不规范的情况，需要将sql进行改写：
SELECT * FROM www_article t1,(SELECT aid FROM www_article WHERE CONCAT(subject,description) LIKE '%??%' ORDER BY aid DESC LIMIT 75,15) t2 WHERE t1.aid=t2.
注意这里的索引需要改用覆盖索引：aid+ subject+description
优化后的结果：
Sql优化是性能优化的最后一步，虽然位于塔顶，他最直影响用户的使用，但也是最容易优化的步骤，往往效果最直接。
RDS-mysql由于有资源的隔离，不同的实例规格拥有的iops能力不同，比如新1型提供的iops为150个，也就是每秒能够提供150次的随机磁盘io操作，所以如果用户的数据量很大，内存很小，由于iops的限制，一条慢sql就很有可能消耗掉所有的io资源，而影响其他的sql查询，对于数据库来说就是所有的sql需要执行很长的时间才能返回结果，对于应用来说就会造成整体响应的变慢；所以优化永不止境，既可以帮助你的系统稳定，同时又可以节约你的成本，何乐不为。
Categories:
Categories鍏充簬RDS瀹炰緥CPU瓒呰繃100%鐨勫垎鏋