专栏首页MySQL故障优化案例MySQL Case-为何ibdata1共享表空间一直增加?
原创

MySQL Case-为何ibdata1共享表空间一直增加?

【现象】数据库磁盘使用率一直上升,从监控上看各个文件使用率,其中ibdata1使用有323G, 并且一直增加持续了31天,那么如果遇到这样的情况,是什么内容占用ibdata表空间,你怎么分析呢?

1、首先看了实例innodb_file_per_table是开启的

2、5.6版本undo信息

show variables like '%undo%';
1    innodb_undo_directory      .
2    innodb_undo_logs         128 
3    innodb_undo_tablespaces    0

ibdata1文件增大的原因有如下因素

1、InnoDB引擎表由于支持多版本并发控制(MVCC),因此会将查询所需的undo信息保存在系统文件ibdata1 中。

如果存在对一个InnoDB表长时间不结束的查询,而且在查询过程中表有大量的数据变化,则会生成大量的undo信息,导致 ibdata1文件尺寸增加。由于MySQL内部机制的限制,ibdata1文件目前是不支持收缩的。因此出现这样的情况,只能通过切换主备,或者迁移,再或者增大存储空间解决。 建议:监控和清理执行时间过长的会话或事务。

那么今天的例子大致如下:

通过show processlist 看到一个sql执行了31天,并且还处于query,Sending data状态,SQL本身三个表关联,没有关联条件,后面kill掉此sql,通过切换实例,新备重新搭建

数据文件

对于数据文件占用空间高的情况,可以通过清理数据的方式来减少空间占用情况,比如通过 drop table 和 truncate table 来清理不再需要的数据。

说明 3 个常见问题:

1、information_schema.tables 查询的数据容量

information_schema.tables 提供的是根据采样获取的表的部分统计信息,因此通过下面的查询获取的表、库数据尺寸和实际数据文件占用尺寸间会有出入(通常要小于实际数据文件占用空间)

select table_name, concat(round((data_length + index_length) / 1024 / 1024, 2), 'MB') as size_mb
from information_schema.tables
where table_schema = 'TPCH' and table_name = 'ORDERS';

注: 想获得更准确的大小,需要用analyze table 命令,重新收集统计信息,得到的数值通常也小于实际数据文件占用空间;

由于数据文件在频繁的 DML 后会出现数据空洞的现象,比较接近实际数据文件占用空间的计算方法请参考:

select sum(data_length + index_length + data_free) / 1024 / 1024 
from information_schema.tables;

注:因为 information_schema.tables 中提供的是采样统计数据,因此该计算方式在统计数据比较接近实际的情况下,才会比较接近真实空间占用情况。

2、delete 删除数据

delete 操作不能够直接回收被删除数据占用的数据文件空间,这就好比排空泳池中水但泳池的占地面积不会发生改变一样。而且 delete 操作会生成相应的 Binlog 文件,会进一步恶化空间使用情况。 在 delete 操作删除数据后,需要通过 optimize table tab_name; 操作来回收空间。

关于降低表大小可以参考本人另一篇文章,MySQL表碎片整理方法 https://cloud.tencent.com/developer/article/1864931

3、删除备份

自建MySQL可能存在备份占用空间的问题,需要排查,RDS上不会有这类问题,因为一般放到对象存储上面

临时文件

临时文件会随查询的结束或者会话的终止而自动释放,因此如果是临时文件导致实例空间满而锁定,可以通过终止会话来释放空间。

遇见过一个案例,客户排序操作导致ibtmp1很大,占用空间很高,需要释放,那么只能重启,也是切换成备库,然后重启新的备释放掉了(注意ibtmp重启实例就会释放)

ib_logfile 日志文件

ib_logfile0 和 ib_logfile1 日志文件保存 InnoDB 引擎表的事务日志信息,其文件大小尺寸固定,不可以改变。较大的尺寸在高并发事务的场景下有利于减少事务日志文件切换的次数,提高实例性能。

david有一个工具能看ibdata1里面具体页的数量大家不妨也可以研究下,py_innodb_page_info.py

总结:

如果再次遇到ibdata表空间很大,你的排查思路

1、看是否开启innodb_file_per_table参数

2、show processlist是否有长时间没执行完的SQL,是否有查询需要获得

更多文章欢迎关注本人公众号,搜dbachongzi或扫二维码

