笔触狂放9

这个屌丝很懒，什么也没留下！

热门标签

mysql 表空间收缩_MySQL 5.7新特性：在线收缩undo log表空间

作者：笔触狂放9 | 2024-02-21 08:31:27

踩

innodb_max_undo_log_size

一、MySQL 5.5时代的undo log

InnoDB存储引擎中，Undo log一直都是事务多版本控制中的核心组件，它具有以下的核心功能：

事务的回退：事务在处理过程中遇到异常的时候可以rollback(撤销)所做的全部修改。

事务的恢复：数据库实例崩溃时，将磁盘的不正确数据恢复到交易前。

读一致性：被查询的记录有事务占用，转向回滚段找事务开始前的数据镜像。

Undo在完成事务回滚和MVCC之后，就可以purge掉了，但undo在事务执行过程中，进行的空间分配如何回收，就变成了一个问题。我们亲历用户的小实例，因为一个大事务，导致ibdata file到800G大小。

在MySQL5.5以及之前，大家会发现随着数据库上线时间越来越长，ibdata1文件(即InnoDB的共享表空间，或者系统表空间)会越来越大，这会造成2个比较明显的问题：

1)磁盘剩余空间越来越小，到后期往往要加磁盘；

2)物理备份时间越来越长，备份文件也越来越大。

这是怎么回事呢？

原因除了数据量自然增长之外，在MySQL 5.5以及之前，InnoDB的undo log也是存放在ibdata1里面的。一旦出现大事务，这个大事务所使用的undo log占用的空间就会一直在ibdata1里面存在，即使这个事务已经关闭。虽然空间可以重用，但文件大小不能更改。

那么问题来了，有办法把上面说的空闲的undo log占用的空间从ibdata1里面清理掉吗？答案是没有直接的办法，只能全库导出sql文件，然后重新初始化MySQL实例，再全库导入。

关于回滚段的，只有这个主要的参数，用来设置多少个rollback segment。

mysql> show global variables like '%rollback_segment%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| innodb_rollback_segments | 128 |

+----------------------------+-------+

mysql>showglobalvariableslike'%rollback_segment%';

+----------------------------+-------+

|Variable_name|Value|

+----------------------------+-------+

|innodb_rollback_segments|128|

+----------------------------+-------+

二、MySQL 5.6时代的undo log

在MySQL 5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下；这给我们部署不同IO类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上。增加了如下几个参数来控制该行为。

mysql> show global variables like '%undo%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_directory | . |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 0 |

+-------------------------+-------+

mysql>showglobalvariableslike'%undo%';

+-------------------------+-------+

|Variable_name|Value|

+-------------------------+-------+

|innodb_undo_directory|.|

|innodb_undo_logs|128|

|innodb_undo_tablespaces|0|

+-------------------------+-------+

innodb_undo_directory=.

当开启独立undo表空间时，指定undo文件存放的目录，就是用于设置rollback segment(回滚段)文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为”.”，表示当前InnoDB存储引擎的目录。如果我们想转移undo文件的位置，只需要修改下该配置，并将undo文件拷贝过去就可以了。

innodb_undo_logs=128

用来设置rollback segment(回滚段)的个数，默认为128。在InnoDB 1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。

innodb_undo_tablespaces=0

用于设定创建的undo表空间的个数，也就是用来构成rollback segment文件的数量，这样rollback segment可以较为平均地分布在多个文件中。在Install db时初始化后，就再也不能被改动了。默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为16，那么就会创建命名为undo001~undo016的undo tablespace文件，每个文件的默认大小为10M。并且会在路径innodb_undo_directory看到undo为前缀的文件。

修改该值可能会导致Innodb无法完成初始化。

分析

在innodb启动时(innobase_start_or_create_for_mysql)，会进行undo表空间初始化，细节见函数srv_undo_tablespaces_init。

–> 如果是新建实例，会去创建undo log文件，undo表空间的space id从1开始；默认初始化大小为10M，由宏SRV_UNDO_TABLESPACE_SIZE_IN_PAGES控制。

–> 读取当前实例的所有undo表空间的space id (trx_rseg_get_n_undo_tablespaces)。

首先从ibdata中读取到事务系统的文件头，然后再从其中记录的回滚段信息，找到回滚段对应的space id和page no(trx_sysf_rseg_get_space，trx_sysf_rseg_get_page_no)，并按照space id排序后返回。

