Usage Description

Release Notes

This feature is released with Kunpeng Computing DC Solution 20.0.3.

Application Scenarios

When there are many write operations (such as update, insert, and delete) in the MySQL OLTP scenario, the global latch in the MySQL database may be the main factor that affects the throughput. After the MySQL fine-grained lock tuning feature is applied, if the Performance Schema shows that there is contention on trx_sys_mutex while the CPU usage is low, this feature can be used to alleviate the contention and improve the system throughput.

The MySQL lock-free tuning feature takes effect immediately after the patch is installed and the MySQL database is recompiled. You do not need to configure system variables.

Compilation and Installation Method

The MySQL lock-free 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.

1. Download the MySQL 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.

   MySQL 8.0.20 source code download address: https://github.com/mysql/mysql-server/archive/mysql-8.0.20.tar.gz

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

2. Download the fine-grained lock tuning patch and lock-free tuning patch to the root directory of the MySQL source code.
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. Optional: If the Yum source is not configured, configure it. For details, see Configuring the Yum Source.
5. Optional: If dos2unix is not installed, run the following command to install it:

   yum install dos2unix

6. Apply the patch of the MySQL fine-grained lock tuning feature, and then apply the patch of the MySQL lock-free tuning feature.

   This feature is based on MySQL fine-grained lock tuning. Therefore, the MySQL fine-grained lock tuning feature must be incorporated in advance.

   Make the fine-grained lock tuning patch and lock-free tuning patch take effect.

   1 2 3 4 5 6

   dos2unix 0001-SHARDED-LOCK-SYS.patch git apply --check 0001-SHARDED-LOCK-SYS.patch git apply --whitespace=nowarn 0001-SHARDED-LOCK-SYS.patch dos2unix 0002-LOCK-FREE-TRX-SYS.patch git apply --check 0002-LOCK-FREE-TRX-SYS.patch git apply --whitespace=nowarn 0002-LOCK-FREE-TRX-SYS.patch

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

7. Compile and install the MySQL source code. For details, see MySQL Porting Guide.
8. Optional: Perform a TPC-C test to obtain the performance improvement data after the MySQL lock-free tuning feature is used. For details about the test, see BenchMarkSQL Test Guide.

   This feature improves the sysbench write performance by 20%.

   Figure 1 Performance comparison before and after MySQL lock-free tuning is used