Mysql脏页flush及收缩表空间原理解析

mysql脏页

由于WAL机制，InnoDB在更新语句的时候，制作了写日志这一个磁盘操作，就是redo log，在内存写完redo log后，就返回给客户端， 即更新成功。

把内存里的数据写入磁盘的过程，术语就是flush，在flush之前，实际数据和数据库中的数据是不一致的，因为在redo log基础上更新了还未写入，数据库是老的，当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为脏页，内存写入后就一致了，称为干净页，

如果mysql偶尔运行速度很慢，很可能是在刷脏页。引发数据库flush的过程

  • redo log满了，系统停止所有更新操作，将checkpoint向前推进，腾出空间继续写。
  • 系统内存不足，需要新的内存页不够用，就会淘汰一些数据页，留给别的数据页使用，如果淘汰的是脏页，就会先写到磁盘。
  • mysql空闲的时候。
  • 正常关闭mysql的时候
  • 在第一种情况下，redo log满了，这种情况是innodb要避免的，因为整个系统都不能再更新了，这是不能接受的
  • 第二种情况，内存满了，要先写到磁盘，innodb用缓冲池管理内存，有三种状态
  • 还没有用的内存页
  • 用了并且是干净页
  • 用了并且是脏页（淘汰的时候需要写入到磁盘）

所以我们有时使用数据库会发现数据库性能突然下降，可能就是在处理脏页。

刷脏页控制策略

  • Innodb_io_capacity参数，这个参数会告诉innodb你的磁盘io能力。（有公式计算）
  • innodb刷盘主要两个因素：脏页比例和redo log的写盘速度
  • innodb_max_derty_pages_pct是脏页比例上限，默认是75%，调整好Innodb_io_capacity参数值，使脏页比例不要超过75%收缩表空间

场景例子：数据库占用空间太大，把最大的表删掉了一半数据，表的大小还是没有变化。

数据删除流程

加入要删掉R4，InnoDB引擎只会把R4这个记录标记为删除，如果之后再掺入一个ID在300-600之间的记录时，会复用这个位置，但是磁盘文件的大小并不会缩小。

如果删掉了一个数据页上的所有记录，这个数据页就可以被复用。

注意：数据页的复用跟记录的复用是不同的。

  • 比如R4这条记录被删除，如果插入一个ID是400的行，直接复用这个空间，但是如果插入ID是800的行，就不能复用这个位置了。
  • 但是整个数据页Page A上的所有记录删除之后，pageA标记为可复用，如果插入一条ID=50的记录需要用新的数据页的时候，PageA是可以背负用的。
  • 如果我们用delete命令把整个表数据删除，结果是所有的数据页都会标记为可复用，但是在磁盘上，文件不会变小。

插入数据流程

如果数据按照索引顺序插入的，索引是紧凑的，但是如果是随机插入的，就会造成索引的数据页分页。

如果pageA已经满了，在插入一行数据会怎样？由于A满了，在插入一个id是550的数据时，就会申请一个新的页面pageB来保存数据，分裂完成后pageA的末尾就留下了空洞。

更新索引上的值也是删除一个旧的值，再插入一个新值，也会造成空洞。

收缩空间

新建一个与表A相同结构的表B，按照主键ID递增的顺序，把数据一行一行的从A里读出来再插入到表B中，表B中无空洞，数据页的利用率也更高，如果我们把表B作为临时表，数据从表A导入到B中的操作完成后，用B替换A，从效果上也起到了收缩A的作用。

在整个DDL过程中，表A不能有更新，所以这个DDL不是online的，在5.6之后的版本中，流程做了更改：

建立一个临时文件，扫描A中的所有数据页，

用数据页中A的记录生成B+树，存储在临时文件中

将所有对A的操作记录在一个日志文件中

临时文件生成后，将日志文件的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件

用临时文件替换表A的数据文件

图示

可以看到，与图3过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A做增删改操作。

