mySQL 配置Undo表空间

配置Undo表空间
Undo日志可以存储在一个或多个Undo表空间中，而不是系统表空间。这种布局与缺省配置不同，缺省配置中undo日志驻留在系统表空间中。undo日志的I/O模式使得undo表空间很适合用于SSD存储，同时将系统表空间保存在硬盘存储上。Undo表空间或这些表空间中的单独段不能被删除。但是，存储在undo表空间中的undo日志可能会被截断。

要为MySQL实例配置单独的undo表空间，请执行以下步骤。假设在将配置部署到生产系统之前，您正在测试实例上执行这个过程。

undo表空间的数量只能在初始化一个新的MySQL实例时配置，因为innodb_undo_tablespaces选项只能在初始化时设置。在MySQL实例的生命周期中，指定的设置是固定的

Innodb_undo_tablespaces已弃用，并将在以后的版本中移除。

1.使用innodb_undo_directory配置选项为undo表空间指定一个目录位置。如果未指定目录位置，则在MySQL数据目录下创建undo表空间。

2.使用innodb_rollback_segments配置选项定义回滚段的数量。从一个相对较低的值开始，随着时间的推移逐渐增加，以检查对性能的影响。innodb_rollback_segments的默认设置是128，这也是最大值。

一个回滚段总是分配给系统表空间，32个回滚段保留给临时表空间(ibtmp1)。因此，如果需要为undo表空间分配回滚段，请将innodb_rollback_segments设置为大于33的值。例如，如果有两个undo表空间，将innodb_rollback_segments设置为35，为两个undo表空间分别分配一个回滚段。回滚段以循环方式分布在undo表空间之间。

当您配置单独的undo表空间时，系统表空间中的回滚段将呈现为非活动状态。

3.使用innodb_undo_tablespaces选项定义undo表空间的数量。该设置在MySQL实例的生命周期内是固定的，因此如果您不确定最佳值，请估计较高的值。

4.使用您选择的选项值创建一个新的MySQL测试实例

5.在数据量类似于生产服务器的测试实例上使用现实的工作负载来测试配置

6.对I/O密集型工作负载的性能进行基准测试

7.定期增加innodb_rollback_segments的值，并重新运行性能测试，直到I/O性能没有进一步的改善。

[mysql@localhost mysql]$ vi my.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1
secure-file-priv=
innodb_data_file_path =ibdata1:10M:autoextend
innodb_log_file_size=100m
innodb_log_files_in_group=3
innodb_undo_directory=/mysqldata/mysql/undo
innodb_undo_tablespaces=3
innodb_rollback_segments=36

[root@localhost bin]# ./mysqld  --defaults-file=/mysqlsoft/mysql/my.cnf --initialize --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --user=mysql
[root@localhost bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql
Generating a 2048 bit RSA private key
........................................................................+++
......................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.....+++
.................................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...+++
..................+++
writing new private key to 'client-key.pem'
-----
[root@localhost bin]# service mysqld start
Starting MySQL.. SUCCESS!

[mysql@localhost mysql]$ cat mysql.err
 100
 100
 100
2022-03-21T08:25:29.596495Z 0 [Warning] InnoDB: New log files created, LSN=48433
2022-03-21T08:25:30.651650Z 0 [Warning] InnoDB: The parameter innodb_undo_logs is deprecated and may be removed in future releases. Please use innodb_rollback_segments instead. See http://dev.mysql.com/doc/refman/5.7/en/innodb-undo-logs.html
2022-03-21T08:25:30.691183Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-03-21T08:25:30.832627Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7877044c-a8f0-11ec-be08-005056a390e6.
2022-03-21T08:25:30.834086Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-21T08:25:30.845375Z 1 [Note] A temporary password is generated for root@localhost: 8jr-,Xgpo3eH

[mysql@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password=password("xxzx7817600");
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc user;
ERROR 1046 (3D000): No database selected
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to root@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 -h 192.168.1.250 -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_undo%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| innodb_undo_directory    | /mysqldata/mysql/undo |
| innodb_undo_log_truncate | OFF                   |
| innodb_undo_logs         | 36                    |
| innodb_undo_tablespaces  | 3                     |
+--------------------------+-----------------------+
4 rows in set (0.00 sec)

截断Undo表空间
要截断undo表空间，MySQL实例必须至少配置两个undo表空间。至少有两个undo表空间，可以确保一个undo表空间保持活动状态，而另一个undo表空间被离线截断。undo表空间的数量由innodb_undo_tablespaces选项定义。缺省值为0。使用该语句检查innodb_undo_tablespaces的值
mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.00 sec)

innodb_undo_tablespaces已弃用，并将在以后的版本中移除。

启用Undo表空间截断功能
如果需要截断undo表空间，请启用innodb_undo_log_truncate。
mysql> SET GLOBAL innodb_undo_log_truncate=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

当启用innodb_undo_log_truncate时，undo表空间文件超过innodb_max_undo_log_size所定义的大小限制将被标记为截断。innodb_max_undo_log_size是动态全局变量，默认值为1024mib(1073741824字节)。
mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+
1 row in set (0.00 sec)

你可以使用SET GLOBAL语句来配置innodb_max_undo_log_size:
mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 2147483648 |
+----------------------------+
1 row in set (0.00 sec)

当启用innodb_undo_log_truncate时:
1.Undo表空间超过innodb_max_undo_log_size设置将被标记为截断。选择用于截断的undo表空间以循环方式执行，以避免每次截断相同的undo表空间。

2.驻留在所选的undo表空间中的回滚段是不活动的，这样它们就不会被分配给新的事务。允许当前使用回滚段的现有事务完成。

3.清除系统释放不再需要的回滚段

4.当undo表空间中的所有回滚段被释放后，执行truncate操作，undo表空间被截断到初始大小。undo表空间文件的初始大小为10MiB。在一个截断操作后，undo表空间的大小可能会超过10MiB，因为在操作完成后立即使用undo表空间。innodb_undo_directory选项定义了undo表空间文件的位置。缺省值为"."，表示默认情况下InnoDB创建其他日志文件的目录。
mysql> SELECT @@innodb_undo_directory;
+-------------------------+
| @@innodb_undo_directory |
+-------------------------+
| .   |
+-------------------------+
1 row in set (0.01 sec)

5.回滚段被重新激活，以便可以将它们分配给新的事务

加速截断Undo表空间文件
在回滚段被释放之前，undo表空间不能被截断。通常，清理系统每调用128次,就释放一次回滚段。为了加快undo表空间的截断，可以使用innodb_purge_rseg_truncate_frequency选项临时增加清理系统释放回滚段的频率。innodb_purge_rseg_truncate_frequency的默认值是128，这也是最大值:
mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.01 sec)

要增加清除线程释放回滚段的频率，请降低innodb_purge_rseg_truncate_frequency的值。例如:
mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                     32 |
+----------------------------------------+
1 row in set (0.00 sec)

在线截断Undo表空间文件对性能的影响
当undo表空间被截断时，该表空间中的回滚段暂时不激活。其他undo表空间中剩余的活动回滚段承担整个系统负载的责任，这可能会导致轻微的性能下降。性能下降的程度取决于许多因素，包括
.undo表空间的数量
.undo日志条数
.undo表空间大小
.I/O子系统速率
.现有的长期运行事务
.系统负载