Bug #88328 Performance degradation with the slave_parallel_workers increase.
Submitted: 2 Nov 9:13
Reporter: ashe sun Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to:

[2 Nov 9:13] ashe sun
Description:
The semi-sync-replication seems like  so cool with slave parallel workers.But in my test case, when I increase d the slave_parallel_workers, the master became slowly. Maybe mutex contention lead to this.

How to repeat:
master my.cnf
[mysqld]
gtid-mode = ON
enforce-gtid-consistency = 1
server_id = 10813308
basedir = /data/mysql/base
datadir = /data/mysql/data/13308
socket = /tmp/mysql_13308.sock
port = 13308
log-bin = mysql-bin
tmpdir = /data/mysql/mysql_tmp
skip-name-resolve = 1
max_allowed_packet = 64M
read_only = 0
default_storage_engine = InnoDB
character_set_server = utf8
skip-external-locking = 1
table_open_cache_instances = 16
back_log = 1500
wait_timeout = 3600
interactive_timeout = 3600
default-time-zone = '+8:00'
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
symbolic-links = 0
secure_file_priv = ''

innodb_data_file_path = ibdata1:1G:autoextend
max_connections=4000
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16

innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000

innodb_buffer_pool_size=32000M
innodb_buffer_pool_instances=32
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=256K

innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
innodb_spin_wait_delay= 6

innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0

innodb_monitor_enable = '%'
performance_schema=OFF
sync_binlog=1

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
gtid-mode = ON
enforce-gtid-consistency = 1
skip-external-locking = 1
slave_pending_jobs_size_max=268435456

slave my.cnf
[mysqld]
gtid-mode = ON
enforce-gtid-consistency = 1
server_id = 108133081
basedir = /data/mysql/base
datadir = /data/mysql/data/13308
socket = /tmp/mysql_13308.sock
port = 13308
log-bin = mysql-bin
tmpdir = /data/mysql/mysql_tmp
skip-name-resolve = 1
max_allowed_packet = 64M
read_only = 0
default_storage_engine = InnoDB
character_set_server = utf8
skip-external-locking = 1
table_open_cache_instances = 16
back_log = 1500
wait_timeout = 3600
interactive_timeout = 3600
default-time-zone = '+8:00'
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
symbolic-links = 0
secure_file_priv = ''

innodb_data_file_path = ibdata1:1G:autoextend
max_connections=4000
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16

innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000

innodb_buffer_pool_size=32000M
innodb_buffer_pool_instances=32
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=256K

innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
innodb_spin_wait_delay= 6

innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0

innodb_monitor_enable = '%'
performance_schema=OFF
sync_binlog=1

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-slave-enabled = 1
gtid-mode = ON
enforce-gtid-consistency = 1
skip-external-locking = 1
slave_pending_jobs_size_max=268435456

relay-log-info-repository = TABLE
relay_log_recovery = 1
master_info_repository = TABLE
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
log_slave_updates=on
slave_preserve_commit_order=on

1,Test master with different slave's slave_parallel_workers.  the results  will show the differences.