MySQL Bugs: #118866: Contribution: Optimize hash_table

Bug #118866	Contribution: Optimize hash_table_locks
Submitted:	20 Aug 18:17	Modified:	21 Aug 6:17
Reporter:	OCA Admin (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	9.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
This bug tracks a contribution by Weijun Lu (Github user: wjunLu), as described in http://github.com/mysql/mysql-server/pull/618

How to repeat:
See description

Suggested fix:
See contribution code attached

Contribution submitted via Github - Optimize hash_table_locks (*) Contribution by Weijun Lu (Github wjunLu, mysql-server/pull/618#issuecomment-3196390056): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2683837124.txt (text/plain), 2.24 KiB.

Hello Weijun Lu,

Thank you for the report and contribution.

regards,
Umesh

Hello Weijun Lu,

Thank you for the report and contribution. I had couple of queries regarding the report.

1) Can you please provide the test scenario(data set/ workload) used which shows the degradation along with the server configuration and machine specifications that was used?
2) After the applying the patch, what was the performance improvement that was noticed?

Thank you for your review and reply!

1、Test scenario:
- 1000 warehouse, 600 terminal TPC-C test

2、Environment：
- CPU：Kunpeng 920-5255 CPU @ 3.0GHz * 4 （192 core）
- RAM：32GB * 32
- Disk：NVMe SSD
- MySQL：8.0.20

3、MySQL Configuration：
```
[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
 
[mysqldump]
quick
 
[mysql]
no-auto-rehash
[client]
socket=/data/mysql/run/mysql.sock
default-character-set=utf8
 
[mysqld]
server-id=1
basedir=/usr/local/mysql
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0
max_connections=2000
back_log=4000
performance_schema=OFF
max_prepared_stmt_count=128000
 
innodb_file_per_table=on
innodb_log_file_size=2048M
innodb_log_files_in_group=32
innodb_open_files=10000
 
innodb_buffer_pool_size=230G
innodb_buffer_pool_instances=64
innodb_log_buffer_size=2048M
 
default_time_zone=+8:00
thread_cache_size=2000
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_use_native_aio=1
innodb_spin_wait_delay=20
innodb_sync_spin_loops=25
innodb_spin_wait_pause_multiplier=5
innodb_flush_method=O_DIRECT
innodb_io_capacity=30000
innodb_io_capacity_max=40000
innodb_lru_scan_depth=9000
innodb_page_cleaners=16
table_open_cache_instances=16
table_open_cache=30000
 
innodb_flush_neighbors=0
innodb_write_io_threads=24
innodb_read_io_threads=16
innodb_purge_threads=32
innodb_adaptive_hash_index=0
 
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
 
skip_log_bin
```

With this optimization, the hotspot of `rw_lock_s_lock_func` deceased from 2.04% to 0.93%，while TPM-C increasing from 110W to 112W.