文档中心 > 聚石塔

RDS-IOPS优化方案

更新时间:2015/09/18 访问次数:34457

IOPS优化方法
        IOPS
使用率高通常是由于慢SQL 导致,这里的慢SQL包括高并发下的全表扫描,内存排序,磁盘排序,锁争用和锁等待等。
用户可以通过show processlist查看当前正在执行的sql。当执行完show processlist后出现大量的语句,通常其状态出现sending dataCopying to tmp tableCopying to tmp table on diskSorting result, Using filesort 都是sql有性能问题在下面有具体案例分析
       1. sending data表示:sql正在从表中查询数据,如果查询条件没有适当的索引,则会导致sql执行时间过长;
       2. Copying to tmp table on disk:出现这种状态,通常情况下是由于临时结果集太大,超过了数据库规定的临时内存大小,需要拷贝临时结果集到磁盘上,这个时候需要用户对sql进行优化;
       3. Sorting result, Using filesort:出现这种状态,表示sql正在执行排序操作,排序操作都会引起较多的cpu消耗,通常的优化方法会添加适当的索引来消除排序,或者缩小排序的结果集。

4. InnoDB产生死锁会导致IOPS消耗高,当系检测到死锁,Innodb 会选择产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成;调整锁等待超时参数:innodb_lock_wait_timeout

5. 子查询导致IOPSCPU消耗高,在mysql的常见的5.05.15.5版本中都存在较大风险,使用不当则会造成严重的性能问题,建议将子查询改为关联的形式

RDS应用架构改善IOPS

1.RDS升级到5.6版本,实行读写分离,减轻数据库的性能压力。

2. 将离线分析计算的数据迁移到ODPS进行离线分析,ODPS间隔时间会逐步改短,提高分析数据的实时性

3. ECSRDS之间,部署OCS,缓存RDS的读数据,同时减轻RDS的读性能压力

 

优化案例

出现sending data 导致CPU高的情况:

| 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

性能sql

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;

注意这里的索引需要改用覆盖索引:aid+ subject+description

索引优化

 

SQL

SELECT order_id, order_sn

FROM order_info FORCE INDEX (shipping_time)

WHERE order_status = 1

       AND shipping_status = 0

       AND is_send = 0

       AND is_locked = 0

       AND shipping_time <= 1419632103

ORDER BY is_emergency DESC, update_time DESC

建议:

 

添加索引:ALTER TABLE order_info ADD INDEX IDX_IS_SH_IS_UP (IS_SEND,SHIPPING_STATUS,IS_EMERGENCY,UPDATE_TIME)

 

 

SQL

DELETE FROM task_process

WHERE task_id = '1800067'

建议:

添加索引:ALTER TABLE task_process ADD INDEX IDX_TASK_ID (TASK_ID)

 

 

SQL

SELECT id, order_sn, upload_request_flag, upload_response_flag

FROM api_wms_trade USE INDEX (lianhe)

WHERE id > 0

       AND ckdm IN ('')

       AND order_type = 'order_info'

       AND upload_response_flag = 0

       AND cancel_request_flag = 0

       AND wms_order_flow_end_flag = 0

ORDER BY id

LIMIT 100

建议:

 

添加索引:ALTER TABLE api_wms_trade ADD INDEX IDX_CK_UP_WM_OR (CKDM,UPLOAD_RESPONSE_FLAG,WMS_ORDER_FLOW_END_FLAG,ORDER_TYPE)

 

 

SQL

SELECT ld.id AS ld_id, lg.id AS lg_id

FROM lsxhdlk_goods lg

       INNER JOIN lsxhdlk lk ON lg.p_id = lk.id

       INNER JOIN lsxhd ld ON lk.p_id = ld.id

WHERE lg.is_ft = 0

       AND ld.ywrq >= 1341504000

LIMIT 50

建议:

添加索引:ALTER TABLE lsxhdlk_goods ADD INDEX IDX_IS_FT_P_ID (IS_FT,P_ID)

 

SQL

 

SQL