–> 根据上一步读到的space id依次打开undo文件(srv_undo_tablespace_open)，如果不存在，就标识启动失败。

所以undo文件也是类似ibdata的重要文件，目前是不可以删除的。所以不要试图删除undo文件来释放空间！可以容忍定义的table space个数比已有的undo文件个数要少(但所有的undo文件依然会打开)，反之则报错初始化失败。

undo回滚段初始化(trx_sys_create_rsegs)

如果是正常shutdown重启，并且设置的回滚段个数大于目前已经使用的回滚段个数(trx_sysf_rseg_find_free)，就会去新建回滚段(trx_rseg_create)。

这里总是从第一个undolog tablespace开始初始化回滚段，看起来似乎有些问题，极端情况下，如果我每次重启递增innodb_undo_logs，是不是意味着所有的undo回滚段都会写入到第一个undo tablespace中?

当有长时间运行的事务时，可能导致purge操作来不及回收undo空间，进而导致undo空间急剧膨胀；理论上讲，如果做一次干净的shutdown，应该可以安全的将将这些undo文件删除并重新做一次初始化；也许未来的某个MySQL版本可能实现这个功能，这对于某些服务(比如按磁盘空间收费的云计算提供商)是非常有必要的功能。

实际使用方面，在初始化实例之前，我们只需要设置innodb_undo_tablespaces参数(建议大于等于3)即可将undo log设置到单独的undo表空间中。如果需要将undo log放到更快的设备上时，可以设置innodb_undo_directory参数，但是一般我们不这么做，因为现在SSD非常普及。innodb_undo_logs可以默认为128不变。

三、MySQL 5.7时代的undo log

那么问题又来了，undo log单独拆出来后就能缩小了吗？MySQL 5.7引入了新的参数，innodb_undo_log_truncate，开启后可在线收缩拆分出来的undo表空间。

在满足以下2个条件下，undo表空间文件可在线收缩：

1)innodb_undo_tablespaces>=2：因为truncate undo表空间时，该文件处于inactive状态，如果只有1个undo表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响，建议将该参数最少设置为3；

2)innodb_undo_logs>=35(默认128)：因为在MySQL 5.7中，第一个undo log永远在系统表空间中，另外32个undo log分配给了临时表空间，即ibtmp1，至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log；

满足以上2个条件后，把innodb_undo_log_truncate设置为ON即可开启undo表空间的自动truncate，这还跟如下2个参数有关：

1)innodb_max_undo_log_size：undo表空间文件超过此值即标记为可收缩，默认1G，可在线修改；

2)innodb_purge_rseg_truncate_frequency：指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时，undo表空间才会被truncate。由此可见，该参数越小，undo表空间被尝试truncate的频率越高。

基本也就是InnoDB的purge线程，会根据innodb_undo_log_truncate开关的设置，和innodb_max_undo_log_size设置的文件大小阈值，以及truncate的频率来进行空间回收和rollback segment的重新初始化。

MySQL 5.7关于undo log参数如下：

mysql> show global variables like '%undo%';

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 1073741824 |

| innodb_undo_directory | ./ |

| innodb_undo_log_truncate | OFF |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 0 |

+--------------------------+------------+

5 rows in set (0.00 sec)

mysql> show global variables like '%truncate%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| innodb_purge_rseg_truncate_frequency | 128 |

| innodb_undo_log_truncate | OFF |

+--------------------------------------+-------+

2 rows in set (0.01 sec)

mysql>showglobalvariableslike'%undo%';

+--------------------------+------------+

|Variable_name|Value|

+--------------------------+------------+

|innodb_max_undo_log_size|1073741824|

|innodb_undo_directory|./|

|innodb_undo_log_truncate|OFF|

|innodb_undo_logs|128|

|innodb_undo_tablespaces|0|

+--------------------------+------------+

5rowsinset(0.00sec)

mysql>showglobalvariableslike'%truncate%';

+--------------------------------------+-------+

|Variable_name|Value|

+--------------------------------------+-------+

|innodb_purge_rseg_truncate_frequency|128|

|innodb_undo_log_truncate|OFF|

+--------------------------------------+-------+

2rowsinset(0.01sec)

innodb_undo_log_truncate

参数设置为1，即开启在线回收(收缩)undo log日志文件，支持动态设置。

