Welcome to this article on MySQL technology shared by the GreatSQL community. If you have any questions or questions you would like to learn, please leave them in the comments section below

  • GreatSQL community original content is not allowed to use without authorization, please contact xiaobian and indicate the source.

background

In the project selection, MYSQL MGR cluster (GreatSQL 8.0.25) is deployed on Linux7.6 system of KVM(16c 16G ssd160G).

Sysbench was used to create 100 warehouses of data, and the tables were created as partition tables, and a 12-hour steady drop test was performed to evaluate the number of concurrent transactions that the corresponding architecture could support and the highest TPS/QPS.

After a continuous compression test with 256 concurrent disks for 12 hours, the SSD disk space usage of the node reached more than 95%. When I checked the log directory, the log directory reached 100 GB +. You can delete 1800s from binlog that is set to expire in a long time. The actual space occupied by binlog and relay group of MGR is about 11G, while du -sh * finds that the size of the log file is 71G and 4.1g, and the undo size of the three nodes of MGR is the same, so the space needs to be released urgently.

But does MySQL8.0 support the online substitution of type Oracle undo for shrinkage? Yes, and somewhat similarly.

Oracle /mysql undo tablespace automatic expansion, if there is a large number of DML operations, or for multiple large tables associated with the update time is long, it may be a short period of time to “stretch” the undo, oracle can create a new undo, Delete the bloated undo using drop to free up space by replacing it online.

Mysql 8.0 can also use this method to deal with large or long objects that are too large and take up too much space.

Methods the following

  • 1. Add the new undo file undo003. In mysql8.0, the default innodb_undo_TABLESPACE is 2. If the default innodb_undo_TABLESPACE is less than 2, it cannot be set to inactive, and undo created by default is protected and cannot be deleted.

  • 2. Set the expanded undo temporarily to INACTIVE and innodb_undo_LOG_TRUNCATE = ON to automatically release the expanded undo space.

  • 3. Set undo after space release to Active.

Specific operations are as follows

[greatdb@mysql ~]$mysql -ugreatsql -pgreatsql -h172.16.130.15 -p3307 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 74 Server version: 8.0.25-15 GreatSQL, Release 15, Revision C7FEAE175e0 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[(none)]> show variables like '%undo%'; +--------------------------+-----------------------------+ | Variable_name | Value | +--------------------------+-----------------------------+ | innodb_max_undo_log_size | 4294967296 | | innodb_undo_directory | /app/dbdata/sqlnode3306/log | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | innodb_undo_tablespaces | 2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.01 SEC)Copy the code

1. Check the undo size

Mysql/(none) > system du - sh/app/dbdata/datanode3307 / log/undo * 4.1 G/app/dbdata datanode3307 / log/undo_001 71 G / app/dbdata/datanode3307 / log/undo_002 -- -- -- -- -- 12 hours continuous pressure measurement stability, lead to node undo is too big, up to 71 gCopy the code

Add undo003 to undo tablespace undo003. By default, there are two undo pairs and the size is set to 4G

mysql[(none)]> mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo003.ibu'; Query OK, 0 rows affected (0.21 SEC) Create add new undo must end with. Ibu, Otherwise trigger the following error message mysql/(none) > create undo in tablespace undo003 add datafile '/ app/dbdata/datanode3307 / log/undo_003.'; ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.Copy the code

3. Query the undo tablespace information as follows:

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%'; +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- --+----------------+-----------------+----------------+---------------+------------+--------+ | SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 4294967279 | Innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | | 0 8.0.25 | 1 | | N active | | 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 76067897344 | 76068229120 | | 0 8.0.25 | | | 1 N Active | | 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | | 0 8.0.25 | 1 | | N active  | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 3 rows in the set (0.03 SEC)Copy the code

Innodb_undo_002 is 76067897344 (about 71G) and its state is active. Manually set it to INACTIVE so that it automatically triggers innodb_undo_LOG_TRUNCate reclamation.

mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;
Query OK, 0 rows affected (0.00 sec)
Copy the code

5. View the corresponding view

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%'; +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- -+----------------+-----------------+----------------+---------------+------------+--------+ | SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | 4294967279 Innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | | 0 8.0.25 | 1 | | N active | | 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2179072 | | 0 8.0.25 | 1 | | N empty | | 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | | 0 8.0.25 | 1 | | N active | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 3 rows in the set (0.01 SEC)Copy the code

You can check the size of undo_002 in the operating system directory. Innodb_undo_002 FILE_SIZE 16777216 The default size is 2179072 and the STATE is empty

