分享MySQL生产库内存异常增高的排查过程

目录
  • 修改performance_schema
  • 打开内存监控
  • 查找内存消耗
    • 统计事件消耗内存
    • 统计线程消耗内存
    • 定位具体SQL

    近期频繁收到一个MySQL实例的内存使用率高的报警,今天我们花时间排查一下问题出在哪里。

修改performance_schema

因为公司生产环境使用的阿里云RDS,修改参数相对方便,performance_schema默认为0,此次修改为1。修改之后提交参数,数据库会进行重启,建议在业务低峰进行。

打开内存监控

登录MySQL数据库,执行如下SQL,打开内存监控。

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

打开之后验证一下。

select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 5;

**注意:**该命令是在线打开内存统计,所以只会统计打开后新增的内存对象,打开前的内存对象不会统计,建议您打开后等待一段时间再执行后续步骤,便于找出内存使用高的线程。

查找内存消耗

统计事件消耗内存

select event_name,
       SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_global_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
LIMIT 10;
+---------------------------------------+-------------------------------------+
| event_name                            | SUM_NUMBER_OF_BYTES_ALLOC           |
+---------------------------------------+-------------------------------------+
| memory/sql/Filesort_buffer::sort_keys | 763523904056                        |
| memory/memory/HP_PTRS                 | 118017336096                        |
| memory/sql/thd::main_mem_root         | 114026214600                        |
| memory/mysys/IO_CACHE                 | 59723548888                         |
| memory/sql/QUICK_RANGE_SELECT::alloc  | 14381459680                         |
| memory/sql/test_quick_select          | 12859304736                         |
| memory/innodb/mem0mem                 | 7607681148                          |
| memory/sql/String::value              | 1405409537                          |
| memory/sql/TABLE                      | 1117918354                          |
| memory/innodb/btr0sea                 | 984013872                           |
+---------------------------------------+-------------------------------------+

可以看到内存消耗最高的event是Filesort_buffer,根据经验,这个应该是排序有关。

统计线程消耗内存

select thread_id,
       event_name,
       SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
limit 10;
+---------------------+---------------------------------------+-------------------------------------+
| thread_id           | event_name                            | SUM_NUMBER_OF_BYTES_ALLOC           |
+---------------------+---------------------------------------+-------------------------------------+
| 105                 | memory/memory/HP_PTRS                 | 69680198792                         |
| 183                 | memory/sql/Filesort_buffer::sort_keys | 49210098808                         |
| 154                 | memory/sql/Filesort_buffer::sort_keys | 43304339072                         |
| 217                 | memory/sql/Filesort_buffer::sort_keys | 37752275360                         |
| 2773                | memory/sql/Filesort_buffer::sort_keys | 31460644712                         |
| 218                 | memory/sql/Filesort_buffer::sort_keys | 31128994280                         |
| 2331                | memory/sql/Filesort_buffer::sort_keys | 28763981248                         |
| 106                 | memory/memory/HP_PTRS                 | 27938197584                         |
| 191                 | memory/sql/Filesort_buffer::sort_keys | 27701610224                         |
| 179                 | memory/sql/Filesort_buffer::sort_keys | 25624723968                         |
+---------------------+---------------------------------------+-------------------------------------+

可以看到内存消耗多的线程都跟Filesort_buffer相关。

定位具体SQL

根据前边我们查到的thread_id去日志里查找对应的SQL,阿里云RDS审计日志相对还是比较强大的。我们直接根据thread_id直接检索。

    我们在日志里看到大量这样的SQL,扫描行数在几千到几万不等。虽然每次查询时间并不长,大概在几十到几百毫秒,但是并发量很大。
    跟开发同学核实之后,这个查询没有做分页,取到的数据有很多行,而且最后要做排序,并且排序字段并没有合适的索引。到此,这次内存使用率出现异常的罪魁祸首已经找到。