作者:姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 实战Zabbix-Server数据库mysq的libdata1文件过大

    今天我们的zabbix-server机器根空间不够了,我一步步排查结果发现是/var/lib/mysql/下的libdata1文件过大,已经达到了41G。我立即...

    DevinGeng
  • 第10期:选择合适的表空间

    MySQL 表空间可分为共享表空间和单表空间;其中共享表空间又可分为系统表空间和通用表空间。

    爱可生开源社区
  • Mysql备份系列(4)--lvm-snapshot备份mysql数据(全量+增量)操作记录

    Mysql最常用的三种备份工具分别是mysqldump、Xtrabackup(innobackupex工具)、lvm-snapshot快照。 前面分别介绍了: ...

    洗尽了浮华
  • 从Linux系统磁盘空间不足引发的Zabbix服务器数据库迁移

            之前一直没有去关心Zabbbix服务器存储空间问题,最近Zabbix报警提示/根目录磁盘空间不足,于是登录Zabbix看了一下,发现根目录只有1...

    木子-Lee
  • mysql表占用多少磁盘空间以及清理表空间

    早上上班,发现监控数据中,好几张表的所占数据空间突增,有的突增甚至达到了8G,仔细检测数据库之后,没有发现数据异常,那么问题出在哪里?

    用户4584874
  • 请收藏 | Linux运维常见故障及处理的 32 个锦囊妙计

    说起来日常的故障,其实,首先应该相到的就是:“备份”、“备份”、“备份”。毕竟再怎么牢固的系统或硬件都会有故障的时候,所以,备份放第一位。

    Spark学习技巧
  • mysql存储引擎、事务

    MySQL存储引擎介绍 文件系统 操作系统组织和存取数据的一种机制。 文件系统是一种软件。 文件系统类型 ext2  ext3  ext4  xfs 数据 不管...

    863987322
  • MySQL InnoDB 共享表空间和独立表空间

    导读:深入学习MySQL的时候总是习惯性的和Oracle数据库进行比较。在学习MySQL InnoDB的存储结构的时候也免不了跟Oracle进行比较。Oracl...

    码农架构
  • Linux运维常见故障排查和处理的33个技巧汇总

    作为linux运维,多多少少会碰见这样那样的问题或故障,从中总结经验,查找问题,汇总并分析故障的原因,这是一个Linux运维工程师良好的习惯。每一次技术的突破,...

    小小科
  • MySQL 中的共享表空间与独立表空间,用哪个好呢?

    松哥原创的四套视频教程已经全部杀青,感兴趣的小伙伴戳这里-->Spring Boot+Vue+微人事视频教程

    江南一点雨
  • MYSQL Innodb逻辑存储结构

    这几天在读《MySQL技术内幕 InnoDB存储引擎》,对 Innodb逻辑存储结构有了些了解,顺便也记录一下;

    SEian.G
  • MySQL 5.7 General Tablespace学习(r11笔记第34天)

    MySQL里面的文件蛮有意思,之前大体有两个参数来做基本的控制。一个是innodb_data_file_path就是一个共享表空间,数据都往这一个文件里放,也就...

    jeanron100
  • InnoDB 表空间

    这应该是 MySQL 原理中最底层的部分了,我们存在 MySQL 中的数据,到底在磁盘上长啥样。你可能会说,数据不都存储在聚簇索引中吗?但很遗憾,你并没有回答我...

    冬夜先生
  • 技术分享 | MySQL 内部临时表是怎么存放的

    如果 SQL 在执行过程中读到的数据无法直接得到结果,那么就需要额外的内存来保存中间结果,得出最终结果,这个额外的内存就是内部临时表。比如 group by 执...

    爱可生开源社区
  • Innodb中MySQL如何快速删除2T的大表

    这个时候所有的mysql的相关进程都会停止,直到drop结束,mysql才会恢复执行。出现这个情况的原因就是因为,在drop table的时候,innodb维护...

    双面人
  • MySQL 之日志

    MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件(各自存储了不同类型的日志),从日志当中可以查询到MySQL数据库的运行情...

    小手冰凉
  • InnoDB存储引擎文件

    与InnoDb存储引擎密切相关的文件包括重做日志文件和表空间文件,首先来说说我对表空间文件的理解。表空间文件是用来存储表信息和表数据的,它默认的大小是1...

    AsiaYe
  • MySQL学习笔记(二)物理文件层的实现

    在上一章我们了解到,物理文件层在MySQL架构位于最底层,将数据库的数据存储在文件系统上,并完成与存储引擎的交互。存储数据包括日志文件,数据文件,配置文件等。本...

    scarlett学习手册
  • Mysql引擎介绍及InnoDB逻辑存储结构

    摘自https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html

    码农编程进阶笔记

扫码关注云+社区

领取腾讯云代金券