innodb_undo_tablespaces

参数必须大于或等于2，即回收(收缩)一个undo log日志文件时，要保证另一个undo log是可用的。

innodb_undo_logs

undo回滚段的数量，至少大于等于35，默认128。

innodb_max_undo_log_size

当超过这个阀值(默认是1G)，会触发truncate回收(收缩)动作，truncate后空间缩小到10M。

innodb_undo_directory

undo文件存放的位置。

innodb_purge_rseg_truncate_frequency

控制回收(收缩)undo log的频率，undo log空间在它的回滚段没有得到释放之前不会收缩，想要增加释放回滚区间的频率，就得降低innodb_purge_rseg_truncate_frequency设定值。

四、MySQL 5.7时代undo表空间的truncate示例

1) 首先确保如下参数被正确设置：

# 为了实验方便,我们减小该值;

innodb_max_undo_log_size = 100M

innodb_undo_log_truncate = ON

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

# 为了实验方便,我们减小该值;

innodb_purge_rseg_truncate_frequency = 10

# 为了实验方便,我们减小该值;

innodb_max_undo_log_size=100M

innodb_undo_log_truncate=ON

innodb_undo_logs=128

innodb_undo_tablespaces=3

# 为了实验方便,我们减小该值;

innodb_purge_rseg_truncate_frequency=10

2) 创建表

mysql> create table t1(id int primary key auto_increment,name varchar(200));

mysql>createtablet1(idintprimarykeyauto_increment,namevarchar(200));

3)插入测试数据

mysql> insert into t1(name) values(repeat('a',200));

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(name) select name from t1;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t1(name) select name from t1;

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into t1(name) select name from t1;

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

...

mysql> insert into t1(name) select name from t1;

Query OK, 8388608 rows affected (2 min 11.31 sec)

Records: 8388608 Duplicates: 0 Warnings: 0

mysql>insertintot1(name)values(repeat('a',200));

QueryOK,1rowaffected(0.01sec)

mysql>insertintot1(name)selectnamefromt1;

QueryOK,1rowaffected(0.00sec)

Records:1 Duplicates:0 Warnings:0

mysql>insertintot1(name)selectnamefromt1;

QueryOK,2rowsaffected(0.01sec)

Records:2 Duplicates:0 Warnings:0

mysql>insertintot1(name)selectnamefromt1;

QueryOK,4rowsaffected(0.00sec)

Records:4 Duplicates:0 Warnings:0

...

mysql>insertintot1(name)selectnamefromt1;

QueryOK,8388608rowsaffected(2min11.31sec)

Records:8388608 Duplicates:0 Warnings:0

这时undo表空间文件大小如下，可以看到有一个undo文件已经超过了100M。

-rw-r----- 1 mysql mysql 13M Feb 17 17:59 undo001

-rw-r----- 1 mysql mysql 128M Feb 17 17:59 undo002

-rw-r----- 1 mysql mysql 64M Feb 17 17:59 undo003

-rw-r-----1mysqlmysql 13MFeb1717:59undo001

-rw-r-----1mysqlmysql128MFeb1717:59undo002

-rw-r-----1mysqlmysql 64MFeb1717:59undo003

此时，为了，让purge线程运行，可以运行几个delete语句：

mysql> delete from t1 limit 1;

Query OK, 1 row affected (0.00 sec)

mysql> delete from t1 limit 1;

Query OK, 1 row affected (0.00 sec)

mysql> delete from t1 limit 1;

Query OK, 1 row affected (0.00 sec)

mysql> delete from t1 limit 1;

Query OK, 1 row affected (0.00 sec)

mysql>deletefromt1limit1;

QueryOK,1rowaffected(0.00sec)

mysql>deletefromt1limit1;

QueryOK,1rowaffected(0.00sec)

mysql>deletefromt1limit1;

QueryOK,1rowaffected(0.00sec)

mysql>deletefromt1limit1;

QueryOK,1rowaffected(0.00sec)

再查看undo文件大小：

-rw-r----- 1 mysql mysql 13M Feb 17 18:05 undo001

-rw-r----- 1 mysql mysql 10M Feb 17 18:05 undo002

-rw-r----- 1 mysql mysql 64M Feb 17 18:05 undo003

-rw-r-----1mysqlmysql 13MFeb1718:05undo001

