Is there a mysql feature to allow parallel replication per query?

Question

When adding columns or indexes to big tables in a master salve setup, replication lag occurs.

My understanding is that replication occurs in a single thread on the slave, and statements get executed in FIFO or queue structure, and this is done to preserve the integrity of data.

So if unique constraint is added on master, and then new data is added with duplicate values, the slave should first add the unique constraint before adding the data to avoid issues.

However there are many use cases where this kind of integrity is not required. For example if I add a secondary index to a table, there is no need for this type of single thread, I would like it to continue inserting other data and replicating even if the index replication is not done yet, avoiding replication lag.

So to my question, is there a command such as:

SET sql_log_bin_blocking=0;
ALTER TABLE user ADD INDEX `index1` (`age`);
SET sql_log_bin_blocking=1;

This query would get replicated to all slaves, but it would not be blocking and not cause replication lag.

Is there something like that currently?

Answer 1

The solution that will truly allow no replication lag is an OSC tool like pt-online-schema-change or gh-ost.

• You can read and write to the table while the ALTER is running either on the source or replica instances.
• They run the ALTER concurrently on the source and replica instances. That is, the ALTER starts on the replica soon after it starts on the source; it doesn't have to wait for it to finish on the source before it starts on the replica, which is how DDL is replicated normally.

There is no way to make an alter table run in parallel in current versions of MySQL. Version 8.0 introduced the beginnings of parallel query execution, but so far it is only used for a very limited case: clustered index reads, which improves performance CHECK TABLE and SELECT COUNT(*) with no where clause. See https://www.percona.com/blog/2019/01/23/mysql-8-0-14-a-road-to-parallel-query-execution-is-wide-open/. It will take many more years before this is implemented for all operations.

You could run the ALTER TABLE on the source without writing the change to the binary log, and then run the same ALTER TABLE directly on the replica. But there is necessary table locking during any DDL statement, and this will block writes to the table anyway, so it is likely to result in replication lag (unless you are never writing to that table anyway).

The parallel replication feature in MySQL does not allow a given change to be run in parallel, and it does not eliminate the table-locking required by DDL statements.

Answer 2

• Newer versions of MySQL have a way to have multiple slave execution threads. But, beware, an ALTER being executed by one thread will block other treads from doing some actions with that table. The latest version allows some flavors of ADD COLUMN and ADD/DROP INDEX are virtually non-blocking. And they run faster, so they are clogging the replication stream (FIFO) for less time.

• Newer versions of MySQL have better ALTERs -- they allow

• See pt-online-schema-change as a relatively efficient way to do certain ALTERs. It may help you.

• Consider running the ALTER separately on each server; see https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sql_log_bin

    # on Primary server:
    SET sql_log_bin = OFF;
    ALTER ...;
    SET sql_log_bin = ON;
  
    # On Replica, one at a time, perhaps with each offline:
    ALTER ...;     # (assuming there are not Replicas hanging off this Replica)
  

• Vertical partitioning can avoid needing to ADD COLUMN. (Less invasive, but not 'clean'.)

FIFO... Before about 5.6, yes, the replication stream was single-threaded. After that, replication became more sophisticated. First it would allow some parallel execution when two queries did not touch the same database. The latest versions allow some forms of parallelism as long as it still keeps the Replica identical to the Primary. That is, 'preserve the integrity of data'. References:

• https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/
• https://dev.mysql.com/doc/refman/5.7/en/replication-implementation-details.html (and other pages)