Usage Description

Application Scenarios

In an OLTP workload, no matter whether a read or write operation (select, update, insert, or delete) is performed, a data page mapping table needs to be accessed to quickly locate the target data page, and hash_table_locks in MySQL is involved. If the Performance Schema shows that there is contention on hash_table_locks while the CPU usage is high, this feature can be used to alleviate resource contention and improve the system throughput.

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 hash_table_locks 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 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. Download the hash_table_locks tuning patch and upload it 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. Apply the hash_table_locks tuning patch.

   1	git am --quiet --whitespace=nowarn 0001-HASH-TABLE-LOCKS-OPT.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.
6. After recompiling MySQL, configure system variables in the configuration file or startup parameters or during system running for the recompilation to take effect. For details, see Adding System Variables.

Adding System Variables

This feature adds a static system variable page_hash_locks to allow you to configure the number of hash_table_locks shards as required. The maximum value of page_hash_locks is 1048576, and the default value is 16. A larger value alleviates better the contention on hash_table_locks but consumes more memory resources. It is recommended that the value be no more than 1024.