-rw-r-----1mysqlmysql 10MFeb1718:05undo002

-rw-r-----1mysqlmysql 64MFeb1718:05undo003

可以看到，超过100M的undo文件已经收缩到10M了。

五、MySQL 5.7 undo log管理

5.1 undo表空间创建

设置innodb_undo_tablespaces的个数，在mysql install的时候，创建指定数量的表空间。InnoDB默认支持128个undo logs，但表示的都是回滚段的个数。从5.7.2开始，其中32个undo logs为临时表的事务分配的，因为这部分undo不记录redo，不需要recovery，另外从33-128一共96个是redo-enabled undo。

rollback segment的分配如下：

Slot-0: reserved for system-tablespace.

Slot-1....Slot-N: reserved for temp-tablespace.

Slot-N+1....Slot-127: reserved for system/undo-tablespace. */

Slot-0:reservedforsystem-tablespace.

Slot-1....Slot-N:reservedfortemp-tablespace.

Slot-N+1....Slot-127:reservedforsystem/undo-tablespace.*/

其中如果是临时表的事务，需要分配两个undo logs，其中一个是non-redo undo logs；这部分用于临时表数据的回滚。

另外一个是redo-enabled undo log，是为临时表的元数据准备的，需要recovery。而且，其中32个rollback segment创建在临时表空间中，并且临时表空间中的回滚段在每次server start的时候，需要重建。每一个rollback segment可以分配1024个slot，也就是可以支持96*1024个并发的事务同时，但如果是临时表的事务，需要占用两个slot。

InnoDB undo的空间管理简图如下：

注核心结构说明：

1. rseg slot

rseg slot一共128个，保存在ibdata系统表空间中，其位置在：

/*!< the start of the array of rollback segment specification slots */

#define TRX_SYS_RSEGS (8 + FSEG_HEADER_SIZE)

/*!

#define TRX_SYS_RSEGS (8 + FSEG_HEADER_SIZE)

每一个slot保存着rollback segment header的位置。包括space_id + page_no，占用8个bytes。其宏定义：

/* Rollback segment specification slot offsets */

/*-------------------------------------------------------------*/

#define TRX_SYS_RSEG_SPACE 0 /* space where the segment

header is placed; starting with

MySQL/InnoDB 5.1.7, this is

UNIV_UNDEFINED if the slot is unused */

#define TRX_SYS_RSEG_PAGE_NO 4 /* page number where the segment

header is placed; this is FIL_NULL

if the slot is unused */

/* Size of a rollback segment specification slot */

#define TRX_SYS_RSEG_SLOT_SIZE 8

/*Rollbacksegmentspecificationslotoffsets*/

/*-------------------------------------------------------------*/

#define TRX_SYS_RSEG_SPACE 0 /* space where the segment

headerisplaced;startingwith

MySQL/InnoDB5.1.7,thisis

UNIV_UNDEFINEDiftheslotisunused*/

#define TRX_SYS_RSEG_PAGE_NO 4 /* page number where the segment

headerisplaced;thisisFIL_NULL

iftheslotisunused*/

/*Sizeofarollbacksegmentspecificationslot*/

#define TRX_SYS_RSEG_SLOT_SIZE 8

2. rseg header

rseg header在undo表空间中，每一个rseg包括1024个undo segment slot，每一个slot保存着undo segment header的位置，包括page_no，暂用4个bytes，因为undo segment不会跨表空间，所以space_id就没有必要了。

其宏定义如下：

/* Undo log segment slot in a rollback segment header */

/*-------------------------------------------------------------*/

#define TRX_RSEG_SLOT_PAGE_NO 0 /* Page number of the header page of

an undo log segment */

/*-------------------------------------------------------------*/

/* Slot size */

#define TRX_RSEG_SLOT_SIZE 4

/*Undologsegmentslotinarollbacksegmentheader*/

/*-------------------------------------------------------------*/

#define TRX_RSEG_SLOT_PAGE_NO 0 /* Page number of the header page of

anundologsegment*/

/*-------------------------------------------------------------*/

/*Slotsize*/

#define TRX_RSEG_SLOT_SIZE 4

3. undo segment header

undo segment header page即段内的第一个undo page，其中包括四个比较重要的结构：undo segment header进行段内空间的管理；undo page header page内空间的管理，page的类型：FIL_PAGE_UNDO_LOG；undo header包含undo record的链表，以便安装事务的反顺序，进行回滚；undo record 剩下的就是undo记录了。

