MySQL Bugs: #73196: Allow ALTER TABLE to run concurrently on master and slave

Bug #73196	Allow ALTER TABLE to run concurrently on master and slave
Submitted:	4 Jul 2014 11:37	Modified:	6 Jul 2015 9:22
Reporter:	Andrew Morgan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
With online ALTER TABLE it is possible for the DDL operation to run for many hours while still processing DML on that same table. The ALTER TABLE is not started on the slave until after it has completed on the master and it will again take many hours to run on the slave. While the DDL runs on the slave, it is not possible for it to process any transactions which followed the ALTER TABLE on the master as they may be dependent on the changes that were made to the table's schema. This means that the slave will lag the master by many hours while the ALTER TABLE runs and then while it catches up on the backlog of DML sent from the master while that was happening.

If the master fails during this period then you're exposed as you have to wait for hours before having a slave that is current and can be promoted to be the new master. In addition, any reads sent to the slaves will be returning very stale data,

How to repeat:
Run DML traffic on the master on a very large table and then run ALTER TABLE at the same time. Observe how the slave lag increases.

Suggested fix:
Have the master signal to the slave(s) (for example through a new event in the binary log) that the ALTER TABLE has started running; based on this the slave could optimistically start running the same DML (in parallel with the continuing stream of DML from the master). When the master completes the ALTER TABLE then it signals this and the slave knows that it can now lock in that change *and* start processing replicated transactions that happened after the ALTER TABLE completed on the master.

Of course, if the on-line DML can also be speeded up then that will also help.

It could be something like this:
Run "ALTER t1 ... , BACKGROUND=1" on a master, which then returns directly and writes this statement to the binlog (and thus send it to the slave). 

Then the slave will start to build the index in the background also and presumably end in the same state.

I would then expect to find a thread which handles the ALTER in the processlist and/or p_s.threads.

And then killing the background thread should cancel the alter.

Another alternative:
Run "ALTER TABLE t1 ... , NOLOCAL=1" on the master which will validate the statement and put it in the binlogs, but without applying it locally. Then the slaves can do the alter and then on the master a SET SESSION sql_log_bin=0; ALTER TABLE... ; SET SESSION sql_log_bin=1; can be done.
This however will still block the SQL thread on the slave for some time, but maybe the slave can detect this and run this in parallel.

I think the BACKGROUND=1 type answer is quite a nice thing to do.
One thing that concerns me is how much I/O such a command might trigger, so pushing out a background task to N downstream slaves could potentially be rather damaging.
- As such perhaps some sort of I/O throttling mechanism might be useful to prevent the I/O generated from such a command saturating the server.

Hello Andrew,

Thank you for the feature request!

Thanks,
Umesh

Can we *please* get an update on the "triaging" of this major issue? Online ALTER TABLE is effectively useless for a tremendous amount of use cases until this is fixed.