Vertical Split enables you to move a subset of tables to their own keyspace. Continuing on from the ecommerce example started in the get started guide, as your database continues to grow, you may decide to separate the customer and corder tables from the product table. Let us add some data into our tables to illustrate how the vertical split works. Paste the following:

mysql < ../common/insert_commerce_data.sql

We can look at what we just inserted:

mysql --table < ../common/select_commerce_data.sql
Using commerce/0
Customer
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | alice@domain.com   |
|           2 | bob@domain.com     |
|           3 | charlie@domain.com |
|           4 | dan@domain.com     |
|           5 | eve@domain.com     |
+-------------+--------------------+
Product
+----------+-------------+-------+
| sku      | description | price |
+----------+-------------+-------+
| SKU-1001 | Monitor     |   100 |
| SKU-1002 | Keyboard    |    30 |
+----------+-------------+-------+
COrder
+----------+-------------+----------+-------+
| order_id | customer_id | sku      | price |
+----------+-------------+----------+-------+
|        1 |           1 | SKU-1001 |   100 |
|        2 |           2 | SKU-1002 |    30 |
|        3 |           3 | SKU-1002 |    30 |
|        4 |           4 | SKU-1002 |    30 |
|        5 |           5 | SKU-1002 |    30 |
+----------+-------------+----------+-------+

Notice that we are using keyspace commerce/0 to select data from our tables.

Create Keyspace #

For a vertical split, we first need to create a special served_from keyspace. This keyspace starts off as an alias for the commerce keyspace. Any queries sent to this keyspace will be redirected to commerce. Once this is created, we can vertically split tables into the new keyspace without having to make the app aware of this change:

./201_customer_keyspace.sh

This creates an entry into the topology indicating that any requests to master, replica, or rdonly sent to customer must be redirected to (served from) commerce. These tablet type specific redirects will be used to control how we transition the cutover from commerce to customer.

Customer Tablets #

Now you have to create vttablet instances to back this new keyspace onto which you’ll move the necessary tables:

./202_customer_tablets.sh

The most significant change, this script makes is the instantiation of vttablets for the new keyspace. Additionally:

• You moved customer and corder from the commerce’s VSchema to customer’s VSchema. Note that the physical tables are still in commerce.
• You requested that the schema for customer and corder be copied to customer using the copySchema directive.

The move in the VSchema should not make a difference yet because any queries sent to customer are still redirected to commerce, where all the data is still present.

VerticalSplitClone #

VerticalSplitClone has been deprecated in version 12.0. In older versions the process would continue with:

./203_vertical_split.sh

starts the process of migrating the data from commerce to customer.

For large tables, this job could potentially run for many days, and may be restarted if failed. This job performs the following tasks:

• Dirty copy data from commerce’s customer and corder tables to customer’s tables.
• Stop replication on commerce’s rdonly tablet and perform a final sync.
• Start a filtered replication process from commerce->customer that keeps the customer’s tables in sync with those in commerce.

NOTE: In production, you would want to run multiple sanity checks on the replication by running SplitDiff jobs multiple times before starting the cutover.

We can look at the results of VerticalSplitClone by examining the data in the customer keyspace. Notice that all data in the customer and corder tables has been copied over.

mysql --table < ../common/select_customer0_data.sql
Using customer/0
Customer
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | alice@domain.com   |
|           2 | bob@domain.com     |
|           3 | charlie@domain.com |
|           4 | dan@domain.com     |
|           5 | eve@domain.com     |
+-------------+--------------------+
COrder
+----------+-------------+----------+-------+
| order_id | customer_id | sku      | price |
+----------+-------------+----------+-------+
|        1 |           1 | SKU-1001 |   100 |
|        2 |           2 | SKU-1002 |    30 |
|        3 |           3 | SKU-1002 |    30 |
|        4 |           4 | SKU-1002 |    30 |
|        5 |           5 | SKU-1002 |    30 |
+----------+-------------+----------+-------+

Cut over #

Once you have verified that the customer and corder tables are being continuously updated from commerce, you can cutover the traffic. This is typically performed in three steps: rdonly, replica and master:

For rdonly and replica:

./204_vertical_migrate_replicas.sh

For master:

./205_vertical_migrate_master.sh

Once this is done, the customer and corder tables are no longer accessible in the commerce keyspace. You can verify this by trying to read from them.

mysql --table < ../common/select_commerce_data.sql
Using commerce/0
Customer
ERROR 1105 (HY000) at line 4: vtgate: http://vtgate-zone1-5ff9c47db6-7rmld:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = FailedPrecondition desc = disallowed due to rule: enforce blacklisted tables (CallerID: userData1)

The replica and rdonly cutovers are freely reversible. However, the master cutover is one-way and cannot be reversed. This is a limitation of vertical resharding, which will be resolved in the near future. For now, care should be taken so that no loss of data or availability occurs after the cutover completes.

Clean up #

After celebrating your first successful ‘vertical resharding’, you will need to clean up the leftover artifacts:

./206_clean_commerce.sh

Those tables are now being served from customer. So, they can be dropped from commerce.

The ‘control’ records were added by the MigrateServedFrom command during the cutover to prevent the commerce tables from accidentally accepting writes. They can now be removed.

After this step, the customer and corder tables no longer exist in the commerce keyspace.

mysql --table < ../common/select_commerce_data.sql
Using commerce/0
Customer
ERROR 1105 (HY000) at line 4: vtgate: http://vtgate-zone1-5ff9c47db6-7rmld:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = InvalidArgument desc = table customer not found in schema (CallerID: userData1)

Next Steps #

You can now proceed with Horizontal Sharding.

Or alternatively, if you would like to teardown your example:

./401_teardown.sh