Bug #75396 Allow slave_preserve_commit_order without log-slave-updates.
Submitted: 2 Jan 2015 14:46 Modified: 2 Mar 2020 10:14
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.7.5 OS:Any
Assigned to: CPU Architecture:Any

[2 Jan 2015 14:46] Jean-François Gagné
Description:
Hi,

the new option in MySQL 5.7.5 to preserve commit order on slaves (slave_preserve_commit_order) is very useful to make sure that the slave will never enter a state that the master was not in when using multi-threaded slaves.

Sadly, to enable it, the slave must have log-slave updates.

I would like to enable multi-threaded slaves, with slave_preserve_commit_order, but without log-slave-updates.  Can this be implemented for the GA version of 5.7 ?

Many thanks,

JFG

How to repeat:
Not exactly a bug, more a feature request, see description.
[23 Jun 2015 17:25] Luis Soares
Hi Jean-François,

Thanks for the feature request.

Can you please detail a use case scenario where this option would
make a difference?

Regards,
Luís
[25 Jun 2015 10:32] Jean-François Gagné
Thanks for looking into that Luís.

If I have T1 and T2 committing this way on the master (B for Begin, C for Commit):

   ------------Time------------>
T1:  B----------------------C
T2:                    B-----C

T1 is a longer transaction than T2, but T1 and T2 commit about at thew same time (T1 before T2).

T1 and T2 might be executed in parallel on the slave.  If they do, T2 will probably be ready to commit on the slave before T1 as shown below (T2 shorter than T1).

   ------------Time------------>
T1:  B----------------------C
T2:  B-----?C?

If slave_preserve_commit_order is not enabled, T2 will commit and its result will become visible before the result of T1.  This state has never materialized on the master.  Moreover, this state does not materialized in single-threaded replication.

To avoid materializing a new state on slaves where parallel replication is enabled, I want to use the option slave_preserve_commit_order.  However, to use that option, I need to enable log-slave-updates.  _I do not need log-slave-updates on most of my slaves_.  Requiring to enable log-slave-updates only to be able to use parallel replication does not please me: it consumes more IOs, slows-down replication, consumes more disk space, and complexify my architectures.  I would prefer to be able to use parallel replication in a safe way without log-slave-updates.

Moreover, in addition to materializing a new state on the slave, running parallel replication without slave_preserve_commit_order creates gaps in transaction execution on the slave.  As stated in https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_preser...:

"If slave_preserve_commit_order is not enabled, after stopping a multi-threaded slave there is a chance that there will be gaps in the sequence of transactions that have been executed from the slave's relay log, [...]. This can be caused when the multiple threads do not all stop in synchrony, which means that there may be transactions after Exec_Master_Log_Pos (shown using SHOW SLAVE STATUS) which have been committed, and there may be gaps consisting of non-committed transactions that appear between Exec_Master_Log_Pos and the committed transactions. Enabling this option ensures that such gaps cannot occur, and therefore no transactions after Exec_Master_Log_Pos can have been committed."

So I would also like to use slave_preserve_commit_order, WITHOUT log-slave-updates, to make sure no such gap happen.

Many thanks,

JFG
[19 Oct 2018 18:43] Online Operations
I've been testing with just the least controversial variables and slave replication performance seems to be much improved:

SET GLOBAL slave_parallel_workers=4;
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_preserve_commit_order=1;

I don't really understand how MySQL takes advantage of multithreading with slave_preserve_commit_order=1. Can you help me understand this?
[19 Oct 2018 18:45] Online Operations
Sorry, I actually posted my question in the wrong spot. It can be deleted.
[2 Mar 2020 10:14] Jean-François Gagné
I think this is fixed in 8.0.19:
https://mysqlhighavailability.com/preserve-commit-order-on-binlogless-replicas/

I closed this bug.