Usage Description

Application Scenarios

When an undo tablespace truncation occurs in the MySQL database and the target undo tablespace is large, undo_spaces_lock is contended for. If the Performance Schema and any visualization tool (such as dimSTAT) show there is contention on undo_spaces_lock while the system throughput fluctuates during undo tablespace truncation, use this feature to alleviate the contention and performance fluctuation.

After the patch is applied, recompile the MySQL database and configure system variables for the patch to take effect. For details, see Adding System Variables.

Compilation and Installation Method

The MySQL undo_spaces_lock tuning feature is provided as a patch file. This patch is developed based on MySQL 8.0.20 and is open-sourced in the Gitee community. Before using this feature, apply the patch to the MySQL source code, and then compile and install MySQL.

• The features used for tuning are provided in patches. Apply the patch in the MySQL source code, and then compile and install the MySQL database.
• The patch is developed for MySQL 8.0.20.

1. Download the MySQL 8.0.20 source package, upload it to the /home directory on the server and decompress it, and then go to the root directory of the MySQL source code.

   cd /home
   tar -zxvf mysql-boost-8.0.20.tar.gz
   cd mysql-8.0.20

2. Decompress the source package and go to the MySQL source code directory.

   tar -zxvf mysql-boost-8.0.20.tar.gz
   cd mysql-8.0.20

3. In the root directory of the source code, run the git init command to create Git management information.

   git init
   git add -A
   git commit -m "Initial commit"

   

   • Generally, Git is provided by the system. If not, configure the Yum source by following instructions in MySQL Porting Guide and then install Git.

     1	yum install git

   • If the Git commit user information is not configured, configure the user email and user name before running the git commit command.

     1 2	git config user.email "123@example.com" git config user.name "123"

4. Apply the patch.
   • If this feature is not used together with the MySQL NUMA scheduling tuning feature, download the undo_spaces_lock tuning patch to the root directory of the MySQL source code, and run the following command to make the patch take effect:

     1	git am --quiet --whitespace=nowarn 0001-UNDO-SPACES-LOCK-OPT.patch

     If no error information is displayed, the patch is successfully installed.

   • If this feature needs to be used together with the MySQL NUMA scheduling tuning feature, the MySQL NUMA scheduling tuning feature must be incorporated before this feature.
     Download the NUMA scheduling feature patch and undo_spaces_lock tuning patch to the root directory of the MySQL source code. Then run the following command to make the patches take effect:

     1	git am --quiet --whitespace=nowarn 0001-SCHED-AFFINITY.patch 0002-UNDO-SPACES-LOCK-OPT.AFTER-SCHED-AFFINITY.patch

     If no error information is displayed, the patch is successfully installed.

5. Compile and install the MySQL source code. For details, see MySQL Porting Guide.

Adding System Variables

This feature adds a dynamic system variable innodb_undo_spaces_snapshot_tickets. The default value is 0, indicating that the innodb_undo_spaces_snapshot_tickets feature is disabled. The maximum value is 1048576.

Three InnoDB monitors are added to facilitate innodb_undo_spaces_snapshot_tickets tuning.

Example

innodb_undo_spaces_snapshot_tickets controls the maximum number of times that the undo tablespaces snapshot generated during a single undo tablespace truncation process can be queried. For example, to alleviate the contention on undo_spaces_lock, set innodb_undo_spaces_snapshot_tickets to a large value, for example, 100000, run the load again, and observe the InnoDB monitors.

• undo_truncate_snapshot_ticket_grant_count indicates the number of times that the DML thread successfully reads the snapshot. This many transactions would be blocked by the purge coordinator before tuning.
• undo_truncate_snapshot_ticket_try_count indicates the contention status when multiple DML threads read the snapshot. In most cases, the value of undo_truncate_snapshot_ticket_try_count is equal to or slightly greater than that of undo_truncate_snapshot_ticket_grant_count, indicating no contention.
• If the value of undo_truncate_snapshot_ticket_wait_count is small (close to 0) in most cases, the time in the critical region of the purge coordinator is not affected. In this case, if the value of undo_truncate_snapshot_ticket_grant_count is close to that of innodb_undo_spaces_snapshot_tickets, increase the value of innodb_undo_spaces_snapshot_tickets.
• If the value of undo_truncate_snapshot_ticket_wait_count is large (over 1000 in this example), decrease the value of innodb_undo_spaces_snapshot_tickets to balance the gains of the DML thread and the consumption of the purge coordinator.