select 'data' as fnodepath , case when order_type = 0 then '1' when order_type = 1 then '0' end as auart , b.sddm as kunnr , b.sdmc as name1 , a.djbh as bstkd , c.brand_code as zbbrand , '---' as posnr , '---' as ean11 , '---' as lgort , '---' as kbetr , '---' as wmeng from lsxhrb a left join shangdian b on a.sd_id = b.id left join brand c on a.brand_id = c.brand_id where djbh = :1 union all select 'data\\items' as fnodepath , '---' as auart , '---' as kunnr , '---' as name1 , '---' as bstkd , '---' as zbbrand , cast ( ( @mycnt := @mycnt+1 ) as char ( 10 ) ) as posnr , d.barcode as ean11 , c.ckdm as lgort , cast ( j.zk as char ( 10 ) ) as kbetr , cast ( sum ( b.sl ) as char ( 10 ) ) as wmeng from lsxhrb a left join lsxhrbmx b on a.id = b.p_id left join cangku c on a.ck_id = c.id left join goods_barcode d on b.goods_id = d.goods_id and b.color_id = d.color_id and b.size_id = d.size_id left join goods e on b.goods_id = e.goods_id left join shangdian f on a.sd_id = f.id left join dttjdmf1 h on a.sd_id = h.sd_id left join dttjdmp j on h.djbh = j.djbh and b.goods_id = j.goods_id where a.djbh = :2 group by d.barcode , c.ckdm union all select 'data\\items' as fnodepath , '---' as auart , '---' as kunnr , '---' as name1 , '---' as bstkd , '---' as zbbrand , cast ( ( @mycnt := @mycnt+1 ) as char ( 10 ) ) as posnr , '运费' as ean11 , b.ckdm as lgort , cast ( shipping_fee as char ( 10 ) ) as kbetr , 0 as wmeng from lsxhrb a left join cangku b on a.ck_id = b.id where a.djbh = :3 and shipping_fee <> :4

建议:

【 严重】扫描行与发送行的比是359514,并且使用了聚合函数,该SQL索引过滤性不好,影响服务器性能。

 

 

SQL

select a.* , now ( ) as createtime from order_info a left join cangku b on a.ck_id = b.id , z_qyrq where ( a.order_status = :1 and shipping_status = :2 and is_send = :3 and process_status = :4 and is_separate = :5 ) and b.ckmc like :6 and a.add_time > z_qyrq.qyrq and exists ( select id from api_wms_trade where upload_response_flag in ( :7 , :8 ) and upload_request_flag = :9 and order_type = :10 and cancel_request_flag = :11 and wms_order_flow_end_flag = :12 and order_sn = a.order_sn )

建议:

【 严重】扫描行与发送行的比是3738355,需要注意like可能无法使用索引,请优化表结构和SQL语句。

 

 

SQL

select lsxhd.lylx , order_id , invoice_no , shipping_name from lsxhd , lsxhdlk where lsxhd.id = lsxhdlk.p_id and ( lsxhdlk.back_status = :1 or ( lsxhdlk.back_status = :2 and ( back_errmsg like :3 or back_errmsg like :4 or back_errmsg like :5 ) ) ) and lsxhd.sd_id = :6 order by lsxhdlk.back_status , lsxhdlk.add_time limit :7

【 严重】扫描行与发送行的比是744,需要注意like可能无法使用索引,请优化表结构和SQL语句。

建议:平均发送行1172,返回行数过多,请考虑在低峰时执行这类语句,以减少对其他查询的影响。

 

 

SQL

select order_id , order_sn from order_info force index ( shipping_time ) where order_status = :1 and shipping_status = :2 and is_send = :3 and is_locked = :4 and shipping_time <= :5 order by is_emergency desc , update_time desc

建议:【 严重】扫描行与发送行的比是2053460,并且使用了force index,该SQL索引过滤性不好,影响服务器性能,请确认force index是否合理。

【 警告】您的SQL语句锁行过多,可能堵住其他更新语句

 

 

SQL

delete from task_process where task_id = :1

建议:

【警告】您的SQL语句锁行过多,可能堵住其他更新语句

 

降低IOPS的压力,Sql优化是性能优化的关键一步,直影响用户的使用,也是最容易优化的步骤,往往效果最直接。RDS由于有资源的隔离,不同的实例规格拥有的iops能力不同,比如新1型提供的iops150个,也就是每秒能够提供150次的随机磁盘io操作,所以如果用户的数据量很大,内存很小,由于iops的限制,一条慢sql就很有可能消耗掉所有的io资源,而影响其他的sql查询,对于数据库来说就是所有的sql需要执行很长的时间才能返回结果,对于应用来说就会造成整体响应的变慢。

FAQ

关于此文档暂时还没有FAQ
返回
顶部