We are using Percona Server 5.7.20 on Debian 9. This is a master-master-replication and on one master we are running a highly parallel ETL 24/7. But slave on second master is not able to keep up. Here is my config on both nodes:
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=100
innodb_purge_threads=4
innodb_io_capacity=5000
innodb_log_file_size=512M
innodb_log_files_in_group=4
innodb_log_buffer_size=32M
innodb_file_per_table=1
innodb_file_format=Barracuda
binlog_format=ROW
log_slave_updates=1
slave_pending_jobs_size_max=512M
binlog_cache_size=10M
gtid-mode=on
enforce-gtid-consistency
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_group_commit_sync_delay=20000
binlog_row_image=minimal
sync_binlog=0
The table where most inserts and updates are going to is partitioned:
ALTER TABLE content DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `partner_id`, `partner_feed`);
ALTER TABLE content PARTITION BY KEY(partner_id, partner_feed) PARTITIONS 32;
So most of these parallel ETL processes execute DML statements on different partitions on this table (I’ve checked this with explain command). Can anyone confirm, if the replication improvements made in MySQL 5.7 take partitions into account? I.e. does partitioning help to improve replication speed?