5.2 undo段的分配

undo段的分配比较简单，其过程如下：

首先是rollback segment的分配：

trx->rsegs.m_redo.rseg = trx_assign_rseg_low(

srv_undo_logs, srv_undo_tablespaces,

TRX_RSEG_TYPE_REDO);

trx->rsegs.m_redo.rseg=trx_assign_rseg_low(

srv_undo_logs,srv_undo_tablespaces,

TRX_RSEG_TYPE_REDO);

1. 使用round-robin的方式来分配rollback segment；

2. 如果有单独设置undo表空间，就不使用system表空间中的undo segment；

3. 如果设置的是truncate的就不分配；

4. 一旦分配了，就设置trx_ref_count，不允许truncate。

参考代码如下：

/******************************************************************//**

Get next redo rollback segment. (Segment are assigned in round-robin fashion).

@return assigned rollback segment instance */

static

trx_rseg_t*

get_next_redo_rseg(

/*===============*/

ulong max_undo_logs, /*!< in: maximum number of UNDO logs to use */

ulint n_tablespaces) /*!< in: number of rollback tablespaces */

/******************************************************************//**

Getnextredorollbacksegment.(Segmentareassignedinround-robinfashion).

@returnassignedrollbacksegmentinstance*/

static

trx_rseg_t*

get_next_redo_rseg(

/*===============*/

ulongmax_undo_logs,/*!

ulintn_tablespaces)/*!

其次是undo segment的创建：

从rollback segment里边选择一个free的slot，如果没有，就会报错，通常是并发的事务太多。

错误日志如下：

ib::warn() << "Cannot find a free slot for an undo log. Do"

" you have too many active transactions running"

" concurrently?";

ib::warn()<

" you have too many active transactions running"

" concurrently?";

如果有free，就创建一个undo的segment。

5.3 undo的truncate

undo的truncate主要由下面两个参数控制：innodb_purge_rseg_truncate_frequency，innodb_undo_log_truncate。

1. innodb_undo_log_truncate：是开关参数。

2. innodb_purge_rseg_truncate_frequency：默认128，表示purge undo轮询128次后，进行一次undo的truncate。

当设置innodb_undo_log_truncate=ON的时候， undo表空间的文件大小，如果超过了innodb_max_undo_log_size，就会被truncate到初始大小，但有一个前提，就是表空间中的undo不再被使用。

其主要步骤如下：

1. 超过大小了之后，会被mark truncation，一次会选择一个。

2. 选择的undo不能再分配新给新的事务。

3. purge线程清理不再需要的rollback segment。

4. 等所有的回滚段都释放了后，truncate操作，使其成为install db时的初始状态。

默认情况下，是purge触发128次之后，进行一次rollback segment的free操作，然后如果全部free就进行一个truncate。但mark的操作需要几个依赖条件需要满足：

1. 系统至少得有两个undo表空间，防止一个offline后，至少另外一个还能工作。

2. 除了ibdata里的segment，还至少有两个segment可用。

3. undo表空间的大小确实超过了设置的阈值。

其核心代码参考：

/** Iterate over all the UNDO tablespaces and check if any of the UNDO

tablespace qualifies for TRUNCATE (size > threshold).

@param[in,out] undo_trunc undo truncate tracker */

static

void

trx_purge_mark_undo_for_truncate(

undo::Truncate* undo_trunc)

/**IterateoveralltheUNDOtablespacesandcheckifanyoftheUNDO

tablespacequalifiesforTRUNCATE(size>threshold).

@param[in,out]undo_truncundotruncatetracker*/

static

void

trx_purge_mark_undo_for_truncate(

undo::Truncate*undo_trunc)

因为，只要你设置了truncate = on，MySQL就尽可能的帮你去truncate所有的undo表空间，所以它会循环的把undo表空间加入到mark列表中。

最后，循环所有的undo段，如果所属的表空间是marked truncate，就把这个rseg标志位不可分配，加入到trunc队列中，在purge的时候，进行free rollback segment。

注意：如果是在线库，要注意影响，因为当一个undo tablespace在进行truncate的时候，不再承担undo的分配。只能由剩下的undo 表空间的rollback segment接受事务undo空间请求。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/122040