使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • 解决Mysql收缩事务日志和日志文件过大无法收缩问题

    一.MS SQL SERVER 2005 --1.清空日志       exec('DUMP TRANSACTION 数据库名 WITH NO_LOG') --2.截断事务日志:      exec('BACKUP LOG 数据库名 WITH NO_LOG') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小      exec('DBCC SHRINKDATABASE(数据库名) ') --4.设置自动收缩      exec('EXEC sp_dboption 数据库名,autosh

  • Mysql InnoDB删除数据后释放磁盘空间的方法

    Innodb数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致InnoDB数据库文件不断增长. 如果在创建数据库的时候设置innodb_file_per_table=1,这样InnoDB会对每个表创建一个数据文件,然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间. 运行OPTIMIZE TABLE 表名后,虽然最后会报Table does not support optimize, doing recreate + analyze in

  • MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句

    查询所有数据库占用磁盘空间大小的SQL语句: 复制代码 代码如下: select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_sizefrom information_schema.tablesgroup by TABLE_SCHEMAorder by dat

  • MySQL 清除表空间碎片的实例详解

    MySQL 清除表空间碎片的实例详解

    MySQL 清除表空间碎片的实例详解 碎片产生的原因 (1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白.被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大: (2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片: (3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分: 例如: 一个表有1万行

  • MYSQL SERVER收缩日志文件实现方法

    事务日志记录着在相关数据库上的操作,同时还存储数据库恢复(recovery)的相关信息. 收缩日志的原因有很多种,有些是考虑空间不足,有些则是应用程序限制导致的. 下面介绍的是在简单模式下,进行收缩操作. 一.命令行搞定 USE MASTER GO ALTER DATABASE {DBName} SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE {DBName} SET RECOVERY SIMPLE --简单模式 GO USE {DBName

  • 深度解析MySQL 5.7之临时表空间

    临时表 临时表顾名思义,就是临时的,用完销毁掉的表. 数据既可以保存在临时的文件系统上,也可以保存在固定的磁盘文件系统上. 临时表有下面几种: 1.全局临时表 这种临时表从数据库实例启动后开始生效,在数据库实例销毁后失效.在MySQL里面这种临时表对应的是内存表,即memory引擎. 2.会话级别临时表 这种临时表在用户登录系统成功后生效,在用户退出时失效.在MySQL里的临时表指的就是以create temporary table 这样的关键词创建的表. 3.事务级别临时表 这种临时表在事务开

  • MySQL 5.7临时表空间如何玩才能不掉坑里详解

    导读 MySQL 5.7的目标是成为发布以来最安全的MySQL服务器,其在SSL/TLS和全面安全开发方面有一些重要的改变. MySQL 5.7起支持独立临时表空间,但个别时候也可能会踩坑的. MySQL 5.7起,开始采用独立的临时表空间(和独立的undo表空间不是一回事哟),命名ibtmp1文件,初始化12M,且默认无上限. 选项 innodb_temp_data_file_path 可配置临时表空间相关参数. innodb_temp_data_file_path = ibtmp1:12M:

  • ubuntu下磁盘空间不足导致mysql无法启动的解决方法

    前言 最近在数据库的一张表添加两个字段,后来提示什么磁盘空间不足什么什么的,后来数据库就断开连接了,之后就一直连接不上去后来,最后经过思考终于解决了这个问题,这一经历下来真是心惊胆战,本文作为记录一下磁盘空间不足导致的 mysql 无法启动的解决办法. 方法如下 操作系统:ubuntu,磁盘空间不足导致的 mysql 无法启动,会造成如下问题: root@iZ28z558vv0Z:/etc/mysql# mysql -u root -p Enter password: ERROR 2002 (H

  • Mysql脏页flush及收缩表空间原理解析

    mysql脏页 由于WAL机制,InnoDB在更新语句的时候,制作了写日志这一个磁盘操作,就是redo log,在内存写完redo log后,就返回给客户端, 即更新成功. 把内存里的数据写入磁盘的过程,术语就是flush,在flush之前,实际数据和数据库中的数据是不一致的,因为在redo log基础上更新了还未写入,数据库是老的,当内存数据页跟磁盘数据页内容不一致的时候,称这个内存页为脏页,内存写入后就一致了,称为干净页, 如果mysql偶尔运行速度很慢,很可能是在刷脏页.引发数据库flus

  • mysql脏页是什么

    目录 脏页(内存页) 为什么会出现 脏页 内存管理机制简述 数据修改和读取只依赖缓冲区行不行 为什么脏页一定要刷新 刷脏页有下面4种场景 会造成的影响 脏页(内存页) 干净页:内存和磁盘中的数据一致 脏页:内存和磁盘中的数据不一致 为什么会出现 脏页 平时很快的更新操作,都是在写内存和日志. 他并不会马上同步到磁盘数据页,这时内存数据页跟磁盘数据页内容不一致,我们称之为脏页. 这里面就涉及 mysql 的内存管理机制 内存管理机制简述 缓冲区中包含这三大类列表.分别为:LRUList.FreeL

  • 浅谈MySQL表空间回收的正确姿势

    浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • MySQL数据库表空间回收的解决

    目录 1. MySQL表空间回收 2. MySQL表空间设置 3. MySQL删除数据流程 4. MySQL数据页空洞问题 1. MySQL表空间回收 我们经常会发现一个问题,就是把表数据删除以后发现,数据文件大小并没有变化,这就是标题中所说的MySQL数据库表空间回收问题. 这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论.一个InnoDB表包含两部分,即:表结构定义和数据.在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里.而MySQL 8.0版本,则已经允

  • Oracle缩表空间的完整解决实例

    Oracle缩表空间的完整解决实例

    目录 备注: 一. 需求 二. 解决方案 2.1 清理过期数据 2.2 收缩表空间 2.3 清理表碎片 2.4 直接把相关的表drop掉 2.5 把该表空间下其它的表移出此表空间 总结 备注: Oracle 11.2.0.4 一. 需求 近期有一个日志库,占用了比较多的空间,需要将历史的清理,然后收缩空间. 如下图所示,4T的空间已经差不多用完. 二. 解决方案 首先想到的是清理掉超过半年的数据,然后resize 表空间. 2.1 清理过期数据 因为业务的表是 tablename_yearmon

  • mysql Innodb表空间卸载、迁移、装载的使用方法

    条件:2台服务器:A和B,需要A服务器上的表迁移到B服务器.Innodb表:sysUser,记录数:351781.以下测试在MySQL 5.5.34中进行.开始处理:1:在B服务器上建立sysUser表,并且执行: 复制代码 代码如下: zjy@B : db_test 09:50:30>alter table sysUser discard tablespace; 2:把A服务器表的表空间(ibd)复制到B服务器的相应数据目录.3:修改复制过来的ibd文件权限: 复制代码 代码如下: chown

  • 浅析mysql 共享表空间与独享表空间以及他们之间的转化

    浅析mysql 共享表空间与独享表空间以及他们之间的转化

    innodb这种引擎,与MYISAM引擎的区别很大.特别是它的数据存储格式等.对于innodb的数据结构,首先要解决两个概念性的问题: 共享表空间以及独占表空间.什么是共享表空间和独占表空间共享表空间以及独占表空间都是针对数据的存储方式而言的.共享表空间:  某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下. 默认的文件名为:ibdata1  初始化为10M.独占表空间:  每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm

  • MySQL InnoDB表空间加密示例详解

    前言 从 MySQL5.7.11开始,MySQL对InnoDB支持存储在单独表空间中的表的数据加密 .此功能为物理表空间数据文件提供静态加密.该加密是在引擎内部数据页级别的加密手段,在数据页写入文件系统时加密,加密用的是AES算法,而其解密是在从文件读到内存中时进行. 1 配置加密插件 1.1 修改配置文件 在mysql配置文件[mysqld]x项中添加如下内容 plugin_dir=/usr/local/mysql5.7/lib/mysql/plugin # 插件路径,根据实际情况修改 ear

  • MySQL的表空间是什么

    今天我要跟你分享的话题是:"大家常说的表空间到底是什么?究竟什么又是数据表?" 这其实是一个概念性的知识点,当作拓展知识.涉及到的概念大家了解一下就好,涉及的参数,留个印象就好. 一.什么是表? 但凡是用过MySQL都知道,直观上看,MySQL的数据都存在数据表中. 比如一条Update SQL: update user set username = '白日梦' where id = 999; 它将user这张数据表中id为1的记录的username列修改成了'白日梦' 这里的user

  • MySQL的Flush-List和脏页的落盘机制

    一.回顾 MySQL启动后Buffer Pool会初始化.Buffer Pool也会初始化好N多个空白的缓存页,以及它们的描述数据会被组织成LRU链表以及FreeList 双向链表. 这时你从磁盘中读取一个数据页,会先从Free List中找出一个空闲缓存页的描述信息,然后将你读出的数据页中加载进缓存页中.同时将缓存页的描述信息从Free List中剔除,此外该描述信息块还会被维护进LRU链表中. 数据页被加载进Buffer Pool后你就可以对其进行变更操作了. 二.Flush List 为了

随机推荐