Welcome to the MySQL technical articles shared by the great SQL community. If you have any questions or want to learn, you can leave a message in the comment area below. After seeing it, you will answer it
- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
Background introduction
In the project selection, Linux 7.0 of KVM (16C 16g SSD160G) is used 6. MYSQL MGR cluster (GreatSQL 8.0.25) is deployed on the system.
Create 100 warehouse data using sysbench, and conduct continuous 12-hour stable pressure test for the table created as partition table to evaluate the number of business concurrency that can be supported by the corresponding architecture and the maximum TPS/QPS.
After using 256 concurrent and continuous pressure test for 12 hours, it was found that the SSD disk space utilization rate of the node reached more than 95%. At that time, check the log directory for the first time, and the log directory has reached 100G +. It was thought that the binlog was not cleared in time due to the long time set by binlog. Clean the 1800s set by binlog expiration time, In fact, the relay group space of binlog and MGR is about 11G. When du -sh * looks at the log file size, it is found that one undo size is 71G and the other is 4.1G, and this is the case for the undo of the three nodes of MGR. It is urgent to free up space.
But mysql8 Does 0 support online replacement of undo of oracle type to shrink? The answer is yes, and it is somewhat similar.
The oracle/mysql undo tablespace is set to be automatically expanded. If there are DML operations running in batches or large tables in the business, which cause large things, or the associated update time for multiple large tables is long, the undo may be "expanded" in a short time. oracle can create a new undo and use drop to delete the expanded undo through online replacement to free up space.
mysql 8.0 can also use this method to deal with the situation that the undo caused by large or long things takes up too much space.
The method is as follows
- 1. Add a new undo file undo 003. mysql8. Default InnoDB in 0_ undo_ There are 2 tablespaces. If there are less than 2 tablespaces, it is not allowed to be set to inactive, and the undo created by default is protected and cannot be deleted.
- 2. Temporarily set the expanded undo to inactive and innodb_undo_log_truncate=on, automatically truncate to release the expanded undo space.
- 3. Set undo to active after the space is released again, which can be used online again.
The 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 set (0.01 sec)
1. View undo size
mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo* 4.1G /app/dbdata/datanode3307/log/undo_001 71G /app/dbdata/datanode3307/log/undo_002 -----12 Hour continuous stability pressure measurement, resulting in node failure undo Too large, up to 71 G
2. Add a new undo tablespace undo 003. The system defaults to 2 undo, with the size 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) Note: create add new undo Must be.ibu Otherwise, the following error prompt will be triggered mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo_003.' ; ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.
3. View the undo tablespace information in the system 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 set (0.03 sec)
4. View InnoDB in the above view_ undo_ 002 size reaches 76067897344 (about 71G) and its state is active. Manually set it to inactive to automatically trigger innodb_undo_log_truncate recycling.
mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive; Query OK, 0 rows affected (0.00 sec)
5. View the corresponding view 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 | | 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 set (0.01 sec)
You can view undo in the corresponding operating system directory_ 002 size, innodb_undo_002 FILE_SIZE 16777216, default size 2179072, STATE empty
mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo* 4.1G /app/dbdata/datanode3307/log/undo_001 2.1M /app/dbdata/datanode3307/log/undo_002 16M /app/dbdata/datanode3307/log/undo003.ibu
6. Reset it to the active state
mysql[(none)]> alter undo tablespace innodb_undo_002 set active; Query OK, 0 rows affected (0.01 sec)
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 set (0.01 sec)
7. Some people say why you can't directly set the expanded undo to inactive. The system creates two undo tablespaces by default. When the number of active tablespaces is less than two, you will be prompted as follows:
mysql[(none)]> mysql[(none)]> show variables like 'innodb_undo_tablespaces'; +--------------------------+-----------------------------+ | Variable_name | Value | +--------------------------+-----------------------------+ | innodb_undo_tablespaces | 2 | +--------------------------+-----------------------------+ 5 rows in set (0.01 sec) mysql[(none)]> alter undo 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)]>
8. The newly created and added can be normally set to inactive, and then deleted by drop, as follows:
mysql[(none)]> alter undo tablespace undo003 set inactive; Query OK, 0 rows affected (0.00 sec)
mysql[(none)]> drop undo tablespace undo003; Query OK, 0 rows affected (0.01 sec)
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 set (0.01 sec)
summary
Through the above operations, we can manually release the default two undo tablespaces of the system by adding temporary undo when unod encounters large transactions and the undo continues to grow.
Of course, truncating UNDO tablespace files has a certain impact on database performance. Try to do it in relatively idle time.
When the UNDO tablespace is truncated, the rollback segments in the UNDO tablespace are deactivated. Active rollback segments in other UNDO tablespaces are responsible for the overall system load, which may result in a slight performance degradation. The extent to which performance is affected depends on many factors:
- 1. Number of UNDO tablespaces
- 2. Amount of data that UNDO logs
- 3. UNDO tablespace size
- 4. Speed of disk I/O system
- 5. Existing long running transactions
So the easiest way to avoid potential performance impact:
- 1. create undo tablespace undo_XXX add datafile '/path/undo_xxx.ibu'; Add a few more UNDO tablespaces.
- 2. On the disk, if conditions permit, high-performance SSD is used to store data, REDO,UNDO, etc.
- Most of the reasons for the excessive expansion of UNDO are due to the large amount of basic data, high business concurrency, frequent table Association operations, large and long transaction operations, resulting in the active state of UNDO and the failure to release the rollback segment in time. The problems caused by big events have a long history, even if we can avoid 99% of big events, However, when the 1% of major events and rigid requirements are sent in the actual business, we have to run in various scenarios, architectures and business layers of MySQL.
Enjoy GreatSQL :)
Article recommendation:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...
Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...
Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...
Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili:
https://space.bilibili.com/13...
Wechat & QQ group:
You can search and 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 composed of blog one article multi posting platform OpenWrite release!