Mysql磁盘管理_Undo Tablespace

Source
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/fly43108622/article/details/86580173

undo表空间基本配置,官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

 

回滚段,逻辑日志,相反操作

mvcc 实现非锁定读

--配置几个undo表空间,默认不创建,放在ibdata 共享表空间
--必须初始化实例的时候配置,且不能改变:The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance.
--如果需要回收undo表空间, 至少2个
--8.0之后,可以实时手工增加undo表空间:As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using CREATE UNDO TABLESPACE syntax.
mysql> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 0     |
+-------------------------+-------+
1 row in set (0.01 sec)

--undo表空间目录,默认当前目录
mysql> show variables like 'innodb_undo_directory';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | ./    |
+-----------------------+-------+
1 row in set (0.00 sec)

--是否回收undo表空间的空间,默认关闭
mysql> show variables like 'innodb_undo_log_truncate';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_log_truncate | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

--达到最大值后,回收undo表空间,默认1G
mysql> show variables like 'innodb_max_undo_log_size';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+
1 row in set (0.01 sec)

--回滚段,默认128个,每个段支持在线事物1024
--如果需要回收undo表空间, 如果是两个undo表空间,那每个undo表空间至少35个回滚段
mysql> show variables like 'innodb_rollback_segments';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_rollback_segments | 128   |
+--------------------------+-------+
1 row in set (0.01 sec)