Bug #88328 Performance degradation with the slave_parallel_workers increase.
Submitted: 2 Nov 2017 9:13 Modified: 19 Dec 2017 17:32
Reporter: ashe sun (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: Bogdan Kecman CPU Architecture:Any

[2 Nov 2017 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.
[19 Dec 2017 17:32] Bogdan Kecman
Hi,
different load conditions will have different impact on your system. There is no silver bullet that solves all issues so you can't expect parallel replication to solve them all. Proper configuration and optimization of your system are beyond the scope of bugs system. You are free to contact our support & consulting team to help you optimize your system.

best regards
Bogdan