A database clustering system for horizontal scaling of MySQL

Vitess supports non-blocking managed schema migrations based upon VReplication. vitess migrations. Vitessmigrations are powerful, reversible and fail agnostic. They use an asynchronous approach, which is less heavy on the database server. The implementation challenge of the asynchronous approach is how to cut-over with minimum impact on the user/app and without causing data loss. This post will take a deep look at the cut-over logic in vitess migrations.

Vitess migrations include:

  • Blocking & atomic to vitess clients
  • You are safe from data loss/data drift concerns

To understand better what this means, let’s first review what cut-over is.

This is the most important step in any online schema migration process. pt-online-schema-change, fb-oscTo. gh-ostFinally vitess.
Online schema migration (or online DDL) creates a shadow tableau, populates it, brings up the original table, then cuts-over by renaming both the original and shadow tables.

The cut-over is the point at which the tables must remain in perfect sync throughout the switch. No matter how the implementation goes, locks will be involved. This will impact the users/apps.
Vitess migrations use Asynchronous Change Propagation. They first look for changes to an original table in the binary logs. Then they apply those changes to the Shadow Table. This can lead to some latency before the changes are applied to the shadow tables. The shadow table may be slightly behind the original table at any given moment, and the tables could be out of sync.

However, the shadow table must be brought into sync with original table at cut-over. How do you do this?

All schema migration techniques employ some form of locking at cut over time. This locking causes queries to stall, and connections to spike.

Vitess uses the following generalized flow to bring the shadow tables up to speed with their original counterparts:

  • Verify that migration is in a safe state
  • Prevent writes to the original Table
  • Mark the time when writing was disabled
  • Consume binary logs to the maximum mark in time, then apply to the shadow table
  • Tables now in sync.

This post will be a reminder about how vitess prevents you from writing to the original table. It’s noteworthy that the flow can fail, or timeout, in which case vitess resumes writes and tries again at a later stage.

There’s a few techniques to preventing writes on the migrated table. They differ in several ways. Are they safe enough? Are they reversible? What is the effect on the app?

Take fb-osc’s approach: it runs a two-step cut-over, where it first renames the original table away, thus creating a puncture in the database, then renaming the shadow table in its place. There is a point in time where the table just doesn’t exist. This manifests itself as unexpected errors to users and apps. A bunch of queries suddenly fail, claiming that there is no such table. What if the tool crashes somewhere in between these two renames.

gh-ost’s approach is to lock the table via an elaborate mechanismThis allows for rollback in the case of error/timeout. It appears atomic to users/apps. Queries will block and pile-up, then be released to work on the new table. The logic relies upon internal MySQL lock prioritization. Some users have shown specific scenarios that would require prioritization. can fail.

Implementing vitess’s cut-over, we wanted the best of all worlds. The cut-over should appear atomic to the apps. The cut-over should not cause unexpected errors and, in worst cases, will block for a few moments. We also wanted to be certain of data integrity. This means that no write could occur on the original table while the shadow table is brought up to speed. The following is the breakdown.

Vitess offers the advantage of traffic going through VTGate, a MySQL compatible proxy. Apps and normal users do not communicate directly with MySQL. However, as explained below the cut-over logic can also be used for direct communication scenarios. Normally, queries are sent to VTGate which routes them to the appropriate shards. VTTablet will then execute the query on MySQL.

VTTablet offers the notion of ACLs (Access Control Lists). These lists were originally created to allow administrators to deny writing to tables.

Each query sent through VTTablet is assigned a query planning. The plan includes all ACLs associated to any table referenced by the query. VTTablet checks whether ACLs allow the query’s success at execution time. Then it executes the query against MySQL and returns the results.

Vitess migrations introduces an entirely new type of ACL: time restricted buffering. These are known as expiring rules. You can place a buffering clause on a table. Then you can cancel the rule or let it expire by itself.

The rule is active, but a query can be buffered or kept at bay while it is being processed. Either:

  • The rule will be active cancelled, meaning that buffering has been completed and the query continues to execute (assuming there are no conflicts with other ACLs).
  • In this case, the rule will expire automatically and the query will be rejected.

Cut-over time should take a few seconds, but more often two or three. Vitess sets a timer when cut-over starts. 10sBuffering ACL for the migrated table. Any new query can be placed into buffering for up 10 seconds. If the migration is complete by that time, the query will unblock and run on the new table. If not, the query fails.

Buffering only allows a set number of queries to wait. The buffer capacity can eventually be exhausted by heavy load, and queries will then fail. This is why it is crucial to complete the cut-over as quickly as possible.

But buffering is not the only solution.

  1. What happens to a query that was executed? Just beforecut-over and has validated its ACLs. If we place buffering ACLs now, it’s too late for that query. It continues to execute. How do we know?
  2. What happens if an automation runs a direct MySQL query? It’s not the normal vitess flow, but it can happen, and we’ve all been there.

To address any potential pending inquiries, the flow is temporarily halted for an additional 100ms. This is enough time to allow queries that have already passed pre-cut over ACLs to execute on the MySQL server.

This step increases overall cut-over for everyone, but lets the remaining queries execute before introducing any locking.

The step doesn’t guarantee anything, really. Although we believe it will give queries enough time to complete the step, as race conditions go, this is not a guarantee. SleepIt is not an answer.

This also has no effect on any queries that may be running directly on MySQL.

Next step is to RENAME original_table TO _____. It’s noteworthy that RENAMEWe will wait for any pending questions to be completed; therefore, any UPDATEStill in progress will be completed rather than failed.

But once you have the RENAMEThe puncture is done. The original table has been removed. It is impossible to modify the original table with any query. Normal app queries do not know about the puncture, and are still protected via ACLs.

We now mark our point in time (MySQL’s gtid_executed value).

Vitess continues to scan any remaining binary log entries up to the mark point in time, and then applies them onto the shadow tableau. We don’t expect many of those. When our binary log processing is in good condition and closely behind actual writes, we enter the cut-over procedure. We expect to have a second or two of catchup time.

Once the events are over, we will know the original as well as the shadow tables are in sync. We now RENAMEThe shadow table is used in place of the original table. We now have a new table! The puncture has been amended.

Finally, we clear the buffering aCL. Buffered queries can then be executed on the new table, unaffected by anything.

While VTTablet runs, it can roll back the cut-over operation at any time:

  • Do you want to fail before buffering starts? No problem, no harm done.
  • Failed to rename the original table? No problem! Undo ACLs and then try again
  • Failed to rename the shadow table in the same place as the original? No problem! Rename your original table, remove buffering, and try again later

What happens if VTTablet’s process fails while the puncture is in place, though? Here is the vitess Framework benefit. A new VTTablet process will be in charge. We expect that there will eventually be a VTTablet process, regardless of whether or not we failover on a new MySQL server. It will run recovery steps in case of an interrupted migration. It will resume any interrupted migration that was interrupted at the point of interruption.

VTTablet reviews the intended action before renaming the table. If the intended action fails, the new VTTablet reviews the state of interrupted migs and makes an audit. It then puts the original table back into place, thereby rolling back the entire cutover operation. The ACLs are stored in-memory so the new VTTablet doesn’t need to remove the buffering. The VTTablet then accepts the migration and allows it to run. It will attempt to cut-over as necessary in the natural order.

Vitess migrations can take advantage of the vitess framework to provide a multi-layered cut-over mechanism that includes ACLs and MySQL primitives. This allows users and apps to have the best experience possible while still having complete control over data accuracy.

Leave a Reply

Your email address will not be published.