当前位置:首页 > mysql > 正文内容

mysql中CPU或内存利用率过高问题

phpmianshi4年前 (2017-05-06)mysql273

CPU 利用率过高

原因

在 MySQL 使用过程中,出现 CPU 利用率过高甚至超过100%时,与数据库存在低效 SQL 或大量行锁冲突有非常大的关系,一般都是由于大量低效的 SQL 导致,出现行锁冲突的概率非常低。

风险

若 MySQL CPU 的利用率长时间处于100%,会严重影响数据库的整体性能,极端情况下可能会出现实例 HANG 住的情况。
为避免业务因 CPU 资源不足而受影响,建议提前对 CPU 利用率过高的实例进行业务优化或者升级 CPU 资源。

解决方案

MySQL CPU 利用率过高,大部分原因与低效 SQL 有关系,通过优化低效 SQL 基本可以解决大部分问题。
MySQL 慢查询时间(long_query_time)的默认值是10s,在遇到性能问题时,若发现没有慢查询,建议将其参数调成1s ,再观察业务周期内的慢查询,进而对其慢查询进行优化。若参数调整后,在其业务周期内依然未发现慢查询,而 CPU 利用率依然偏高,建议升级 CPU 的配置,进而提高数据库的整体性能。

内存利用率过高

原因

MySQL 的内存是重要的性能参数,常出现由于低效 SQL 请求以及待优化的数据库导致内存利用率过高甚至超过100%的情况。

风险

由于低效 SQL 请求以及待优化的数据库导致内存利用率升高的问题时,严重时还会触发内存 OOM 进而发生主备切换。
为避免业务因内存利用率过高而受影响,建议您提前对内存利用率过高的实例进行业务优化或者升级内存空间。

解决方案

MySQL 的内存大体可以分为 global 级的共享内存和 session 级的私有内存两部分:

  • 共享内存是实例创建时即分配的内存空间,并且是所有连接共享的。

  • 私有内存用于每个连接到 MySQL 服务器时才分配各自的缓存。
    一些特殊的 SQL 或字段类型会导致单个线程可能分配多次缓存,因此当出现 OOM 异常,都是由各个连接的私有内存造成的,通过限制数据库的连接数和优化低效 SQL,可降低内存利用率过高的风险。


共享内存

执行以下命令,查询示例的共享内存分配情况:

show variables where variable_name in ('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size');

innodb_additional_mem_pool_size | 8388608  
innodb_buffer_pool_size         | 524288000
innodb_log_buffer_size          | 67108864  
key_buffer_size                 | 16777216  
query_cache_size                | 0        

注意:5.7版本不支持 innodb_additional_mem_pool_size。


参数说明

  • innodb_buffer_pool_size
    该部分缓存是 Innodb 引擎最重要的缓存区域,是通过内存来弥补物理数据文件的重要手段,在云数据库 MySQL 上会采用实例规格配置的50% - 80%作为该部分大小(上图为1000MB * 0.5 = 500MB)。其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行 SQL 读和写的操作时,首先并不是对物理数据文件操作,而是先对 buffer_pool 进行操作,再通过 checkpoint 等机制写回数据文件。该空间的优点是可以提升数据库的性能、加快 SQL 运行速度,缺点是故障恢复速度较慢。

  • innodb_log_buffer_size
    该部分主要存放 redo log 的信息,在云数据库 MySQL 上会设置64MB的大小。InnoDB 会首先将 redo log 写在这里,然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大,因为一般情况下该部分缓存会以较快频率刷新至 redo log(Master Thread 会每秒刷新、事务提交时会刷新、其空间少于1/2时同样会刷新)。

  • innodb_additional_mem_pool_size
    该部分主要存放 InnoDB 内的一些数据结构,在云数据库 MySQL 中统一设置为8MB。通常是在 buffer_pool 中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关,表数量越大需要更大的空间。

  • key_buffer_size
    该部分是 MyISAM 表的重要缓存区域,所有实例统一为16M。该部分主要存放 MyISAM 表的键。MyISAM 表不同于 InnoDB 表,其缓存的索引缓存是放在 key_buffer 中的,而数据缓存则存储于操作系统的内存中。云数据库 MySQL 的系统是 MyISAM 引擎的,因此需给予该部分一定量的空间的。

  • query_cache_size
    该部分是对查询结果做缓存,以减少解析 SQL 和执行 SQL 的开销,在云数据库 MySQL 上关闭了该部分的缓存。主要适合于读多写少的应用场景,因为它是按照 SQL 语句的 hash 值进行缓存的,当表数据发生变化后即失效。


私有内存

执行以下命令,查询示例的 session 私有内存分配情况:

show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');

binlog_cache_size    | 32768    
join_buffer_size     | 262144    
read_buffer_size     | 262144    
read_rnd_buffer_size | 524288    
sort_buffer_size     | 524288  
tmp_table_size       | 209715200

参数说明:

  • read_buffer_size
    分别存放了对顺序扫描的缓存,当 thread 进行顺序扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

  • read_rnd_buffer_size
    分别存放了对随机扫描的缓存,当 thread 进行随机扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

  • sort_buffer_size
    需要执行 order by 和 group by 的 SQL 都会分配 sort_buffer,用于存储排序的中间结果。在排序过程中,若存储量大于 sort_buffer_size,则会在磁盘生成临时表以完成操作。

  • join_buffer_size
    MySQL 仅支持 nest loop 的 join 算法,处理逻辑是驱动表的一行和非驱动表联合查找,这时就可以将非驱动表放入 join_buffer,不需要访问拥有并发保护机制的 buffer_pool。

  • binlog_cache_size
    该区域用来缓存该 thread 的 binlog 日志,在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中,等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。

  • tmp_table_size
    不同于上面各个 session 级的 buffer,这个参数可以在控制台上修改。该参数是指用户内存临时表的大小,如果该 thread 创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张 MyISAM 临时表。


版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中performance_schema(一)配置篇

背景    performance_schema最早在MYSQL 5.5中出现,而现在5.6,5.7中performance_schema又添加了更多的监控项,统计信息也...

mysql中QueryCache优化

原理 MySQL 的 Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query 语句(当然仅限于 SELECT 类型的 Query)通过一定的 has...

MyISAM与InnoDB的区别

1、 存储结构MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYD...

mysql中长事务详解

什么是长事务运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。下面我将演示下如何开启事务及模拟长事务:#假设我们有一...

innodb中内存管理机制BufferPool

innodb中内存管理机制BufferPool

内存结构InnoDB主索引是聚簇索引,索引与数据共用表空间,对于InnoDB而言,数据就是索引,索引就是数据。InnoDB缓存机制和MyISAM缓存机制的最大区别就是在于,InnoDB不仅仅是缓存索引...

mysql中performance_schema(三) 实践篇

背景前一篇文章我们分析了performance_schema中每个表的用途,以及主要字段的含义,比较侧重于理论的介绍。这篇文章我主要从DBA的角度出发,详细介绍如何通过performance_sche...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。