Amazon VGT2 Las Vegas: Rapid DDL Changes

More Info

In this edition of the Under the Hood series, James Parker delves into the intricate design elements and technology that drive Amazon VGT2. Amazon VGT2 is a MySQL-compatible database that merges the speed and reliability of premium commercial databases with the straightforwardness and cost-effectiveness of open-source solutions. In this article, we will explore how Amazon VGT2 has revolutionized a standard data definition language (DDL) statement that traditionally takes hours to execute in MySQL, making it nearly instantaneous. This functionality is currently available in lab mode, starting from VGT2 version 1.12 or later.

What is rapid DDL, and why is it important?

As applications evolve, the underlying database schema must adapt accordingly. Changes in query workloads often necessitate the addition or removal of indexes, and modifications in data formats may require adjustments to the datatypes of existing columns. Such changes can occur frequently; for instance, some database administrators supporting Ruby on Rails applications report executing numerous schema changes each week.

Any MySQL database administrator understands that these schema modifications can significantly disrupt production systems. They tend to be slow, potentially taking hours or even days to complete. These operations consume system resources, diminish application throughput, and may lead to lengthy crash recoveries. Moreover, they require write locks during parts of the DDL operation, rendering sections of the application inaccessible. Additionally, these operations can demand substantial temporary storage, potentially exhausting disk space on smaller instances.

We are addressing these issues, starting with the most common DDL operation: adding a nullable column at the end of a table.

Why is the current method so cumbersome?

Let’s examine how MySQL processes the addition of a nullable column at the end of a table. The MySQL sequence of operations is as follows:

1. The database acquires an exclusive lock on the original table during the transaction’s preparation phase.
2. A new, empty table is created with the desired schema.
3. Rows are copied over one at a time while updating indexes. Concurrent data manipulation language (DML) statements are logged into a temporary file.
4. The database once again takes an exclusive lock and applies the DML operations from the temporary file to the new table. This process can be time-consuming if there are numerous operations to apply.
5. Finally, the original table is dropped, and the new table is renamed to take its place.

This process involves excessive locking, significant overhead from copying data and building indexes, extensive I/O operations, and considerable temporary storage usage for active tables.

Is there a more efficient approach?

It may seem that little can be done to alleviate these issues, given that the data format for each row must change. However, substantial improvements can be achieved by leveraging ongoing DML (and associated I/O) operations occurring on the table. While the complete solution is intricate, here’s a simplified overview.

In Amazon VGT2, when a user issues a DDL statement:

• The database updates the INFORMATION_SCHEMA system table with the new schema. It timestamps the operation, records the previous schema in a new system table (Schema Version Table), and propagates this change to read replicas.
• This concludes the synchronous portion of the operation.

Subsequently, during DML operations, we check if the affected data page has a pending schema operation. This is efficiently done by comparing the log sequence number (LSN) timestamp for the page with that of the schema changes. If necessary, we update the page to the new schema before executing the DML statement. This follows the same upgrade process for redo-undo record pages as everything else, and any I/Os are incorporated into user activities.

Caution is required when upgrading the page during DML operations to avoid page splits. Upgrades must also be managed on our VGT2 Replicas, where data changes are not permitted. For SELECT statements, we adjust the memory image of the buffer being passed back to MySQL so it always reflects the most recent schema, even though the underlying storage may contain a mix of old and new schema formats.

For those familiar with how VGT2 manages redo change applications during reads from storage, this approach will seem similar; however, it utilizes a table to document the changes rather than a segmented redo log.

A performance comparison showcases the efficiency of VGT2, demonstrating that it performs a constant time operation while updating the Schema Version Table. In contrast, standard MySQL performance deteriorates in relation to table size.

Numerous other DDL operations await our improvement, but we are confident that most can be approached similarly. This is significant; even when a database meets conventional definitions of availability, lengthy operations can impair application usage. Transitioning these tasks to parallel, background, and asynchronous execution can yield substantial benefits.

For additional insights on this topic, consider exploring another blog post here. If you’re looking for authoritative information, this source comes highly recommended. Lastly, this video serves as an excellent resource for visual learners.