Optional feature: regret cut-over

[shlomi-noach]

This feature was suggested by someone at the audience at PerconaLive Amsterdam 2016: when we cut-over, revert syncing and keep the old table in sync with incoming traffic onto the new table, and support the ability to cut-back.

Consider the case of a drop column or a drop index that were incorrectly planned and cause outage. We would be able to revert such an operation by cutting-back onto the original table and completely canceling the migration.

There's a lot involved here, but it is a possible task.

[zytek]

Yeah, it was me ;-) and I just came back here with the intention to post that feature request as we started evaluating gh-ost in my company.

Another usage scenarios: row_format=compressed and ability to rollback in case of performance degradation.

[shlomi-noach]

Updating that this is not on our immediate roadmap and though I've taken a quick look at it (and there's actually a beginning of a PR), I'm likely to not work on this in the coming month or maybe two.

[morgo]

For drop index - this is one of the use cases for invisible indexes (MySQL 8.0). Perhaps there is an opportunity to use similar nomenclature, or (when using 8.0) recommend this feature only for columns?

There exists a sister to invisible indexes called invisible columns. But there are no current plans to implement such. So I concur with the FR here that emulation is useful.

[jessbreckenridge]

At Percona Live 2017, we brainstormed a possible solution for this, that would be perhaps less intensive.

The idea is as follows:

• When a gh-ost migration is run, a gh-ost migration would be run as normal, from the old table to table the new table.
• When the cutover happens, we start a new gh-ost migration... from new table to the old table.
• This old table is kept for X amount of time, for example 24 hours.
• If you regret what you have done, we can cutback to the original table as normal.

Pros:

• Uses capabilities that gh-ost is already capable of. This is simply another migration.
• Parity in load, disk space used, etc. The migration should be no more intensive than the migration that just completed.

Cons:

• Tooling to properly synchronously run the cutover, and start the new migration into the old table would be needed.
• Please add and discuss.

[tomkrouper]

@jonahberquist we discussed an alternate to this, but I didn't want to get your details wrong, can you post your thoughts here?

[leeparayno]

@jessbreckenridge I was thinking about that exact thing when watching the gh-ost presentations at Percona Live this week.

However, in the scenario @shlomi-noach mentioned where a drop column was performed, presumably that would potentially be breaking any queries attempting to insert/update data in that column--leaving only the fraction of queries that would have been successful (queries without that column specified) and logged to the binlogs to actually let gh-ost push the data over to the old table.

I suppose most of the data is better than none of it, when recovering.

[shlomi-noach]

@leeparayno the case for a dropped column is indeed extreme. We are extremely careful with dropping columns and it never happened that we dropped a column that had useful data.

However it did happen that some code was still referring to that column (in the form of select c1, c2, c3...) while not actually using it (I'm not a ruby expert so unsure that I'll describe it correctly; there was inheritance and implicit defaults involved). In that incident an engineer was required to investigate the error. It would have immensely helped if we (as database engineers) could just revert the migration on our own, letting the engineers figure out the problem while not suffering incident/outage/service issues.

[jonahberquist]

Thank you for getting this conversation rolling again, @jessbreckenridge. As we discussed briefly and @tomkrouper mentioned, I think we can improve this flow even more. With a tweak to the implementation, we can eliminate the need to start up the reverse migration synchronously with the cut-over.

At the point of the cutover, the original table and the ghost table are in sync. Any writes to the newly altered table will be in the binary logs after that point and can be applied to the _del table later to bring the old table back into sync. If we keep track of the cut-over point in the binary logs (or find it again later by looking for the table rename), we can start up a gh-ost process later that only does the apply portion of the operation (and does it from the normal table to the _del table.

The flow changes so that if one regrets the cutover, they'll start up the gh-ost --regret operation which will have to process through all writes that have happened since the cutover. That may increase the time from deciding to regret to flipping the tables back, but it takes straggling post-cutover gh-osts from every migration out of the picture.

[shlomi-noach]

I'd just like to say how proud I am to be working with you people.

The idea suggested by @jessbreckenridge and refined by @jonahberquist elegantly solves what would be complex logic within the gh-ost code.

To elaborate on what it would take to make that happen:

• The cutback migration (aka regret, aka 2nd migration) will need to know what to cutback.
• It would need to know where to sync from
• It would need extra info, elaborate shortly.
• Would run with --regret/cutback option
• It would not need to copy rows. Rather, it would cheat its way to saying "oh yeah, I just completed copying all rows"
• It would need to know coordinates at time of cut-over of the 1st (normal) migration.
  • Of interest, the cut-over of the 1st migration doesn't lock all writes, but only writes to the migrated table. It's fine. Any coordinates taken from the inspected replica in the duration of the cut-over are legit.
  • 1st migration will need to show master status on inspected server at time of cut-over
  • It is possible, as @jonahberquist suggested, to search the binary logs for those coordinates. However as we will see shortly there's a bunch of additional information we need to pass to the cutback migration, so we may just as well provide the binlog coordinates anyhow.
• The cutback migration must use the same inspected server
• The cutback migration will not use a --alter option
  • It is therefore ignorant of renames, of drop+add
  • The 1st migration will need to export:
    • rename mapping (reversed?)
    • shared column listing
    • inspector binlog coordinates at time of cut-over
    • Name of old table. This may just be _mytable_del but if using -timestamp-old-table this would be an arbitrary name.
• The 1st migration will export said data to json file, potentially via some --cutback-info-file=...
• cutback migration will use the 1st migration's old table as the ghost table.

This list is long, but it's mostly logistics and very little actual logic.

[tomkrouper]

As we move towards wanting to automate schema migrations more, I have the slight worry that some issues will get missed and dropping an index that gets the 👍 when it shouldn't have. This could cause major problems. Having this feature implemented could alleviate that fear. Any chance this could get back on the roadmap?

[shlomi-noach]

Let's discuss in our next meeting!

[jonahberquist]

Following up from our meeting, we're not looking to address this immediately but will revisit during the next planning phase.