到此这篇关于分享MySQL生产库内存异常增高的排查过程的文章就介绍到这了,更多相关MySQL生产库内存异常增高内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL内存使用的查看方式详解

    前言 本文主要给大家介绍了关于MySQL内存使用查看的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 使用版本:MySQL 5.7 官方文档 在performance_schema有如下表记录内存使用情况 mysql> show tables like '%memory%summary%'; +-------------------------------------------------+ | Tables_in_performance_schema (%memor

  • 详解分析MySQL8.0的内存消耗

    在MySQL8.0在启动的时候,会配置各种各样的buffer和cache来提高数据库的性能.如果我们在一台服务器上配置了MySQL8.0的服务,那么这台服务器的内存会同时被操作系统.MySQL8.0服务.以及其他应用程序所共享. 生产环境中,经常会遇到内存的报警,在处理这些报警之前,你需要知道MySQL本身消耗内存最多的点在哪里,这样才能比较直观的判断出来你的MySQL服务占用的内存有多少,以及如何降低MySQL本身的内存消耗. 在MySQL配置文件中,最常用的两个内存相关的参数是innodb_

  • MySQL常见内存不足启动失败的完美解决方法

    1.启动MySQL时一直不成功,查看错误日志 /var/log/mysql/error.log 2.主要的错误信息有如下几条: [ERROR] InnoDB: mmap(136151040 bytes) failed; errno 12 [ERROR] InnoDB: Cannot allocate memory for the buffer pool [ERROR] InnoDB: Plugin initialization aborted with error Generic error [

  • Mysql5.6启动内存占用过高解决方案

    vps的内存为512M,安装好nginx,php等启动起来,mysql死活启动不起来看了日志只看到对应pid被结束了,后跟踪看发现是内存不足被killed; 调整my.cnf 参数,重新配置(系统默认配置太高直接占用400M内存,小玩家玩不起呢)即可 performance_schema_max_table_instances=200 table_definition_cache=200 table_open_cache=128 下面附一个相关的my.cnf配置文件的说明 [client] po

  • MySQL占用内存较大与CPU过高测试与解决办法

    更改后如下: innodb_buffer_pool_size=576M ->256M InnoDB引擎缓冲区占了大头,首要就是拿它开刀 query_cache_size=100M ->16M 查询缓存 tmp_table_size=102M ->64M 临时表大小 key_buffer_size=256m ->32M 重启mysql服务后,虚拟内存降到200以下. 另外mysql安装目录下有几个文件:my-huge.ini .my-large.ini.my-medium.ini..

  • MySQL OOM(内存溢出)的解决思路

    OOM全称"Out Of Memory",即内存溢出. 内存溢出已经是软件开发历史上存在了近40年的"老大难"问题.在操作系统上运行各种软件时,软件所需申请的内存远远超出了物理内存所承受的大小,就叫内存溢出. 内存溢出产生原因多种多样,当内存严重不足时,内核有两种选择: 直接panic 杀掉部分进程,释放一些内核. 大部分情况下,会杀掉导致OOM的进程,然后系统恢复.通常我们会添加对内存的监控报警,例如:当memory或swap使用超过90%时,触发报警通知,需要及

  • MySQL 内存表和临时表的用法详解

    内存表: session 1 $ mysql -uroot root@(none) 10:05:06>use test Database changed root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) root@test 10:08:46>insert into tmp_memory values (1); Query OK,

  • 详解MySQL InnoDB存储引擎的内存管理

    存储引擎之内存管理 在InnoDB存储引擎中,数据库中的缓冲池是通过LRU(Latest Recent Used,最近最少使用)算法来进行管理的,即最频繁使用的页在LRU列表的最前段,而最少使用的页在LRU列表的尾端,当缓冲池不能存放新读取到的页时,首先释放LRU列表尾端的页. 上面的图中,我使用8个数据页来表示队列,具体作用,先卖个关子.在InnoDB存储引擎中,缓冲池中页的默认大小是16KB,LRU列表中有一个midpoint的位置,新读取到的数据页并不是直接放入到LRU列表的首部,而是放入

  • 分享MySQL生产库内存异常增高的排查过程

    目录 修改performance_schema 打开内存监控 查找内存消耗 统计事件消耗内存 统计线程消耗内存 定位具体SQL     近期频繁收到一个MySQL实例的内存使用率高的报警,今天我们花时间排查一下问题出在哪里. 修改performance_schema 因为公司生产环境使用的阿里云RDS,修改参数相对方便,performance_schema默认为0,此次修改为1.修改之后提交参数,数据库会进行重启,建议在业务低峰进行. 打开内存监控 登录MySQL数据库,执行如下SQL,打开内存

  • 分享MySQL 主从延迟与读写分离的七种解决方案

    目录 一.强制走主库 二.从库延迟查询 三.判断主从是否延迟?决定选主库还是从库 1.针对这个问题,有什么解决方案? 四.从库节点判断主库位点 五.比较 GTID 六.引入缓存中间件 七.数据分片 1.转换到数据库方面 前言: 我们都知道互联网数据有个特性,大部分场景都是 读多写少,比如:微博.微信.淘宝电商,按照 二八原则,读流量占比甚至能达到 90%. 结合这个特性,我们对底层的数据库架构也会做相应调整.采用 读写分离. 处理过程: 客户端会集成 SDK,每次执行 SQL 时,会判断是 写

  • 生产环境MySQL索引时效的排查过程

    早上收到开发同学求助,有个SQL查询耗时特别长,看了执行计划发现没有走索引,但是不知道原因在哪里,我们一起来分析一下. mysql>explain SELECT * FROM artisan_income WHERE parent_id IN ( 222645481, 222583953, 222181775, 222180931, 222081126, 221678753, 221616102, 221591783, 221219312, 221195482, 221118672, 22076

  • MySQL索引的一些常见面试题大全(2022年)

    目录 为什么要建立索引? 哪些情况适合建立索引? 那么哪些情况下适合建索引? 哪些情况下不适合建索引? 为什么索引是使用B+树?(重点) 索引分为那几类? 什么是聚簇索引?(重点) 使用聚簇索引的优缺点?(知道) 为什么推荐使用自增主键作为索引?(知道) 什么叫回表?(重点) 什么叫索引覆盖?(重点) 什么是最左前缀原则?(重点) MySQL索引失效的几种情况(重点) 常见的索引优化手段有哪些? 谈一下你对MySQL索引的理解? 总结 为什么要建立索引? 当在非常大的表中进行查询,如果数据库进行

  • Mysql半同步复制原理及问题排查

    mysql半同步复制和异步复制的差别如上述架构图所示:在mysql异步复制的情况下,Mysql Master Server将自己的Binary Log通过复制线程传输出去以后,Mysql Master Sever就自动返回数据给客户端,而不管slave上是否接受到了这个二进制日志.在半同步复制的架构下,当master在将自己binlog发给slave上的时候,要确保slave已经接受到了这个二进制日志以后,才会返回数据给客户端.对比两种架构:异步复制对于用户来说,可以确保得到快速的响应结构,但是

  • MySQL主从复制问题总结及排查过程

    目录 一.概述 二.mysql主从复制原理 1.MYSQL主从复制过程 三.问题及解决方法 1.show slave status \G 显示如下报错信息 2.根据提示信息定位报错位置 四.通用解决方法 1. 跳过指定数量的事务 2. 跳所有错误或指定类型的错误 一.概述 mysql主从是常用的高可用架构之一,也是使用最广泛的的系统架构.在生产环境中mysql主从复制有时会出现复制错误问题.MySQL主从复制中的问题(Coordinator stopped beacause there were

  • MySQL中dd::columns表结构转table过程及应用详解

    目录 一.MySQL的dd表介绍 二.代码跟踪 三.知识应用 四.总结 一.MySQL的dd表介绍 MySQL的dd表是用来存放表结构和各种建表信息的,客户端建的表都存在mysql.table和mysql.columns表里,还有一个表mysql.column_type_elements比较特殊,用来存放SET和ENUM类型的字段集合值信息.看一下下面这张表的mysql.columns表和mysql.column_type_elements信息.为了缩短显示长度,这里只展示几个重要的值. #建表

  • mysql left join快速转inner join的过程

    在日常优化过程中,发现一个怪事情,同一个SQL出现两个完全不一样执行计划,left join 连驱动表都可以变成不一样. 对于left join,如果where条件里有被关联表过滤,left join有可能被转成inner join ,本案例中shopInfo有ShopCategory = 'LOC'过滤条件: 保证shopInfo的记录非NULL,因此left join在优化过程中可以转为inner join. 那么O和S的JOIN顺序就是可以交换的. 验证结论: 创建表: --班级表 CRE

  • 生产级K8S基础环境部署配置过程

    生产级K8S基础环境部署: 配置本地hosts文件(有多少台,配置多少台) vim /etc/hosts 192.168.1.5 k8s-master 配置yum源(有自己的yum源更好) wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/e

  • MySQL的prepare使用及遇到bug解析过程

    目录 一.问题发现 二.问题调查过程 三.问题解决方案 四.问题总结 一.问题发现 在一次开发中使用 MySQL PREPARE 以后,从 prepare 直接取 name 赋值给 lex->prepared_stmt_name 然后给 EXECUTE 用,发现有一定概率找不到 prepare stmt 的 name,于是开始动手调查问题发生的原因. SQL语句示例: CREATE TABLE t1 (a INT, b VARCHAR(10)); PREPARE dbms_sql_stmt4 F

随机推荐