understanding parallel replication in MySQL 5.7

Question

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:

1. started a long running INSERT in "Database1".
2. waited until the slave starts to execute the INSERT (show processlist).
3. 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 "Database1" 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.

Answer 1

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 SELECTs on 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.

• sebkoe: sadly, InnoDB is not an option. The whole Software that is using MySQL is optimized for using MyISAM. No chance this will change (in the near future). I agree that MyISAM Inserts block a table. In my example the first (long) insert was to DB1.TABLE1, while the second Insert was into DB2.TABLE2. So in my opinion a block cant be the problem here.
• Rick James: @sebkoe - But, for example, if in between there is a SELECT with a JOIN between both tables, that could chain the locks out for some time.
• sebkoe: also true, but not the case here, since my two queries are the only ones running.