Usage Description

Fix vulnerabilities as soon as possible based on the Common Vulnerabilities and Exposures (CVE) of MySQL 8.0.20 on the MySQL official website.

Release Notes

This feature is released with Kunpeng BoostKit 21.0.0 and Kunpeng BoostKit 22.0.0, which correspond to MySQL 8.0.20 and MySQL 8.0.25, respectively.

Application Scenarios

When there are a large number of write operations (update, insert, and delete) in the OLTP load, plenty of redo log write requests are generated. The log write thread in the MySQL background may be overloaded, affecting the system throughput. If you find that the log thread is busy using log_on_write_waits in InnoDB Monitors, use this feature to improve the log thread efficiency. In addition, if the service is suitable for NUMA affinity, this feature can be used to improve the memory access efficiency of user threads on the multi-channel server with the NUMA architecture.

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

Restrictions

The libnuma library is required to implement this feature. For the libnuma library, the number of configured CPU cores for API calling cannot exceed the core pinning range for starting the parent process. Otherwise, a conflict occurs. In addition, the MySQL scheduler cannot detect the change of core pinning policy implemented by other tools. Comply with the following rules when using this feature:

When a MySQL instance is started, if a core pinning policy is set by using tools such as taskset and numactl, ensure that the configured MySQL scheduler parameters do not conflict with the core pinning policy. Otherwise, the MySQL instance fails to be started and an error log is output.
When a MySQL instance is running, if you want to modify the MySQL scheduler parameters, ensure that they do not conflict with the core pinning policy set by using tools such as taskset and numactl when the MySQL process is started. Otherwise, the MySQL instance continues to run, but the scheduler enters the fallback mode, stops responding to internal thread scheduling requests, and generates an alarm log.
After the MySQL instance is started, do not modify the thread pinning policy by means other than using MySQL. Otherwise, the MySQL instance continues to run, but the load information of the scheduler is inaccurate, which affects the scheduling performance.
If the value of SHOW STATUS LIKE 'Sched_affinity_group_number' is -1, the feature is disabled.

Compilation and Installation Method

The MySQL NUMA scheduling tuning feature is provided as a patch file. This patch is developed based on MySQL 8.0.20 or MySQL 8.0.25 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.

Download the MySQL source code based on Table 1 and upload it to the /home directory on the server.

**Table 1** Download URLs for different MySQL versions
Version	Download URL
MySQL 8.0.20	https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.20.tar.gz
MySQL 8.0.25	https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.25.tar.gz

Download the MySQL NUMA scheduling tuning patch based on Table 2 and upload it to the root directory of the MySQL source code.

**Table 2** Download URLs of the MySQL NUMA scheduling tuning patches
Version	Download URL
MySQL 8.0.20	https://gitee.com/kunpengcompute/mysql-server/releases/download/21.0.RC1.B031/0001-SCHED-AFFINITY.patch
MySQL 8.0.25	https://gitee.com/kunpengcompute/mysql-server/releases/download/KunpengBoostKit22.0.RC4.SchedAffinity/KunpengBoostKit22.0.RC4-CODE-SCHED-AFFINITY-FOR-MySQL-8.0.25.patch

Optional: If the Yum source is not configured, configure it. For details, see Configuring the Yum Source.
This feature depends on libnuma. Install related dependencies before compiling MySQL (take CentOS as an example):
```
yum install -y numactl numactl-devel numactl-libs
```
MySQL can still be compiled even if the libnuma dependencies are not found during compilation. But this feature will not take effect.
Upload the MySQL source package to the /home directory, decompress the source package, and go to the root directory of the MySQL source code. (Assume that the MySQL version is 8.0.20.)
```
cd /home
tar -zxvf mysql-boost-8.0.20.tar.gz
cd mysql-8.0.20
```

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.

            
                 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.

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

Optional: If dos2unix is not installed, run the following command to install it:
```
yum install dos2unix
```

Apply the NUMA scheduling tuning patch.

        
             dos2unix 0001-SCHED-AFFINITY.patch
git apply --check 0001-SCHED-AFFINITY.patch
git apply --whitespace=nowarn 0001-SCHED-AFFINITY.patch

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

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

After recompiling MySQL, configure system variables in the configuration file or boot parameters or during system running for the recompilation to take effect.

The MySQL system variables described in Table 3 are added, which can be set in the configuration file or boot parameters or during system running.

