全部产品
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
云数据库 RDS 版

利用CloudDBA解决MySQL实例CPU使用率过高的问题

更新时间:2018-01-15 16:31:18

说明:本文暂不适用于MySQL 5.7版本的实例。关于MySQL 5.7实例的CPU使用率过高的解决方案,请参见MySQL CPU使用率高的原因和解决方法

在使用MySQL数据库的过程中,经常会因CPU使用率过高而导致系统异常,如响应变慢、无法获取连接、出现报错等。在CPU使用率过高的场景中,有95%以上都是由异常SQL所致。另外,大量行锁冲突、行锁等待或后台任务也有可能导致实例CPU使用率过高,但这些状况出现的概率非常低,本文不做讨论。本文将介绍如何使用RDS的CloudDBA功能来定位系统中的慢SQL和其它异常SQL语句,然后您可通过CloudDBA提供的建议优化这些SQL语句来降低实例的CPU使用率以提升系统效率。

导致CPU使用率较高的原因

数据库中的数据以数据块为基本操作单位,一个MySQL数据块是8KB,一次逻辑读或物理读对应一个数据块的读取操作。

当数据库执行业务查询语句(包括数据修改操作)时,CPU会先从内存中请求数据块。如果内存中有对应的数据,CPU执行计算任务后会将结果返回给用户。如果内存中没有对应的数据,系统会触发从磁盘读取数据的操作。这两个数据获取过程分别是逻辑读和物理读,如下图所示。

当业务提交的SQL语句不够优化时,就会对数据库的性能产生如下影响:

  • 使数据库产生大量的逻辑读,从而导致CPU使用率过高。

  • 使数据库产生大量的物理读,从而导致IOPS和I/O延时过高。

解决方法

CloudDBA提供了如下两种SQL诊断功能:

  • 诊断慢SQL:诊断当前实例最近1个月内的慢SQL,并给出慢SQL的优化建议。

  • SQL统计:利用云数据库SQL审计数据,从多个维度分析SQL语句并给出慢SQL的优化建议。

所以,您可以通过如下两种方法来排查和优化导致CPU使用率过高的异常SQL语句。

前提条件

实例是公共云华北1、华北2、华东1、华东2、华南1地域的MySQL 5.5或MySQL 5.6版本的实例。

使用SQL诊断功能排查异常SQL语句

  1. 登录RDS管理控制台

  2. 选择目标实例所在地域。

  3. 单击目标实例ID,进入基本信息页面。

  4. 在左侧导航栏中,选择CloudDBA > 问题诊断,进入问题诊断页面。

  5. 选择慢SQL标签页。

  6. 选择要查询的时间,然后单击确定

    说明:目前,系统只支持显示最近1个月内的慢SQL数据。

  7. 若实例中有慢SQL,图示中会以柱形图的方式显示慢SQL产生的时间点和个数。单击柱形图,下方的列表就会显示其对应的所有慢SQL信息,且柱形图会变成红色。

    慢SQL详情

  8. 分析慢SQL,重点关注表格中返回行数扫描行数的值。如下图中慢SQL,每条SQL语句都有很多扫描行数但返回行数都为0,说明系统产生了大量的逻辑读和物理读。产生物理读是因为内存大小有限,不可能缓存所有数据,当有大量数据请求时必然会产生大量物理I/O请求。大量的逻辑读会占用大量的CPU资源,导致CPU使用率上涨。

    分析慢SQL

  9. 单击SQL语句栏中的SQL语句,查看该SQL语句的详情。

  10. 单击SQL优化建议,即可查看CloudDBA给该SQL语句提出的优化建议。

    SQL优化建议

    从上图的分析结果可以看出,该SQL语句执行时缺少对应的索引,导致执行该语句时要全表扫描。另外,根据MySQL的数据更新机制,每次执行该语句时整个表格都会被锁,进而使问题更加严重。凡是执行该语句的session都会出现排队等待的状况,单次执行成本又极高,所以就很容易导致CPU使用率较高甚至达到100%的状况。

  11. 根据优化建议优化异常SQL语句,CPU使用率过高的问题就会随之而解。