Mysql/(none) > system du - sh/app/dbdata/datanode3307 / log/undo * 4.1 G/app/dbdata/datanode3307 / log/undo_001 2.1 M /app/dbdata/datanode3307/log/undo_002 16M /app/dbdata/datanode3307/log/undo003.ibuCopy the code

6. Set it to active again

mysql[(none)]> alter undo tablespace innodb_undo_002 set active; Query OK, 0 rows affected (0.01sec)Copy the code
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%'; +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- -+----------------+-----------------+----------------+---------------+------------+--------+ | SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | 4294967279 Innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | | 0 8.0.25 | 1 | | N active | | 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2195456 | | 0 8.0.25 | 1 | | N active | | 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | | 0 8.0.25 | 1 | | N active | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 3 rows in the set (0.01 SEC)Copy the code

The default undo tablespace is set to inactive. The default number of created undo tablespace is 2. If the number of created undo tablespace is less than 2, the following message is displayed:

mysql[(none)]> mysql[(none)]> show variables like 'innodb_undo_tablespaces'; +--------------------------+-----------------------------+ | Variable_name | Value | +--------------------------+-----------------------------+ | innodb_undo_tablespaces | 2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.01 SEC) mysql/(none) > alter undo in tablespace  innodb_undo_002 set inactive; ERROR 3655 (HY000): Cannot set innodb_undo_002 inactive since there would be less than 2 undo tablespaces left active. mysql[(none)]>Copy the code

8. The newly created and added objects can be dropped after being set to Inactive, as follows:

mysql[(none)]> alter undo tablespace undo003 set inactive;
Query OK, 0 rows affected (0.00 sec)
Copy the code
mysql[(none)]> drop undo tablespace undo003; Query OK, 0 rows affected (0.01sec)Copy the code
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%'; +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- -+----------------+-----------------+----------------+---------------+------------+--------+ | SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | 4294967279 Innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | | 0 8.0.25 | 1 | | N active | | 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2244608 | | 0 8.0.25 | 1 | | N active | +------------+-----------------+------+------------+-----------+---------------+------------+---------------+----------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC)Copy the code

conclusion

Through the above operations, we can manually release the two default undo tablespace sizes by adding temporary undo in the case that unoD encounters large transactions and undo continues to grow.

Of course, truncating UNDO tablespace files has some impact on database performance, so try to do it in relatively idle time.

When an UNDO tablespace is truncated, the rollback segment in the UNDO tablespace is disabled. Active rollback segments in other UNDO tablespaces are responsible for the overall system load, which may cause a slight performance degradation. The extent to which performance is affected depends on a number of factors:

  • 1, the number of UNDO tablespace
  • 2. UNDO amount of log data
  • UNDO tablespace size
  • 4, disk I/O system speed
  • 5. Existing long-running transactions

The easiest way to avoid potential performance impact:

  • Create undo TABLESPACE undo_XXX add datafile ‘/path/undo_xxx.ibu’; Add a few more UNDO tablespaces.
  • 2. Use high-performance SSDS to store data, REDO,UNDO, etc.
  • The reasons for the excessive expansion of UNDO are mostly due to the large amount of basic data, high business concurrency, frequent table association operations, large and long thing operations, which lead to UNDO being in active state all the time and unable to release the rollback segment in time. The problems caused by big things have a long history. Even if we can avoid 99% of big things, But when the actual business meets the rigid demand of 1% of big things, it requires our MySQL to run in and out of various scenarios, various architectures and business layers.

Enjoy GreatSQL 🙂

Article recommendation:

GreatSQL MGR FAQ mp.weixin.qq.com/s/J6wkUpGXw…

A # 12, MGR the whole cluster after hanging up, how can automatically choose the Lord, without manual intervention mp.weixin.qq.com/s/07o1poO44…

2021 data technology carnival “ON LINE” : the high availability of the MySQL architecture evolution and practice mp.weixin.qq.com/s/u7k99y6i7…

An SQL statement caught analysis of slow where mp.weixin.qq.com/s/AYibbzl86…

Answer # 15, what are the conditions may lead to MGR service cannot be started mp.weixin.qq.com/s/inSGpd0Q_…

Technology sharing | why MGR consistency mode is not recommended AFTER mp.weixin.qq.com/s/rNeq479RN…

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli Database, which focuses on improving the reliability and performance of MGR. It supports InnoDB parallel query feature and is a MySQL branch version suitable for financial applications.

Gitee: gitee.com/GreatSQL/Gr…

Making: github.com/GreatSQL/Gr…

Bilibili: space.bilibili.com/1363850082/…

Wechat &QQ Group: you can search to add GreatSQL Community Assistant wechat friends, send verification information “add group” to join GreatSQL/MGR communication wechat group

QQ group: 533341697 wechat assistant: WanliDBC

This article is published by OpenWrite!