**Table 3** Parameter description and recommended configuration of MySQL NUMA scheduling tuning
Parameter	Description	Recommended Configuration
sched_affinity_numa_aware	A global parameter of the Boolean type. If it is set to ON and sched_affinity_foreground_thread is not left blank, the CPU cores specified by sched_affinity_foreground_thread are grouped by NUMA node, and the thread of a session is migrated only between CPU cores in a specified group. This parameter can be modified when the database is running. The default value is OFF.	Specifies whether to enable core binding for foreground processes. If sched_affinity_foreground_thread is not left blank, CPU cores specified by sched_affinity_foreground_thread are grouped by NUMA node, and the thread of a session is migrated only between cores in a specified group. You are advised to set this parameter to ON.
sched_affinity_foreground_thread	A global parameter of the String type. It is used to set the CPU cores that can be used for MySQL foreground threads. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that this type of threads is scheduled by the OS, that is, this parameter is not used.	Specifies the CPU cores on which MySQL foreground threads (user threads) run. You are advised to bind foreground threads and background threads to different cores.
sched_affinity_log_writer	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_writer thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_writer thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL log_writer thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_log_flusher	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_flusher thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_flusher thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL log_flusher thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_log_write_notifier	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_write_notifier thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_write_notifier thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL log_write_notifier thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_log_flush_notifier	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_flush_notifier thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_flush_notifier thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL log_flush_notifier thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_log_checkpointer	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_checkpointer thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_checkpointer thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL log_checkpointer thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_purge_coordinator	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL purge_coordinator thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the purge_coordinator thread is scheduled by the OS.	Specifies the CPU cores that can be used for the MySQL purge_coordinator thread. You are advised to bind background threads to cores of the same NUMA node.
sched_affinity_log_closer	A global parameter of the String type. It is used to set the CPU cores that can be used for the MySQL log_closer thread. The value is a character string consisting of digits representing core IDs. Core IDs can be separated by commas (,) and the value range can be represented by a minus sign (-). For example, the following lists valid values of CPU cores: Blank 5 0,5,7 0,2-5,7 This parameter can be modified when the database is running. It is left blank by default, indicating that the log_closer thread is scheduled by the OS. NOTICE: The log_closer thread is deleted from MySQL 8.0.25. Therefore, this parameter is not provided in the corresponding patch version.	Specifies the CPU cores that can be used for the MySQL log_closer thread. You are advised to bind background threads to cores of the same NUMA node.

Method 1: Modify the configuration file. This method takes effect only after the database is restarted.
1. Configure system variables in the configuration file. Example:
```
sched_affinity_numa_aware=ON
sched_affinity_foreground_thread=0-29
sched_affinity_log_writer=30
sched_affinity_log_flusher=30
sched_affinity_log_write_notifier=31
sched_affinity_log_flush_notifier=31
sched_affinity_log_checkpointer=31
sched_affinity_purge_coordinator=31
```
  The default path to the database configuration file is /etc/my.cnf. You can also run the following command to set the defaults-file option, where /tmp/myconfig.txt indicates the configuration file path.
```
mysqld --defaults-file=/tmp/myconfig.txt
```
2. Restart the database.

Method 2: Modify boot parameters.

When starting the database, add system variable configurations to the boot command. Example:

mysqld --defaults-file=/etc/my.cnf \
--sched_affinity_numa_aware=ON \
--sched_affinity_foreground_thread=0-29 \
--sched_affinity_log_writer=30 \
--sched_affinity_log_flusher=30 \
--sched_affinity_log_write_notifier=31 \
--sched_affinity_log_flush_notifier=31 \
--sched_affinity_log_checkpointer=31 \
--sched_affinity_purge_coordinator=31 \

Restart the database.

Method 3: Connect to the database during system running and configure system variables. This method does not require restarting the database. Example:

set global sched_affinity_numa_aware=ON;
set global sched_affinity_foreground_thread="0-29";
set global sched_affinity_log_writer="30";
set global sched_affinity_log_flusher="30";
set global sched_affinity_log_write_notifier="31";
set global sched_affinity_log_flush_notifier="31";
set global sched_affinity_log_checkpointer="31";
set global sched_affinity_purge_coordinator="31";

Optional: Check the MySQL status variables.

The MySQL status variables described in Table 4 are added in this feature, enabling you to query the internal status of the scheduling manager.

**Table 4** MySQL status variables
Status Variable	Description
Sched_affinity_status	Returns the load status of each group in the scheduling manager.
Sched_affinity_group_number	Returns the total number of NUMA nodes in the system.
Sched_affinity_group_capacity	Returns the number of cores on each NUMA node.

After the MySQL NUMA scheduling tuning feature is enabled, execute the following SQL statement to view information about MySQL status variables:

show status like "%status variable name%";

Optional: Perform a TPC-C test to obtain the performance improvement data after the MySQL NUMA scheduling tuning feature is used. For details about the test, see BenchMarkSQL Test Guide.
The MySQL NUMA scheduling tuning feature improves the comprehensive TPC-C performance by 10%. Figure 1 shows the effect before and after the tuning.
Figure 1 Performance comparison before and after MySQL NUMA scheduling tuning is used