使用SQL统计功能排查异常SQL语句

若使用SQL统计功能排查异常SQL语句,实例必须先要开通SQL审计,详情请参见SQL审计

说明:SQL审计需另计费,为节约成本,您可以在查看数据库SQL语句前开通SQL审计,然后待问题排查完毕后再关闭该功能。

  1. 登录RDS管理控制台

  2. 选择目标实例所在地域。

  3. 单击目标实例ID,进入基本信息页面。

  4. 在左侧导航栏中,选择CloudDBA > SQL统计,进入SQL统计页面。

  5. 选择CPU,并选择要进行数据分析的时间范围,然后单击确定,状态图中即会显示当前实例的CPU在指定时间段内的使用率状况。

    注意:您最多只能选择1天的时间段。

    查看CPU使用率

  6. 选择获取审计日志的起始时间(需在步骤5中所选择的时间范围内)以及时长,然后单击获取审计日志

    获取审计日志.png

  7. 分析任务创建成功后,页面列表中会显示分析进度。

    日志分析状态

  8. 分析任务完成后,找到目标分析记录,并单击SQL分析栏下的查看,进入SQL分析详情页面。

    查看SQL分析详情

  9. 选择分析维度(执行次数、执行时间、扫描行数、返回行数、更新行数),并从不同维度分析SQL语句。

    • 执行次数维度

      在执行次数维度下,默认根据总执行次数倒序排序SQL语句,以定位执行量较大或者执行量有异常变化的语句,然后分析语句执行量的合理性并根据CloudDBA给出的建议优化SQL语句。

      如果语句执行量相对比较合理,而且执行量大的SQL也是最优的,那么建议您升级实例规格,或者使用读写分离功能来分担执行量较大的SQL语句。您也可以通过优化程序来解决数据库的查询问题,例如采用Redis的缓存系统来缓存量大的最优SQL语句。缓存的成本相对较低,且响应速度更快,能够极大地优化系统的整体性能。

      分析示例:

      执行次数维度

      上图中,通过对比平均执行时间最大执行时间,可以判断出执行次数最多的前两个SQL语句的平均执行时间很短,说明这两个SQL语句性能没有问题,您可以通过升级实例规格或开通读写分离功能来解决主库CPU压力大的问题。但这两个SQL语句的最大执行时间却很大,达到了30ms以上,说明整个系统存在波动,需要通过其它维度找出其它异常SQL语句。

    • 执行时间维度

      该维度默认按照执行时间倒序排列SQL语句,可以协助您筛选出执行总量不大但单次执行时间却很长的SQL语句。每次执行这类语句时,都会极大影响系统的响应时间和CPU使用率,当被大量执行时,CPU使用率就会较高甚至达到100%,系统卡死。

      分析示例:

      执行时间维度

      上图中,第二条SQL语句总共执行了100次,执行次数占比为0,但总执行时间占比却是12%,且单次最大执行时间约为300秒,说明了该语句的性能极差。单击该语句,通过语句详情可以看出,order by子句中既有asc又有desc排序,导致无法高效使用SQL排序,而where条件的过滤性很低,所以该SQL语句的性能很差。另外,由where条件过滤性能较低导致的SQL语句性能问题,还可以通过扫描行数维度排查。

    • 扫描行数维度

      该维度默认按照扫描行数倒序排列SQL语句,可以协助您筛选出每次执行都会查询很多条记录的SQL语句,每次执行这类语句就会产生大量的逻辑读甚至物理读。针对这类SQL语句,如果语句已经足够优化,建议您优化程序,在where条件中传入更多的筛选条件,从而可以避免SQL语句单次查询时扫描很多行。

      分析示例1:

      扫描行数示例1

      与执行时间维度相比,从扫描行数维度查看SQL语句性能时能筛选出更多异常SQL。例如,上图中执行次数为58次的语句占总执行时间的比例仅为0.63%,所以在执行时间维度的查询结果中很难搜索到该异常SQL。单击该SQL语句并查看CloudDBA给出的优化建议,如下图所示。

      扫描行数优化建议

      根据CloudDBA的分析可知,该SQL语句的写法存在很大的性能问题。因为数据库已经有了本条语句所需要的索引,所以SQL语句中不需要额外创建索引来优化性能。此类写法导致无法使用正确的索引,所以执行该类SQL时会出现性能问题。根据CloudDBA给出的建议,针对此类异常SQL,您只需要修改SQL语句的写法即可降低CPU使用率,从而优化系统性能。

      分析示例2:

      扫描行数示例2

      此外,扫描行数维度有时还能找出有性能隐患的SQL语句。例如,对比上图中第一条和第四条SQL语句,它们的执行次数占比分别是40.99%和47.93%,都占系统运行量的很大比重。同时,第一条和第四条语句的平均执行时间分别是1.35毫秒和0.19毫秒,虽然有些差距但性能都还不错。但若对比二者扫描行数和执行次数的比值,即平均单次扫描的行数,可看出单次执行第一条SQL时会扫描约500行数据,单次执行第二条SQL时会扫描约15行数据,说明第一条语句单次扫描行数过多,可能有异常。第一条SQL语句的执行次数占比很大,如果能将该语句的单次扫描行数从500行优化到10行以内,可以极大地提升系统的性能。

    • 返回行数维度

      该维度默认按照返回行数倒序排列SQL语句,会展示出单次执行返回很多行的SQL语句,一般这类SQL语句常见于导出数据的场景。其实,非正常业务的这类SQL语句破坏力很大,当大量返回数据库的数据时很容易撑爆程序的内存,会严重破坏业系统整体的稳定性。

      分析示例:

      返回行数维度

      上图中,执行次数最多的SQL语句总的返回行数也较多是正常现象。但从第二条语句开始,语句的总执行量很小,但返回行数却很多,都需要优化。例如,第三条语句,每次执行该语句时都会均等地返回1000条数据,业务场景应该是获取TOP 1000的数据的需求或某种数据导出需求。 除了优化该SQL语句外,还建议您采用缓存、利用只读实例、给应用程序做开关限流排队等优化手段降低数据库CPU的压力。

    • 更新行数维度

      该维度默认按照更新行数倒序排列SQL语句,可协助您找出单次执行写SQL语句时所影响的行数。另外,写SQL语句还会影响到锁、事务、连接数等问题,所以有性能问题的写SQL语句对数据库的破坏力极大,很容易导致系统卡住、大量锁争用甚至死锁、有很多执行和排队的连接等,最终使系统无法响应业务请求。

      分析示例:

      更新行数维度

      上图中,更新行数和执行次数的比例基本为1:1,即平均执行一次写SQL时会更新一条记录,理论上性能应该很好。但根据最大执行时间可以看出,大部分写SQL的最大执行时间都超过了10ms,这说明整个数据库有很多异常时刻,极大地影响了更新性能。根据最小执行时间可以看出,大部分写SQL的性能都很好,但最后一条语句的性能极差,每次执行时都会影响系统的稳定性,例如CPU的使用率会出现一个尖刺,所以需要优化该语句。

      单击最后一条SQL语句,并查看其SQL优化建议,系统会返回如下结果。经CloudDBA分析,该语句的写法和索引都没有问题,但由于存在where条件的字段隐式转换,使update语句不能正确使用索引,导致每次执行该语句时都是全表扫描并且锁全表,这就是该语句性能较差的原因。所以,只需要在where条件中显示转换sid字段类型即可优化该SQL语句。

      写SQL的优化建议

本文导读目录