understanding parallel replication in MySQL 5.7
I am currently experimenting with parallel replication in MySQL (5.7).
I have set up a replication between master and slave (MyISAM, statement based) and did also set the following settings for parallel replication on the slave:
log_slave_updates=1 log-bin=/home/mysqlbinlogs/mysql slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=7 slave_preserve_commit_order=ON
There are two databases. "Datebase1" and "Database2".
To test the parallel replication I have now done the following:
- started a long running INSERT in "Database1".
- waited until the slave starts to execute the INSERT (show processlist).
- started a fast INSERT in "Database2".
My expectation was that the INSERT in "Database2" is executed directly on the slave despite the INSERT in "Database1" still running on the slave.
Instead, the INSERT in "Database2" was executed on the slave only after the INSERT in "Database2" was done on the slave.
Where is my (understanding) error here?
Background: we have the problem in production that data on the slave is written too late due to long running inserts into another database. My guess was that this is because replication is processing the data sequentially and not in parallel.
MyISAM may the main problem. Any write (including replication writes) to a table blocks all other writes and, to some extent, even reads. Hence, the "parallel" replication threads are sometimes blocking each other. And even
SELECTson the Replica are blocked and/or block replication.
I strongly encourage you to switch to InnoDB, then come back if you still have issues.
InnoDB transactions and replication are better kept in order even without completely stalling other replication threads.