mysql多实例中ibdata1_ibdata1共享表空间文件一直增加的一个案例

【现象】数据库磁盘使用率一直上升，从监控上看各个文件使用率，其中ibdata1使用有323G，

并且一直增加持续了31天

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文件增大的原因有如下因素：

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

中。

如果存在对一个InnoDB表长时间不结束的查询，而且在查询过程中表有大量的数据变化，

则会生成大量的Undo信息，导致 ibdata1文件尺寸增加。

由于 MySQL内部机制的限制，ibdata1文件目前是不支持收缩的。

因此出现这样的情况，只能通过切换主备，或者迁移，再或者增大存储空间解决。

建议：监控和清理执行时间过长的会话或事务。

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

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

1.2 数据文件

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

说明 3 个常见问题：

1.2.1 information_schema.tables 查询的数据容量

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

select table_name, concat(round((data_length + index_length)/1024/1024,2),'MB') from

information_schema.tables where

table_schema = 'TESTDB' and table_name = 'TESTTABLE';

下图中可以看到：在收集表的统计信息前后反馈出的表数据量大小存在差异。

注：

即使通过 analyze table 命令，重新收集统计信息，得到的数值通常也小于实际数据文件占用空间；比如本例的 16143 MB 也小于该表的数据文件实际占用空间。

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

select

sum(data_length + index_length + data_free) / 1024 / 1024from

information_schema.tables;

注：

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

1.2.2 delete 删除数据

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

在 delete 操作删除数据后，需要通过 optimize table tab_name; 操作来回收空间。

1.2.3  删除备份

自建MySQL可能存在备份占用空间的问题，但是云上RDS 备份放置在后台 OSS 上，不占用用户的 RDS 实例空间，因此删除备份不能解决实例的空间问题。而且删除备份会影响实例的可恢复性，强烈建议任何情况下不要考虑删除备份。

1.3 临时文件

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

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

ib_logfile 日志文件：

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

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