mysql 数据库内存溢出会丢失吗
发布网友
发布时间：2022-05-02 17:14
共3个回答
懂视网
时间：2022-05-02 21:35
一、从常见的报错说起
? 故事的开头我们先来看一个常见的sql报错信息：
? 相信对于这类报错大家一定遇到过很多次了，特别对于OMG这种已内容生产为主要工作核心的BG，在内容线的存储中，数据大一定是个绕不开的话题。这里的数据“大”，远不止存储空间占用多，其中也包括了单个（表）字段存储多、大，数据留存时间长，数据冗余多，冷热数据不明显导致的体量大，访问峰值随着热点变化明显，逻辑处理复杂导致数据存储压力放大等等。回到这个报错的问题上来，我们先来看一下这个表的结构：
看到这里，我相信大家会有不同的处理方式了，这里就不对各种处理方式的优劣做比较了，仅仅叙述使用频率较高的两种处理方式。
? 这两种的处理方式也各有优缺点，把字段改成text或者blob，不仅增大了数据存储的容量，对这个字段的索引页只能采用前缀或者全文索引了，如果业务侧存储的是json格式的数据，5.7支持json数据类型是个不错的选择，可以针对单个子类进行查询和输出。同样如果缩小和拆分的话就比较依赖业务的场景和逻辑需求了，业务使用的逻辑上需要修改，工程量也需要评估。
二、深入探索
? 接着我们再来深入分析下关于限制大小“65535”的一些容易混淆的概念。
1、“65535”不是单个varchar(N)中N的最大限制，而是整个表非大字段类型的字段的bytes总合。
---------------------------------------------------------------------------------------------
Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
---------------------------------------------------------------------------------------------
2、不同的字符集对字段可存储的max会有影响，例如，UTF8字符需要3个字节存储，对于VARCHAR（255）CHARACTER SET UTF8列，会占用255×3 =765的字节。故该表不能包含超过65,535/765=85这样的列。GBK是双字节的以此类推。
3、可变长度列在评估字段大小时还要考虑存储列实际长度的字节数。例如，VARCHAR（255）CHARACTER SET UTF8列需要额外的两个字节来存储值长度信息，所以该列需要多达767个字节存储，其实最大可以存储65533字节，剩余两个字节存储长度信息。
4、BLOB、TEXT、JSON列不同于varchar、char等字段，列长度信息独立于行长存储，可以达到65535字节真实存储
5、定义NULL列会降低允许的最大列数。
? row length = 1 + (sum of column lengths) + (number of NULL columns + delete_flag + 7)/8 + (number of variable-length columns)
6、对于InnoDB表，NULL和NOT NULL列存储大小是一样
7、InnoDB允许单表最多1000个列
8、varchar主键只支持不超过767个字节或者768/2=384个双字节 或者767/3=255个三字节的字段 而GBK是双字节的，UTF8是三字节的
9、不用的引擎对索引的限制有区别
三、真正的故障
? 下面来说下今天遇到的业务故障，线上业出现了大量的如下报错，导致程序无法写入数据：
按照提示和正常的思路，我们先第一反应认为业务存在如下的问题：
? 接着查看了业务的库表结构，如下：
? 很快排除了第一个原因，因为首先业务的报错不是在建立表的时候出现的，如果是表中非大字段之和65535，在建表的时候就会出错，而业务是在写入的时候才报错的，而且通过库表结构也能发现大量的都是mediumblob类型字段，非大字段加起来远小于65535。
? 接着根据业务提供的具体SQL，appversion、datadata、elt_stamp、id这几个非大字段，也并没有超过限制，mediumblob类型字段最大可存储16M，业务的数据远远没有达到这个量级。按照报错的提示把 appversion、datadata、elt_stamp、id这几个非大字段均改成blob类型，还是无法解决。（根据之前的分析，必然不是问题的根源）。
? 冷静下来后，发现其实还有个细节被忽略掉了，业务的失败率不是100%，说明还是有成功的请求，通过对比成功和失败的sql，发现果然数据量差异的还是mediumblob类型字段。那么现在第一个想到的就是，max_allowed_packet这个参数，是不是调小了，是的单个请求超过大小被拒绝了，查了下配置的值（如下图），配置的大小1G，sql的数据长度远没有这么大，这个原因也排除了。
? 查到这里基本上排除了常见几个问题，接着再看一下另一个参数的限制：innodb_page_size，这个的默认值是16K，每个page两行数据，所以每行最大8k数据。
查看了下数据表Row_format是Compact，那么我们可以推断问题的原因应该就是innodb默认的approach存储格式会把每个blob字段的前864个字节存储在page里，所以blob超过一定数量的话，单行大小就会超过8k，所以就报错了。通过对比业务写成功和失败的SQL也应征了这个推论，那么现在要怎么解决这个问题？
由于业务单表的存储条数并不大，而且业务逻辑不适合拆分，所以我们要在Row_format上来解决这个问题。
? Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种，新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中。Compressed行记录格式的另一个功能就是存储在其中的数据会以zlib的算法进行压缩。
相关的变更操作就相对简单了：
? 1、 修改MySQL全局变量：
? SET GLOBAL innodb_file_format=‘Barracuda‘;
? 2、平滑变更原表的属性：
? ROW_FORMAT=COMPRESSED
四、继续学习
? 通过这个案例我们可以从中提炼出两个值得深入研究一下的点：
1、关于innodb_page_size
? 从MySQL5.6开始，innodb_page_size可以设置Innodb数据页为8K,4K，默认为16K。这个参数在一开始初始化时就要加入my.cnf里，如果已经创建了表，再修改，启动MySQL会报错。
那么在5.6的版本之前要修改这个值，怎么办？那只能是在源码上做点文章了，然后重新rebuild一下MySQL。
? UNIV_PAGE_SIZE是数据页大小，默认的是16K，该值是可以设置必须为2的次方。对于该值可以设置成4k、8k、16k、32K、64K。同时更改了UNIV_PAGE_SIZE后需要更改UNIV_PAGE_SIZE_SHIFT 该值是2的多少次方为UNIV_PAGE_SIZE，所以设置数据页分别情况如下：
? 接着再来说一下innodb_page_size设置成不同值的对于mysql性能上的影响，测试的表含有1亿条记录，文件大小30G。
? ①读写场景（50%读50%写）
? 16K，对CPU压力较小，平均在20%
? 8K，CPU压力为30%~40%，但select吞吐量要高于16K
? ②读场景（100%读）
? 16K和8K差别不明显
? InnoDB Buffer Pool管理页面本身也有代价，Page数越多，那么相同大小下，管理链表就越长。因此当我们的数据行本身就比较长（大块插入），更大的页面更有利于提升速度，因为一个页面可以放入更多的行，每个IO写的大小更大，可以更少的IOPS写更多的数据。 当行长超过8K的时候，如果是16K的页面，就会强制转换一些字符串类型为TEXT，把字符串主体转移到扩展页中，会导致读取列需要多一个IO，更大的页面也就支持了更大的行长，64K页面可以支持近似32K的行长而不用使用扩展页。 但是如果是短小行长的随机读取和写入，则不适合使用这么大的页面，这会导致IO效率下降，大IO只能读取到小部分。
2、关于Row_format
? Innodb存储引擎保存记录，是以行的形式存放的。在InnoDB 1.0.x版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据。MySQL 5.1 中的innodb_plugin 引入了新的文件格式：Barracuda，该文件格式拥有新的两种行格式：compressed和dynamic。并且把 compact 和 redundant 合称为Antelope。可以通过命令SHOW TABLE STATUS LIKE ‘table_name‘；来查看当前表使用的行格式，其中 row_format 列表示当前所使用的行记录结构类型。
? MySQL 5.6 版本中，默认 Compact ，msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，默认值是DYNAMIC，也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC（通过这个可动态调整表的存储格式）。如果要修改现有表的行模式为compressed或dynamic，必须先将文件格式设置成Barracuda（set global innodb_file_format=Barracuda;）。再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否则修改无效却无提示。
①compact
? 如果blob列值长度 <= 768 bytes，不会发生行溢出(page overflow)，内容都在数据页(B-tree Node)；如果列值长度 > 768字节，那么前768字节依然在数据页，而剩余的则放在溢出页(off-page)，如下图：
? 上面讲的blob或变长大字段类型包括blob、text、varchar，其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于N。
②compressed或dynamic
对blob采用完全行溢出，即聚集索引记录（数据页）只保留20字节的指针，指向真实存放它的溢出段地址：
? dynamic行格式，列存储是否放到off-page页，主要取决于行大小，它会把行中最长的那一列放到off-page，直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。可以避免compact那样把太多的大列值放到 B-tree Node，因为dynamic格式认为，只要大列值有部分数据放在off-page，那把整个值放入都放入off-page更有效。
? compressed 物理结构上与dynamic类似，但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用，可以降低off-page的使用，减少存储空间（50%左右，可参见之前“【数据库评测报告】第三期：innodb、tokudb压缩性能”报告中的测试结果），但要求更高的CPU，buffer pool里面可能会同时存储数据的压缩版和非压缩版，所以也多占用部分内存。
? 最后参考了《高性能MySQL》，给出一些使用BLOB这类变长大字段类型的建议：
? ①大字段在InnoDB里可能浪费大量空间。例如，若存储字段值只是比行的要求多了一个字节，也会使用整个页面来存储剩下的字节，浪费了页面的大部分空间。同样的，如果有一个值只是稍微超过了32个页的大小，实际上就需要使用96个页面。
? ②太长的值可能使得在查询中作为WHERE条件不能使用索引，因而执行很慢。在应用WHERE条件之前，MySQL需要把所有的列读出来，所以可能导致MySQL要求InnoDB读取很多扩展存储，然后检查WHERE条件，丢弃所有不需要的数据。
? ③一张表里有很多大字段，最好组合起来单独存到一个列里面。让所有的大字段共享一个扩展存储空间，比每个字段用自己的页要好。
? ④把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩，可以获得显著的空间优势和性能收益。
? ⑤扩展存储禁用了自适应哈希，因为需要完整的比较列的整个长度，才能发现是不是正确的数据。
此文已由作者授权腾讯云+社区发布
【MySQL经典案例分析】关于数据行溢出由浅至深的探讨
标签：nod lnl red this 原因 engine 行记录 建表 可变
热心网友
时间：2022-05-02 18:43
如果是java层崩了，注意不要一次性加载太多的数据到内存，并且不在使用的数据要彻底放弃引用关系。java虽然是自动回收，回收的原则就是一个对象不再被持有，即引用计数为零。如果数据太大，可考虑临时文件。
如果是mysql崩了，首先增加配置缓存。一般来说mysql是不容易崩的，特别是插入操作的时候。查询的时候如果查询结果记录集特别大，会导致一个查询需要使用很大的内存空间，这种是有问题的。而插入操作都是一条一条的执行，不会导致大内存的使用。
如果仅仅是数据移植，也尽量不要用ORM框架，比如hibernate，mybatis这些东西，因为他们都有自己的缓存，直接使用JDBC比较好。
热心网友
时间：2022-05-02 20:01
内存泄漏（Memory Leak）是指程序中己动态分配的堆内存由于某种原因程序未释放或无法释放，造成系统内存的浪费，导致程序运行速度减慢甚至系统崩溃等严重后果。
内存泄漏缺陷具有隐蔽性、积累性的特征，比其他内存非法访问错误更难检测。因为内存泄漏的产生原因是内存块未被释放，属于遗漏型缺陷而不是过错型缺陷。此外，内存泄漏通常不会直接产生可观察的错误症状，而是逐渐积累，降低系统整体性能，极端的情况下可能使系统崩溃。
Valgrind 是一个用于构建动态分析工具的工具框架。它提供了一组工具，每个工具都执行某种调试、分析或类似的任务，以帮助您改进程序。Valgrind 的体系结构是模块化的，因此可以轻松地创建新工具，而不会影响现有的结构。
标配了许多有用的工具：
Memcheck 是内存错误检测器。
Cachegrind 是一个缓存和分支预测探查器。
Callgrind 是一个生成调用图的缓存分析器。
Helgrind 是线程错误检测器。
DRD 还是线程错误检测器。
Massif 是堆分析器。
DHAT 是另一种堆分析器。
SGcheck 是一种实验性工具，可以检测堆栈和全局阵列的溢出。
BBV 是一个实验性 SimPoint 基本块矢量生成器。
关于内存泄漏，我们需要使用 valgrind 的默认工具，也就是 memcheck 工具。
Memcheck 是内存错误检测器。它可以检测以下和内存相关的问题：
使用未初始化的内存
读取/写入已释放的内存
读取/写入 malloc 块的末端
内存泄漏
对 malloc/new/new[]与free/delete/delete[] 的不匹配使用
双重释放内存
